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> edit preexcep1
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> 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
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> @ preexcep1a Multiple rows meet criteria PL/SQL procedure successfully completed.
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> edit nonpreex1
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> @ 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> edit userexcep1
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> @ 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> edit funcexcep1
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> @ funcexcep1 Undefined error -2291 ORA-02291: integrity constraint (SCOTT.ORDNO_FK) violated - parent key not found PL/SQL procedure successfully completed.