Little-Known Exceptions in  Oracle’s PL/SQL

Oracle has provided a number of pre-defined error conditions to be used in PL/SQL blocks and many of these may be unfamiliar to most programmers. To be honest some of these conditions won’t be thrown because programmers usually catch these conditions before any code goes into test. For those just starting to seriously code PL/SQL it would be good to cover these conditions and what can cause them. What follows is a list of these pre-defined conditions and a few examples of what can cause them to be thrown.

The list of conditions Oracle has pre-configured isn’t extremely large but it does cover errors that might not be well-known in some circles. The list, taken from the Oracle documentation, is shown below:

Let’s look at a few examples of lesser-known errors. The first is CASE_NOT_FOUND and is specific to the CASE statement implemented in PL/SQL (a CASE statement in a SQL query won’t throw this error if such a condition exists). If PL/SQL can’t find a matching WHEN clause to the CASE statement it will throw CASE_NOT_FOUND:

SQL> 
SQL> --
SQL> -- CASE_NOT_FOUND error
SQL> --
SQL> declare
  2  
  3  	     cursor mycur is
  4  	     select empno
  5  	     from emp;
  6  
  7  	     v_found	     number:=0;
  8  begin
  9  
 10  	     for myrec in mycur loop
 11  		     dbms_output.put_line(myrec.empno);
 12  		     case
 13  			     when myrec.empno=1 then v_found:=4444;
 14  			     when myrec.empno=73 then v_found:=4444;
 15  		     end case;
 16  		     dbms_output.put_line(v_found);
 17  	     end loop;
 18  
 19  exception
 20   when case_not_found then
 21  		     dbms_output.put_line('It ain''t here!!!');
 22  		     dbms_output.put(dbms_utility.format_error_stack);
 23  		     dbms_output.put_line(dbms_utility.format_error_backtrace);
 24  	     when others then
 25  		     dbms_output.put_line('It ain''t here!!!');
 26  		     dbms_output.put(dbms_utility.format_error_stack);
 27  		     dbms_output.put_line(dbms_utility.format_error_backtrace);
 28  end;
 29  /
7369                                                                                                                                                  
It ain't here!!!                                                                                                                                      
ORA-06592: CASE not found while executing CASE statement
ORA-06512: at line 12
                                                                       

PL/SQL procedure successfully completed.

SQL> 

An interesting point to note is that if this case statement/value assignment is re-written as shown below:

	v_found := case
			when myrec.empno=1 then 4444
			when nyrec.empno=73 then 4444
		   end;

The CASE_NOT_FOUND error is not thrown. Because of this behavior many programmers may not realize that the CASE statement is failing to assign a value because there isn’t a matching WHEN clause.

The next example can occur in complex procedures and programs when a cursor is opened several hundred lines back and the programmer forgets that the cursor is already open:

SQL> 
SQL> --
SQL> -- CURSOR_ALREADY_OPEN error
SQL> --
SQL> 
SQL> declare
  2  
  3  	     cursor mycur is
  4  	     select empno
  5  	     from emp
  6  	     where empno = 7934;
  7  
  8  	     v_myval number;
  9  
 10  begin
 11  
 12  	     open mycur;
 13  	     fetch mycur into v_myval;
 14  	     dbms_output.put_line(v_myval);
 15  	     open mycur;
 16  	     close mycur;
 17  exception
 18  	     when cursor_already_open then
 19  		     dbms_output.put_line('It''s already open, silly!!!');
 20  		     dbms_output.put(dbms_utility.format_error_stack);
 21  		     dbms_output.put_line(dbms_utility.format_error_backtrace);
 22  end;
 23  /
7934                                                                                                                                                  
It's already open, silly!!!                                                                                                                           
ORA-06511: PL/SQL: cursor already open
ORA-06512: at line 4
ORA-06512: at line 15
                                                                    

PL/SQL procedure successfully completed.

SQL> 

Likewise a programmer may think a cursor is open and tries to close it:

SQL> 
SQL> --
SQL> -- INVALID_CURSOR error
SQL> --
SQL> 
SQL> declare
  2  
  3  	     cursor mycur is
  4  	     select empno
  5  	     from emp
  6  	     where empno = 7934;
  7  
  8  	     v_myval number;
  9  
 10  begin
 11  
 12  	     close mycur;
 13  	     open mycur;
 14  	     fetch mycur into v_myval;
 15  	     dbms_output.put_line(v_myval);
 16  	     close mycur;
 17  
 18  exception
 19  	     when invalid_cursor then
 20  		     dbms_output.put_line('You didn''t open it yet.');
 21  		     dbms_output.put(dbms_utility.format_error_stack);
 22  		     dbms_output.put_line(dbms_utility.format_error_backtrace);
 23  end;
 24  /
You didn't open it yet.                                                                                                                               
ORA-01001: invalid cursor
ORA-06512: at line 12
                                                                                                      

PL/SQL procedure successfully completed.

SQL> 

Again such an occurrence would be more likely in long PL/SQL procedures and programs with complicated logic; it can be easy to lose track of cursor status in such cases.

