Exceptions in PL/SQL:

PL/SQL allows the programmer to build in an exception handler to deal with exceptions that occur within the block. Exceptions may be either Oracle errors that happen automatically or where the programmer raises an exception explicitly through the use of the RAISE. The exception can be trapped by the handler meaning the exception happened somewhere in the BEGIN and will be trapped/handled in the exception or the exception can happen in the begin without an exception handler so the block will end as a failure and the exception will be propagated to the calling environment. The three types of exceptions are predefined Oracle errors that Oracle handles implicitly, Oracle errors that are not predefined that can be declared in the block and handled implicitly by Oracle or user-defined errors that should be declared and handled explicitly by the programmer.

When trapping exceptions, the programmer can use a WHEN clause in the exception portion of the block to detail the processing that should occur. There can be a WHEN OTHERS to handle unspecified exceptions. In setting up the exception portion of the block you should begin with the keyword EXCEPTION (similar to the DECLARE and BEGIN structure). Code each exception with a WHEN and place the WHEN OTHERS clause at the bottom. PL/SQL will handle only one exception before leaving the block.

Some of the predefined Oracle errors are CURSOR_ALREADY_OPEN, DUP_VAL_ON_INDEX, INVALID_CURSOR, INVALID_NUMBER, LOGIN_DENIED, NO_DATA_FOUND, NOT_LOGGED_ON, PROGRAM_ERROR, ROWTYPE_MISMATCH, STORAGE_ERROR, TOO_MANY_ROWS, VALUE_ERROR AND ZERO_DIVIDE. Check help, your text, or the Web links for definitions of these errors. These errors may be trapped by using the standard name within the WHEN clause.

In the block below, I have set up an exception routine to handle the predefined Oracle errors of multiple rows being found by the select and no rows being found by the select. There is also a segment to handle other errors that might occur. The code in the first example asks for idno > then 77777. No such record exists so the error for no rows found is returned. In the second run, I changed the where to read idno < 77777 which will give multiple rows and therefore the multiple row error will appear.

SQL CODE:

SQL> edit preexcep1

PL/SQL CODE:

SET SERVEROUTPUT ON
DECLARE
   v_idno    VARCHAR2(5);
   v_yrgoal  NUMBER(7,2);
BEGIN
   SELECT idno, yrgoal INTO v_idno, v_yrgoal
   FROM donornew
   WHERE idno > 77777;
   IF v_yrgoal > 250 THEN
      v_yrgoal := v_yrgoal * 1.1;
   END IF;
   UPDATE donornew
   SET yrgoal = v_yrgoal
   WHERE idno = v_idno;
EXCEPTION
   WHEN TOO_MANY_ROWS THEN
      dbms_output.put_line('Multiple rows meet criteria');
   WHEN NO_DATA_FOUND THEN
      dbms_output.put_line('Not a valid id #');
   WHEN OTHERS THEN
      dbms_output.put_line('Undefined error occured');
END;   
/
SET SERVEROUTPUT OFF

SQL CODE:

SQL> select * from donornew;

IDNO  NAME            STADR           CITY       ST ZIP   DATEFST      YRGOAL CONTACT
----- --------------- --------------- ---------- -- ----- --------- --------- ------------
11111 Stephen Daniels 123 Elm St      Seekonk    MA 02345 03-JUL-98       845 John Smith
12121 Jennifer Ames   24 Benefit St   Providence RI 02045 24-MAY-97       605 Susan Jones
22222 Carl Hersey     24 Benefit St   Providence RI 02045 03-JAN-98       100 Susan Jones
23456 Susan Ash       21 Main St      Fall River MA 02720 04-MAR-92    190.08 Amy Costa
33333 Nancy Taylor    26 Oak St       Fall River MA 02720 04-MAR-92     66.55 John Adams
34567 Robert Brooks   36 Pine St      Fall River MA 02720 04-APR-98     63.53 Amy Costa

6 rows selected.

SQL> @ preexcep1
Not a valid id #

PL/SQL procedure successfully completed.

SQL> edit preexcep1a

PL/SQL CODE:

SET SERVEROUTPUT ON
DECLARE
   v_idno    VARCHAR2(5);
   v_yrgoal  NUMBER(7,2);
BEGIN
   SELECT idno, yrgoal INTO v_idno, v_yrgoal
   FROM donornew
   WHERE idno < 77777;
   IF v_yrgoal > 250 THEN
      v_yrgoal := v_yrgoal * 1.1;
   END IF;
   UPDATE donornew
   SET yrgoal = v_yrgoal
   WHERE idno = v_idno;
EXCEPTION
   WHEN TOO_MANY_ROWS THEN
      dbms_output.put_line('Multiple rows meet criteria');
   WHEN NO_DATA_FOUND THEN
      dbms_output.put_line('Not a valid id #');
   WHEN OTHERS THEN
      dbms_output.put_line('Undefined error occured');
END;   
/
SET SERVEROUTPUT OFF

SQL CODE:

SQL> @ preexcep1a
Multiple rows meet criteria

PL/SQL procedure successfully completed.

SQL CODE:

SQL> SELECT * FROM orderz;

ORDNO  CUSTI ORDATE
------ ----- ---------
000001 11111 10-JUN-99
000002 12121 10-JUN-99
000003 12345 10-JUN-99
000004 11111 08-JUL-99

SQL> SELECT * FROM ordline;

ORDNO  ITEM    NUMORD
------ ---- ---------
000001 1212         1
000001 2121         1
000001 2345         1
000002 1111         3
000002 3333         1
000003 3333         2
000003 3456         1
000004 1212         3
000004 1234         2

9 rows selected.
In this example I am trapping a non-predefined error. The data shown above is the data used in this example. The error that I am trapping is an attempt to add a row to the order line file that contains an order number that does not appear in the order header file. There is a foreign key link that makes this illegal. The error from Oracle is shown below. The code that I wrote to deal with this error is also shown below. Notice that I first defined a name for the exception and then using PRAGMA EXCEPTION_INIT, I gave the error number. I then dealt with the error in the EXCEPTION section using the defined name. When I ran the program, my error appeared. Note that PRAGMA is a compiler directive.

Oracle ERROR:

ORA-02291

ORA-02291	integrity constraint str.name violated - parent key not found 

Cause:	An attempt was made to INSERT or UPDATE a foreign key value. The result was a value that is not in the parent key. 
Action:	UPDATE to or INSERT a value that is in the parent key. 

Copyright (C) 1995, Oracle Corporation

SQL CODE:

SQL> edit nonpreex1

PL/SQL CODE:

SET SERVEROUTPUT ON

DECLARE

   e_invalid_ordno EXCEPTION;
   PRAGMA EXCEPTION_INIT(e_invalid_ordno, -2291);

   v_ordno ordline.ordno%TYPE :='123456' ;
   v_itemno ordline.itemno%TYPE :='1212';
   v_numord ordline.numord%TYPE :=100;

BEGIN

   INSERT INTO ordline
      VALUES(v_ordno, v_itemno, v_numord);
   
EXCEPTION

   WHEN e_invalid_ordno THEN
      dbms_output.put_line('Foreign key problem - no ordno on ORDERZ');
   WHEN OTHERS THEN
      dbms_output.put_line('Undefined error occured');

END;   
/
SET SERVEROUTPUT OFF

SQL CODE:

SQL> @ nonpreex1
Foreign key problem - no ordno on ORDERZ

PL/SQL procedure successfully completed.

Next, we are going to look at the process to trap user defined errors. In the DECLARE, I must define the exception. In the BEGIN, I must use the RAISE to explicitly raise the exception. Note in these examples, I am hard coding values into the DECLARE, in the real world I would probably be testing user input. In the EXCEPTION, I must deal with the error.

For example lets assume that when putting a record into the ordline table, I do not want to allow the number ordered to be greater than 200. First, I named the exception in the DECLARE. Next, In the BEGIN, I put in an IF test to test and see if the numord was greater than 200. If it was I coded the RAISE that will generate the exception. The code for the exception is in EXCEPTION. Note that for these examples, I have been handling errors by simply displaying a message. More sophisticated handling could be included.

SQL CODE:

SQL> edit userexcep1

PL/SQL CODE:

SET SERVEROUTPUT ON

DECLARE

   e_numord_over200 EXCEPTION;

   v_ordno ordline.ordno%TYPE :='000003' ;
   v_itemno ordline.itemno%TYPE :='1212';
   v_numord ordline.numord%TYPE :=300;

BEGIN

   IF v_numord > 200 THEN
      RAISE e_numord_over200;
   END IF;

   INSERT INTO ordline
      VALUES(v_ordno, v_itemno, v_numord);
   
EXCEPTION

   WHEN e_numord_over200 THEN
      dbms_output.put_line('NUMORD EXCEEDS 200 - USER RANGE ERROR');
   WHEN OTHERS THEN
      dbms_output.put_line('Undefined error occured');

END;   
/
SET SERVEROUTPUT OFF

SQL CODE:

SQL> @ userexcep1
NUMORD EXCEEDS 200 - USER RANGE ERROR

PL/SQL procedure successfully completed.

SQL> SELECT * FROM ordline;

ORDNO  ITEM    NUMORD
------ ---- ---------
000001 1212         1
000001 2121         1
000001 2345         1
000002 1111         3
000002 3333         1
000003 3333         2
000003 3456         1
000004 1212         3
000004 1234         2

9 rows selected.

As you can see from the SELECT above, the INSERT was not executed because the error caused the EXCEPTION to be handled and the block was ended.

In the example below, I am using two functions SQLCODE which returns the code of an error and SQLERRM which returns a message. There are different codes for different kinds of errors: 0 means no exceptions were found, 1 means a user defined error, there are positive codes for predefined errors and the not predefined errors return the negative number that signifies the code. In this case, I used the foreign key problem and did not define anything specific so it became an error in the other category. I then used the functions to specify the error that occurred.

SQL CODE:

SQL> edit funcexcep1

PL/SQL CODE:

SET SERVEROUTPUT ON

DECLARE

   e_numord_over200 EXCEPTION;

   v_err_code NUMBER;
   v_err_msg  VARCHAR2(255);

   v_ordno ordline.ordno%TYPE :='500003' ;
   v_itemno ordline.itemno%TYPE :='1212';
   v_numord ordline.numord%TYPE :=200;

BEGIN

   IF v_numord > 200 THEN
      RAISE e_numord_over200;
   END IF;

   INSERT INTO ordline
      VALUES(v_ordno, v_itemno, v_numord);
   
EXCEPTION

   WHEN e_numord_over200 THEN
      dbms_output.put_line('NUMORD EXCEEDS 200 - USER RANGE ERROR');
   WHEN OTHERS THEN
      v_err_code:= SQLCODE;
      v_err_msg := SQLERRM;
      dbms_output.put_line('Undefined error '||TO_CHAR(v_err_code)||'  '||v_err_msg);

END;   
/
SET SERVEROUTPUT OFF

SQL CODE:

SQL> @ funcexcep1
Undefined error -2291  ORA-02291: integrity constraint (SCOTT.ORDNO_FK) violated - parent key not found

PL/SQL procedure successfully completed.