CHAPTER 6. Error Handling

Based on PL/SQL User s Guide

How to handling predefined and user-defined errors/exceptions.

6.1 Introducation

A warning or a error is called exception. Handling errors in your code will not crush your program and you can record those abnormal conditions.

Error/exception handling includes three steps: defining the exception, raising the exception and handling the exception. There are two types of exceptions; the predefined exceptions and user-defined exceptions.

For the predefined exceptions, the definitions are done, and they are raised automatically when the exceptions occur. The only thing a user program has to do is the write code to handling the exception.

For user defined functions, all three steps must be done in different parts of a PL/SQL block. That is, define the exception in the declaration part, raise exception in the executable part and handle exception in the exception handling part (the part following the executable part).

To define a exception, use EXCEPTION as data type to define the name of a exception:

Zero_shipment EXCEPTION ;

To raise an exception, use RAISE exception_name to raise exception:

Qty := I;

Insert into sp( s1, p1, qty ) ;

If qty = 0 Then

RAISE zeron_qty ;

END IF;

COMMIT ;

To handle exception, use WHEN exception_name THEN exception handler

WHEN zero_qty THEN

ROLLBACK ;

S := SQLERRM ;

INSERT INTO MSG ( sysdate, s );

Commit ;

6.2 Predefined Exceptions

The predefined exceptions are already defined and will be raised automatically by the run-time system. In a PL/SQL block, you only need to handle the exception in the exception handling part. In handling exceptions, you usually record error(s) in table(s), roll the error transaction. A

(A) List of Predefined Exceptions

The following table give a list of predefined exceptions. From the example code, it looks like not all predefined exception are listed here.

Exception Name Oracle Error SQL_CODE value

CURSOR_ALREADY_OPEN ORA-06511 -6511 (reopen unclosed cursor)

DUP_VAL_ON_INDEX ORA-00001 -1 (duplicate value in col with unique index)

INVALID_CURSOR ORA-01001 -1001 (illeage cursor op, such as fetch un-opened

cursor)

INVLIDE_NUMBER ORA-01722 -1722 (failure when converting string to number)

LOGIN_DENIED ORA-01017 -1017 (log on Oracle with invalid user/password)

NO_DATA_FOUND ORA-01403 +100 (no row is returned from SELECT INTO.

Notice that, FETCH is supposed not to return row eventually, NO_DATA_FOUND is not raised for fetch)

NOT_LOGGED_ON ORA-01012 -1012 (user is not logged on Oracle )

PROGRAM_ERROR ORA-06501 -6501 (PL/SQL internal error )

