oracle - How can I modify my PL/SQL procedure to go to my exception handling? -

i using sql developer write procedure.

the objective name of drainage system 1 table , count of how drainage system name code appears in table.

my procedure works, when enter incorrect value, not go exception section. example, when input ‘mexico river’, name not exist in table. so, want exception section realize incorrect value being entered.

my question how modify code, can detect incorrect values , go exception section.

below snippet of code:

procedure number_of_rivers --second procedure 1 parameter   (input_sysname in tbldrainage.drainage_sys%type)          -- create cursor      cursor c_river        select code, drainage_sys        tbldrainage        drainage_sys = input_sysname;     v_rivercount number;     r_variable c_river %rowtype;     begin     r_variable in c_river    loop       select count (drainage_sys) v_rivercount      tblriver      drainage_sys = r_variable.code;      dbms_output.put_line (upper(input_sysname) || ' has ' || v_rivercount || ' river(s) in drainage system.');   end loop;   exception   when no_data_found     dbms_output.put_line ('error: please enter valid drainage system name');   when others     dbms_output.put_line ('error in finding system'); end ; 

the cursor..for loop has property of executing 0 or more times. doesn't throw no_data_found.

there couple of solutions. 1 include count inside loop, , raise exception afterwards.

l_count := 0; r_variable in c_river loop    ....   l_count := l_count + 1; end loop; if l_count = 0   raise no_data_found; end if; 

the other validate input parameter @ start of program.

begin   open c_river;   fetch c_river r_variable;   if c_river%notfound       raise no_data_found;   else     select count (drainage_sys)      v_rivercount     tblriver     drainage_sys = r_variable.code;     dbms_output.put_line (upper(input_sysname) || ' has ' || v_rivercount || ' river(s) in drainage system.');   end if;   close c_river; exception   when no_data_found     dbms_output.put_line ('error: please enter valid drainage system name');     close c_river; end ;       

in solution have removed loop, because expect look-up on drainage system should unique , return 1 record. please re-instate loop if data model isn't that.

---

i have retained names cursor , row variables should re-name them. used selecting drainage systems not rivers, , names ought reflect that. discipline in naming things useful habit acquire, misleading variable names cause confusion in larger chunks of code.

also, swallowing exceptions bad:

 when others     dbms_output.put_line ('error in finding system'); 

oracle has thousands of error messages: better nothing error message throw away.