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
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.
