Defining PL/SQL Exceptions

Remote DBA

Remote DBA Plans
Remote DBA Service

Defining PL/SQL Exceptions

Oracle Tips by Burleson

Defining PL/SQL Exceptions

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.

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