 |
|
Oracle Tips by Burleson |
Exceptions in PL/SQL
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*Plus to
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 option will
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 functions are
created, both with errors. The first will raise a CASE_NOT_FOUND
exception 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 functions above
contains an exception handler. The PL/SQL block
below 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.
The above book excerpt is from:
Easy Oracle PL/SQL Programming
Get Started
Fast with Working PL/SQL Code Examples
ISBN 0-9759135-7-3
John Garmany
http://www.rampant-books.com/book_2005_1_easy_plsql.htm
|
