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