The following two errors process the same condition but in different contexts. In the first case a TO_NUMBER conversion is attempted within a SQL query; this will throw an INVALID_NUMBER error:

SQL> 
SQL> --
SQL> -- INVALID_NUMBER error
SQL> --
SQL> 
SQL> declare
  2  	     cursor mycur is
  3  	     select to_number(job)
  4  	     from emp
  5  	     where rownum = 1;
  6  
  7  	     v_mydata	     emp.job%type;
  8  
  9  begin
 10  	     open mycur;
 11  	     fetch mycur into v_mydata;
 12  	     close mycur;
 13  exception
 14  	     when invalid_number then
 15  		     dbms_output.put_line('Value isn''t a number.');
 16  		     dbms_output.put(dbms_utility.format_error_stack);
 17  		     dbms_output.put_line(dbms_utility.format_error_backtrace);
 18  end;
 19  /
Value isn't a number.                                                                                                                                 
ORA-01722: invalid number
ORA-06512: at line 11
                                                                                                      

PL/SQL procedure successfully completed.

SQL> 

If that conversion is moved to the PL/SQL engine a VALUE_ERROR is thrown:

SQL> 
SQL> --
SQL> -- VALUE_ERROR error
SQL> --
SQL> 
SQL> declare
  2  	     cursor mycur is
  3  	     select job
  4  	     from emp
  5  	     where rownum = 1;
  6  
  7  	     v_mydata	     number;
  8  
  9  begin
 10  	     open mycur;
 11  	     fetch mycur into v_mydata;
 12  	     close mycur;
 13  exception
 14  	     when value_error then
 15  		     dbms_output.put_line('Value isn''t a number.');
 16  		     dbms_output.put(dbms_utility.format_error_stack);
 17  		     dbms_output.put_line(dbms_utility.format_error_backtrace);
 18  end;
 19  /
Value isn't a number.                                                                                                                                 
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 11
                                                

PL/SQL procedure successfully completed.

SQL> 

Knowing what is thrown in what context can help the programmer troubleshoot and fix the problem.

The last example provided covers the condition when division by zero is encountered in regular numeric data. Such conditions are more frequently encountered than one might think, especially when running calculations on values found in data dictionary views:

SQL> 
SQL> --
SQL> -- ZERO_DIVIDE error
SQL> --
SQL> 
SQL> declare
  2  	     v_mynum number:=1;
  3  	     v_myden number:=0;
  4  	     v_myquo number;
  5  begin
  6  	     v_myquo := v_mynum/v_myden;
  7  	     dbms_output.put_line(v_myquo);
  8  exception
  9  	     when zero_divide then
 10  		     dbms_output.put_line('Can''t divide by 0.');
 11  		     dbms_output.put(dbms_utility.format_error_stack);
 12  		     dbms_output.put_line(dbms_utility.format_error_backtrace);
 13  end;
 14  /
Can't divide by 0.                                                                                                                                    
ORA-01476: divisor is equal to zero
ORA-06512: at line 6
                                                                                             

PL/SQL procedure successfully completed.

SQL> 

Changing the code slightly to use the BINARY_FLOAT data type can eliminate this error:

SQL>
SQL> --
SQL> -- ZERO_DIVIDE error not thrown
SQL> --
SQL>
SQL> declare
  2          v_mynum binary_float:=1f;
  3          v_myden binary_float:=0f;
  4          v_myquo binary_float;
  5  begin
  6          v_myquo := v_mynum/v_myden;
  7          dbms_output.put_line(v_myquo);
  8  exception
  9          when zero_divide then
 10                  dbms_output.put_line('Can''t divide by 0.');
 11                  dbms_output.put(dbms_utility.format_error_stack);
 12                  dbms_output.put_line(dbms_utility.format_error_backtrace);
 13  end;
 14  /
Inf

PL/SQL procedure successfully completed.

SQL>

Binary float division will return ‘Inf’ in PL/SQL or ‘Nan’ from SQL*Plus but it won’t throw the ‘divisor is equal to zero’ error.

[For those who are old enough to remember the 1960’s the following query and output might remind you of the old Batman television show theme; it uses the ‘Nan’ output SQL*Plus provides for binary float division by zero:]

SQL> select listagg (b, '-')
  2        within group (order by null) ||' Batman'
  3    from (select 0f/0 b
  4        from dual
  5      connect by level <= 16
  6      );

LISTAGG(B,'-')WITHINGROUP(ORDERBYNULL)||'BATMAN'
--------------------------------------------------------------------------------
Nan-Nan-Nan-Nan-Nan-Nan-Nan-Nan-Nan-Nan-Nan-Nan-Nan-Nan-Nan-Nan Batman

SQL>

Many conditions that programmers may not consider when writing code are covered by pre-defined exceptions in PL/SQL. Yes, there is the ‘catch-all’ condition “when others then …” but it can be confusing if the exception handler isn’t properly coded as the error triggering the exception handler can be lost, making it almost impossible to know what condition existed at the time the handler was called. Knowing these exceptions and how to catch when they are thrown may make writing and debugging PL/SQL programs and procedures less frustrating, and can make error reports from the user community easier to understand and diagnose.

See all articles by David Fitzjarrell