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:
| Exception | ORA Error | SQLCODE | Raise 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.