Oracle – Handling PL/SQL errors in a static and dynamic context

PL/SQL error handling is done using the EXCEPTION block of the PL/SQL procedure. The construct of this block is fairly straightforward and well documented. The intent of this article is not to explain this block, but to present two approaches to error handling. The first is a “static” approach, in which possible error codes are defined at compile time. This is the most commonly used approach. However, sometimes it is useful to define procedures that can be passed dynamic error codes to be handled. This is sometimes useful for library functions where a single block of code my raise different errors based on the conditions in the calling function. The calling function may pass in an error code that is expected into the function where it can be handled. The example below encapsulates both of these approaches:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2
  3    -- Raise an expected error
  4    PROCEDURE div_by_zero
  5    IS
  6      result NUMBER;
  7    BEGIN
  8      result := 1/0;
  9    END;
 10
 11    -- Handle an error statically
 12    PROCEDURE handle_static_error
 13    IS
 14      div_ex EXCEPTION;
 15
 16      PRAGMA EXCEPTION_INIT( div_ex, -1476 );
 17    BEGIN
 18      div_by_zero;
 19    EXCEPTION
 20      WHEN div_ex THEN
 21        DBMS_OUTPUT.PUT_LINE( 'Captured static divide by zero' );
 22    END;
 23
 24    -- Handle a passed-in error code
 25    PROCEDURE handle_dynamic_error( expected_ex NUMBER )
 26    IS
 27    BEGIN
 28      div_by_zero;
 29    EXCEPTION
 30      WHEN OTHERS THEN
 31        IF SQLCODE = expected_ex THEN
 32          DBMS_OUTPUT.PUT_LINE( 'Captured dynamic error, code='
 33            || expected_ex );
 34        ELSE
 35          RAISE;
 36        END IF;
 37    END;
 38
 39  BEGIN
 40
 41    handle_static_error;
 42    handle_dynamic_error( -1476 );
 43
 44  END;
 45  /
Captured static divide by zero
Captured dynamic error, code=-1476

PL/SQL procedure successfully completed.

The div_by_zero procedure exists only as an example error to be handled. The error code for divide by zero is -1476, which will be of interest in the following paragraphs. Let’s first focus on the handle_static_error procedure. As the name suggests, this function handles a static error code defined at compile time. In the declaration block of the procedure, note the div_ex EXCEPTION; and PRAGMA EXCEPTION_INIT( div_ex, -1476 ); statements. The first simply defines an EXCEPTION variable and gives it a name. The call to EXCEPTION_INIT is key in that it maps the error code of interest to our EXCEPTION variable. With this information in hand, it is time to look to the EXCEPTION block of the handle_static_error procedure. The WHEN div_ex THEN statement defines where the divide by zero error will be captured and handled. The DBMS_OUTPUT statement simply writes to the console as an error handler. Defining EXCEPTION variables like this is the most common approach to error handling.
The handle_dynamic_error procedure demonstrates an alternative approach to error handling. This procedure takes an error code as a parameter. In the EXCEPTION block of this procedure, note that no EXCEPTION variable is referenced. Instead, the IF SQLCODE = expected_ex THEN uses a a special PL/SQL function. SQLCODE returns the code of the most recently raised error, which is then compared to the error code passed in as a parameter to the procedure. If a match exists, the DBMS_OUTPUT line is called as the error handler.
Generally speaking, the use of EXCEPTION variables and the PRAGMA EXCEPTION_INIT statement is the preferable way to implement error handling. However, the above does provide an alternative when dynamic error codes are necessary.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s