ROWTYPE_MISMATCH ORA-06504 -6504 (incompatible data type when PL/SQL cursor

variable is assigned to host cursor variable

and two cursor has incompatible type.

STORAGE_ERROR ORA-06500 -6500 (PL/SQL run out-of-memory, or memory

corrupted.)

TIMEROUT_ON_RESROUCE ORA-00051 -51 (Timeout when Oracle is waiting for resource.)

TOO_MANY_ROWS ORA-01422 -1422 (More than 1 row returned from select into)

VALUE_ERROR ORA-06502 -6502 (an arithmetic, conversion, trunction or size-

constraint error occurs. Ex, table col is

longer than variable.)

ZERO_DIVIDE ORA-01476 -1476

(B) Examples

-- The example show how to handle the predefined error: Divided-by-Zero.

-- The exception is predefined, and automatically raised. The only code

-- needed is to handle the exception. In this example the error message

-- and code is printed on the screen in the debug mode.

-- More realistic solution is to record error in table and roll back

-- transaction if necessary shown in SQL INSERT statement.

declare

i integer := 0;

j integer := 100;

s varchar2(50) ;

begin

i := j / i;

EXCEPTION

when others then

s := SQLERRM ;

dbms_output.put_line( SQLCODE || ', ' || SQLERRM ) ;

insert into MSG VALUES ( sysdate, s );

commit;

END ;

-- The example shows the INVALID_NUMBER

declare

s varchar2(50) ;

begin

insert into sp values('s2', 'p3', '100a') ; -- 100a  is not a num

commit;

EXCEPTION

when others then

s := SQLERRM ;

dbms_output.put_line( SQLCODE || ', ' || SQLERRM ) ;

insert into MSG VALUES ( sysdate, s );

commit;

END ;

-- The example shows the VALUE_ERROR

declare

s varchar2(50) ;

begin

insert into sp values('s10202', 'p1', 1000) ;

-- snum has more than 5 chars

commit;

EXCEPTION

when others then

s := SQLERRM ;

dbms_output.put_line( SQLCODE || ', ' || SQLERRM ) ;

insert into MSG VALUES ( sysdate, s );

commit;

END ;

6.3 User-Defined Exceptions

An user-defined exception must be defined, and raised and handled by user.

To define a exception, define a exception variable (name) like defining any other variable. The differences are exception variable cannot be used in SQL statement or assignment statement.

To raise an exception, you use the raise statement in a IF statement.

Example 1: Exception with fixed message. The message raised by the handler is system defined. You cannot change it.

-- The example shows how to

-- 1. define user-exception.

-- 2. raise exception with raise statement

-- 3. handle the exception.

DECLARE

s varchar2(100) ;

qty integer := 0 ;

illegal_qty EXCEPTION ;

BEGIN

insert into sp values ('s1', 'p1', qty );

if qty <= 0 THEN

raise illegal_qty ;

end if;

EXCEPTION

WHEN ILLEGAL_QTY THEN

rollback ;

s := SQLERRM ;

dbms_output.put_line( SQLCODE || ', ' || SQLERRM );

insert into msg values (sysdate, s );

commit;

END ;

You can raise an exception with RAISE_APPLICATION_ERROR subprogram defined in DBMS_STANDARD package. No prefix is needed when call the subprogram since DMBS_STANDARD is a extension of STANDARD package. The exception don t have to be defined before you call raise_appliction_error( error_number, msg [, { true | false } ] ) where error_number must be between 2000 to 20999, msg should be no more than 2048 bytes long, and true or false indicate whether the error should placed on the stack or previous errors or replace all errors with the new one.

Example 2: Raise error with user-defined message and handled by OTHERS hanler.

-- The example shows how to

-- 1. Raise exception with raise_applicaiton_error with exception definition

-- 2. handle the exception.

-- 3. Pass user's message to Oralce and select user own error code.

-- In this example, the procedure raise_application_error of DMBS_STANDARD

-- package is used, no exception needs to be defined. The error code and

-- the message are assigned and will be returned by SQLCODE and SQLERRM

-- function calls.

DECLARE

s varchar2(100) ;

qty integer := 0 ;

BEGIN

insert into sp values ('s1', 'p1', qty );

if qty <= 0 THEN

raise_application_error (-20123, 'Quantity is zero or negative') ;

-- the error code must be between -20,000 and -20,999 and

-- the error message is up to 2048 characters.

end if;

EXCEPTION

WHEN OTHERS THEN

-- The error raised by raise_application_error must be handled by

-- OTHERS handler if no pseudocommand/compiler instruction

-- EXCEPTION_INIT ( err_name, err_code ) is used.

rollback ;

s := SQLERRM ;

dbms_output.put_line( SQLCODE || ', ' || SQLERRM );

insert into msg values (sysdate, s );

commit;

END ;

To use user defined exception name to define exception hanlder, you can associate a error name with an error code by compiler command EXCEPTION_INIT ( error_name, error_code ). After user defined error name/variable is associated with a code, you can raise the error and handle it with the user-named handler:

Example 3:

-- The example shows how to

-- 1. Associate an exception with a error code which will be used by

-- SQLCODE.

-- 2. Handle exception with user_defined exception name.

-- 3. Pass user's message to Oralce and select user own error code.

-- In this example, the procedure raise_application_error of DMBS_STANDARD

-- package is used, no exception needs to be defined. The error code and

-- the message are assigned and will be returned by SQLCODE and SQLERRM

-- function calls.

DECLARE

ILLEGAL_QTY EXCEPTION;

PRAGMA EXCEPTION_INIT ( ILLEGAL_QTY, -20123 ); -- pseudocommand.

-- without pseudocommand, error -20124 must by catched by OTHERS

s varchar2(100) ;

qty integer := 0 ;

BEGIN

insert into sp values ('s1', 'p1', qty );

if qty <= 0 THEN

raise_application_error (-20123, 'Quantity is zero or negative') ;

-- the error code must be between -20,000 and -20,999 and

-- the error message is up to 2048 characters.

end if;

EXCEPTION

WHEN ILLEGAL_QTY THEN

-- Now user-named exception handler can be used to handle error

-- instead of OTHERS.

rollback ;

s := SQLERRM ;

dbms_output.put_line( SQLCODE || ', ' || SQLERRM );

insert into msg values (sysdate, s );

commit;

END ;

Exception Propagation

Usually exceptions are handled in the block they are defined. However, if there are no proper handlers for the exception, the exception handlers in the enclosing blocks are called to handle the exceptions. If there is no proper handler in the enclosing blocks, then an un-handled exception will be raised.

6.4 Handling User-Defined Exceptions

Exceptions are handled by exception handled. An exception handler has the following format:

WHEN exception_name [ OR exception_name ] THEN

Code  

WHEN . . . . . . . THEN

. . . . . .

WHEN OTHERS THEN

. . . . . . .

where

(1) More than on exception can share the same handler.

(2) The OTHERS handler guarantee that no unhandled exception.

(3) Exception can be raised in the declaration such as

I INTEGER(2) := 123 ; -- value too big

The exception can be catched by OTHERS exception handler.

(4) Exception raised in exception handler is handled by enclosing handler:

WHEN OTHERS THEN

Insert into msg values (   ) ; -- exception if raised will be handled by enclosing handler.

(5) You cannot branch from the executable part to exception hander nor branch from exception handler to executable part.

(6) SQLCODE and SQLERRM can be used in the handler to get exception code and the corresponding message. All but NO_DATA_FOUND exception returns a negative number. The message returned from SQLERRM contains Oracle error code ( ORA 10130) plus the message. For user-defined error, the error code is +1 if no EXCEPTION_INIT compiler command is used. Otherwise it returns the code listed in the EXCEPTION_INIT( exception_name, exception_code ) command.

(7) To make SQLERRM function return a user-defined message, call RAISE_APPLICATION_ERROR( code, message) procedure.

(8) SQLCODE and SQLERRM function cannot be called from SQL statement, that is

INSERT INTO msg VALUES ( sysdate, SQLERRM ) ;

Is illegal. However, you can define a local string variable and assign SQLERRM in the string variable and then insert the string into msg.

(9) You can pass an error code to SQLERRM to get the corresponding error message. Pass 0 to SQLERRM also received ORA-0000: Normal Successful Completion

(10) Try always use WHEN OTHERS THEN handler to handle error. Unhandled errors affect subprograms, blocks. In PL/SQL subprogram, an unhandled exception will not rollback the database changes.

6.5 Useful Techniques

After an exception occur and handled by exception handler, you cannot go back to current block where the exception is raised. However, putting the raising statements and exception handler with a sub-block and adding code after the sub block.

BEGIN

BEGIN

 .

RAISE exception_name ;

EXCEPTION

WHEN   THEN

END ;

Continuation code  

EXCEPTION  

END ;

Instead of rolling back transaction, you may put code + exception handler inside a loop. When a error occurs, change the some value and retry it in the next loop until the code is executed without error and exit from the loop.

LOOP

Insert into sp VALUES (  s1 ,  p1 , qty ) ;

If qty <= 0 Then

Raisze ILLEGAL_QTY ;

END IF

Commit ;

EXIT ; -- exit loop

EXCEPTION

WHEN ILLEGAL_QTY THEN

Qty := qty + 5 ;

END LOOP ;