Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted November 14, 2016

Little-Known Exceptions in  Oracle’s PL/SQL

By David Fitzjarrell

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:

ExceptionORA ErrorSQLCODERaise When ...
ACCESS_INTO_NULL 06530 -6530 A program attempts to assign values to the attributes of an uninitialized object
CASE_NOT_FOUND 06592 -6592 None of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause.
COLLECTION_IS_NULL 06531 -6531 A program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.
CURSOR_ALREADY_OPEN 06511 -6511 A program attempts to open an already open cursor. A cursor must be closed before it can be reopened. A cursor FOR loop automatically opens the cursor to which it refers, so your program cannot open that cursor inside the loop.
DUP_VAL_ON_INDEX 00001 -1 A program attempts to store duplicate values in a column that is constrained by a unique index.
INVALID_CURSOR 01001 -1001 A program attempts a cursor operation that is not allowed, such as closing an unopened cursor.
INVALID_NUMBER 01722 -1722 n a SQL statement, the conversion of a character string into a number fails because the string does not represent a valid number. (In procedural statements, VALUE_ERROR is raised.) This exception is also raised when the LIMIT-clause expression in a bulk FETCH statement does not evaluate to a positive number.
LOGIN_DENIED 01017 -1017 A program attempts to log on to Oracle with an invalid username or password.
NO_DATA_FOUND 01403 +100 A SELECT INTOstatement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table.

Because this exception is used internally by some SQL functions to signal completion, you should not rely on this exception being propagated if you raise it within a function that is called as part of a query.

NOT_LOGGED_ON 01012 -1012 A program issues a database call without being connected to Oracle.
PROGRAM_ERROR 06501 -6501 PL/SQL has an internal problem.
ROWTYPE_MISMATCH 06504 -6504 The host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types. When an open host cursor variable is passed to a stored subprogram, the return types of the actual and formal parameters must be compatible.
SELF_IS_NULL 30625 -30625 A program attempts to call a MEMBER method, but the instance of the object type has not been initialized. The built-in parameter SELF points to the object, and is always the first parameter passed to a MEMBER method.
STORAGE_ERROR 06500 -6500 PL/SQL runs out of memory or memory has been corrupted.
SUBSCRIPT_BEYOND_COUNT 06533 -6533 A program references a nested table or varray element using an index number larger than the number of elements in the collection.
SUBSCRIPT_OUTSIDE_LIMIT 06532 -6532 A program references a nested table or varray element using an index number (-1 for example) that is outside the legal range.
SYS_INVALID_ROWID 01410 -1410 The conversion of a character string into a universal rowid fails because the character string does not represent a valid rowid.
TIMEOUT_ON_RESOURCE 00051 -51 A time out occurs while Oracle is waiting for a resource.
TOO_MANY_ROWS 01422 -1422 A SELECT INTO statement returns more than one row.
VALUE_ERROR 06502 -6502 An arithmetic, conversion, truncation, or size-constraint error occurs. For example, when your program selects a column value into a character variable, if the value is longer than the declared length of the variable, PL/SQL aborts the assignment and raises VALUE_ERROR. In procedural statements, VALUE_ERROR is raised if the conversion of a character string into a number fails. (In SQL statements, INVALID_NUMBER is raised.)
ZERO_DIVIDE 01476 -1476 A program attempts to divide a number by zero.

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM