Question: What is a PL/SQL exception? Why
are PL/SQL exceptions important and how do I define a PL/SQL
exception?
Answer (by Dr, Hall)
Exceptions are like flags that are raised when a predefined
event occurs, normally error conditions. Most programming
languages "throw" exceptions but PL/SQL "raises" exceptions.
Whether thrown or raised, the meaning is the same. When an
exception is raised, program execution stops and jumps to
the nearest exception handler. If the exception handler
catches the exception, program execution resumes at the
point right after the exception handler code.
Program
execution never returns to the code that raised the
exception unless the module is subsequently re-executed. If
there is no exception handler in the module that raised the
exception, execution returns to the calling block's
exception handler. This continues until the exception is
handled or the exception jumps out of the PL/SQL module
and the exception
is passed to the calling application (such as SQL*Plus or a
script). Since none of the modules that we have presented so
far have exception handlers, all exceptions are passed back
to SQL*Plus which then displayed the exception along with
the error messages to the user. Every PL/SQL block
can have an optional exception handler.
SQL>
declare
2 n_1 number := 5;
3 n_2 number := 0;
4
begin
5 n_1 := n_1/n_2; -- divide by zero
6
dbms_output.put_line(n_1);
7 end;
8 /
declare
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 5
In the example
above there is an exception raised because the code tries to
divide by zero. Since the block has no exception handler,
the exception is passed back to SQL*Plusto handle. An
exception handler or exception code is placed at the end of
a block before the END clause.
If there is no exception raised, the exception code is
jumped and not executed.
begin
----- Code goes here
exception
----- Exception code
goes here
end;
The exception
code follows the format:
when
<exception> then <handle code>;
when others then <handle
code>;
The OTHERS optionwill catch all
exceptions that are not handled above the OTHERS clause. As
with the CASE statement, an exception will be handled by the
first WHEN clause that matches the exception, as show below.
SQL>
declare
2 n_1 number := 5;
3 n_2 number := 0;
4
begin
5 n_1 := n_1/n_2; -- divide by zero
6
dbms_output.put_line(n_1);
7 exception
8 when
ZERO_DIVIDE
9 then dbms_output.put_line('You Divided By
Zero');
10 end;
11 /
You Divided By Zero
Line 5 raises the exception when the
division by zero occurs. Line 6 is jumped as the exception
moves execution to the exception handler starting at line 7.
Lines 8 and 9 actually handle the exception. Once the
exception is handled, execution resumes at line 10, where
the block ends.
As stated earlier, if
an exception is not handled, it will fall through to the
next higher or calling block's exception handler. This is
shown in the example below where two functionsare created,
both with errors. The first will raise a
CASE_NOT_FOUNDexception on certain values. The second
divides by zero.
SQL> create
or replace function bad_convert
2 (n_number IN number)
3 return varchar2
4 as
5 begin
6 case n_number
7
when 1 then return 'one';
8 when 2 then return 'two';
9 when 3 then return 'three';
10 end case;
11 end;
12 /
Function created.
SQL> create
or replace function divide_by_zero
2 (n_1 IN number)
3
return number
4 as
5 n_2 number := 0;
6 begin
7
n_2 := n_1/n_2; -- divide by zero
8 return n_2;
9 end;
10 /
Function created.
Neither of the functionsabove contains
an exception handler. The PL/SQL blockbelow will call these
functions and cause an exception.
SQL>
declare
2 v_result varchar2(100);
3 v_numb number;
4 begin
5 -- buggy code, encase in a block
6 begin
7 for i in 1 .. 6 loop
8 dbms_output.put_line(bad_convert(i));
9 end loop;
10 exception
11 when INVALID_NUMBER
12
then dbms_output.put_line('Invalid Number
Exception');
13 when ZERO_DIVIDE
14 then dbms_output.put_line('Divide
By Zero
Exception');
15 end;
16
17 -- more buggy
code
18 begin
19 v_numb := divide_by_zero(25);
20
end;
21
22 exception
23 when others
24 then
dbms_output.put_line('Caught at the
End');
25 end;
26 /
one
two
three
Caught at the
End
The code begins
to loop on line 7 but a CASE_NOT_FOUND
exception is raised by the
bad_convert
function on line 8 when the loop index
i equals 4.
The exception is not handled in the function so the program
execution instead jumps out of the function to the exception
handler for the calling block which is line 10. This handler
does not handle the CASE_NOT_FOUND exception so execution
jumps to the outer block's exception handler at line 22.
This handler catches all exceptions with
the OTHERS clause. Execution resumes at line 25 which is the
end of the block. Notice that the procedure ended
successfully. Since the exception was handled, SQL*Plus does
not see the exception and instead sees the module end
normally. The best place to handle the exception is normally
in the offending block. A corrected version below now
handles the exception.
SQL> create
or replace function bad_convert
2 (n_number IN number)
3 return varchar2
4 as
5 begin
6 case n_number
7
when 1 then return 'one';
8 when 2 then return 'two';
9 when 3 then return 'three';
10 end case;
11
exception
12 when CASE_NOT_FOUND
13 then return 'Bad
Conversion';
14 end;
15 /
Function created.
SQL>
declare
2 v_result varchar2(100);
3 v_numb number;
4 begin
5 -- buggy code, encase in a block
6 begin
7 for i in 1 .. 6 loop
8 dbms_output.put_line(bad_convert(i));
9 end loop;
10 exception
11 when INVALID_NUMBER
12
then dbms_output.put_line('Invalid Number
Exception');
13 when ZERO_DIVIDE
14 then dbms_output.put_line('Divide
By Zero
Exception');
15 end;
16
17 -- more buggy
code
18 begin
19 v_numb := divide_by_zero(25);
20
end;
21
22 exception
23 when others
24 then
dbms_output.put_line('Caught at the
End');
25 end;
26 /
one
two
three
Bad Conversion
Bad Conversion
Bad Conversion
Caught at the End
PL/SQL procedure
successfully completed.
The function
bad_conversion
now handles the exception and we can see this in the output
above. But there is still an exception caused by the
divide_by_zero
function. The example below handles the divide by zero
exception in the calling block.
SQL>
declare
2 v_result varchar2(100);
3 v_numb number;
4 begin
5 -- buggy code, encase in a block
6 begin
7 for i in 1 .. 6 loop
8 dbms_output.put_line(bad_convert(i));
9 end loop;
10 end;
11
12 -- more buggy code
13
begin
14 v_numb := divide_by_zero(25);
15 exception
16 when INVALID_NUMBER
17 then
dbms_output.put_line('Invalid
Number');
18 when
ZERO_DIVIDE
19 then dbms_output.put_line('Divide By
Zero');
20 end;
21
22 exception
23 when others
24 then dbms_output.put_line('Caught at the
End');
25
end;
26 /
one
two
three
Bad Conversion
Bad Conversion
Bad Conversion
Divide By Zero
The exception is handled in the calling
block at line 18. The final exception routine is not
executed as all exceptions are already handled. The block
execution ends normally. Now that we know the basics, let's
take a closer look at defining and raising exception in
PL/SQL.
