CODEWITHSUNDEEP
oracle-databaseplsqlexception
user35002
user35002

Reputation: 53

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

I am using SQL developer to write a procedure.

The objective is to get the name of the drainage system from one table and get the count of how much the drainage system name code appears in another table.

My procedure works, but when I enter an incorrect value, it does not go to the exception section. For example, when I input ‘Mexico River’, this name does not exist in the table. So, I want my exception section to realize that this is an incorrect value being entered.

My question is how do I modify my code, so it can detect incorrect values and go to my exception section.

Below is a snippet of my code:

PROCEDURE number_of_rivers --second procedure with 1 parameter
  (input_sysName IN TBLDrainage.DRAINAGE_SYS%TYPE)
   is 

    -- Create a cursor 
    cursor c_river is 
      select code, drainage_sys 
      from TBLDRAINAGE 
      where DRAINAGE_SYS = input_sysName;
    v_rivercount Number;
    r_variable c_river %rowtype; 

  Begin

   FOR r_variable in c_river
   loop 
     select count (Drainage_sys) into v_rivercount
     from TBLRIVER
     where DRAINAGE_SYS = r_variable.code;
     DBMS_OUTPUT.PUT_LINE (UPPER(input_sysName) || ' has ' || v_rivercount || ' river(s) in the drainage system.');
  end loop;


EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('Error: Please enter a valid drainage system name');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE ('Error in finding system');
END ;

Upvotes: 0

Views: 93

Answers (1)

APC
APC

Reputation: 146239

The CURSOR..FOR loop has the property of executing zero or more times. It doesn't throw NO_DATA_FOUND.

There are a couple of solutions. One is to include a count inside the loop, and raise an exception afterwards.

l_count := 0;
FOR r_variable in c_river
loop 
  ....
  l_count := l_count + 1;
end loop;
if l_count = 0 then
  raise NO_DATA_FOUND;
end if;

The other would be to validate the input parameter at the start of your program.

begin
  open c_river;
  fetch c_river into r_variable;
  if c_river%notfound then  
    raise NO_DATA_FOUND;
  else
    select count (Drainage_sys) 
    into v_rivercount
    from TBLRIVER
    where DRAINAGE_SYS = r_variable.code;
    DBMS_OUTPUT.PUT_LINE (UPPER(input_sysName) || ' has ' || v_rivercount || ' river(s) in the drainage system.');
  end if;
  close c_river;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('Error: Please enter a valid drainage system name');
    close c_river;
END ;

In this solution I have removed the loop, because I would expect a look-up on drainage system should be unique and return one record. Please re-instate the loop if your data model isn't like that.

I have retained your names for the cursor and its row variables but you should re-name them. They are used for selecting drainage systems not rivers, and their names ought to reflect that. Discipline in naming things is a useful habit to acquire, as misleading variable names will cause confusion in larger chunks of code.

Also, swallowing exceptions like this is very bad:

 WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE ('Error in finding system');

Oracle has thousands of error messages: it better to do nothing with the error message than to throw it away.

Upvotes: 2

Related Questions