Exceptions

Exceptions

Introduction to Oracle SQL & PL/SQL
Updated 11/7/2001

Setup
Concepts
Syntax for Using Exceptions

Setup

If you have not done so already, you'll need run the following script before executing some of these examples:

CREATE TABLE message (result VARCHAR2(255));

Concepts

In PL/SQL, whenever any error condition occurs, an exception is raised. Consider the following script:

PROMPT Enter last name of an employee to determine salary.
PROMPT

SET SERVEROUTPUT ON
SET VERIFY OFF

ACCEPT p_ename PROMPT 'Enter employee last name:'
PROMPT

DECLARE
v_ename emp.ename%TYPE:='&p_ename';
v_emp_rec emp%ROWTYPE;
BEGIN
SELECT * INTO v_emp_rec
FROM emp
WHERE UPPER(ename)=UPPER(v_ename);
DBMS_OUTPUT.PUT_LINE(INITCAP(v_ename)||' makes '||v_emp_rec.sal*12||' a year.');
END;
/

SQL> @ex_1
Enter last name of an employee to determine salary.
Enter employee last name:SCOTT

Scott makes 36000 a year.

SQL> @ex_1
Enter last name of an employee to determine salary.
Enter employee last name:GATES
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 5

Oracle will raise an exception any time a statement would violate a PL/SQL or SQL rule.

BEGIN
FOR i IN REVERSE 0..5 LOOP
DBMS_OUTPUT.PUT_LINE(120/i); --Statement will fail when i=0
END LOOP;
END;
/
24
30
40
60
120
BEGIN
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 3

BEGIN
INSERT INTO emp (empno, ename)
VALUES (7839,'GATES'); --Insert statement violates table constraints
END;
/
BEGIN
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("EMP"."DEPTNO")
ORA-06512: at line 2

Revisiting out earlier example:

PROMPT Enter last name of an employee to determine salary.
PROMPT

SET SERVEROUTPUT ON
SET VERIFY OFF

ACCEPT p_ename PROMPT 'Enter employee last name:'
PROMPT
DECLARE
v_ename emp.ename%TYPE:='&p_ename';
v_emp_rec emp%ROWTYPE;
BEGIN
SELECT * INTO v_emp_rec
FROM emp
WHERE UPPER(ename)=UPPER(v_ename);
DBMS_OUTPUT.PUT_LINE(INITCAP(v_ename) ||' makes '||v_emp_rec*12||' a year.');
END;
/
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 5

Now that we suspect that Oracle will raise an exception, we can code an error handler to process the exception if it occurs.

PROMPT Enter last name of an employee to determine salary.
PROMPT

SET SERVEROUTPUT ON
SET VERIFY OFF

ACCEPT p_ename PROMPT 'Enter employee last name:'
PROMPT
DECLARE
v_ename emp.ename%TYPE:='&p_ename';
v_emp_rec emp%ROWTYPE;
BEGIN
SELECT * INTO v_emp_rec
FROM emp
WHERE UPPER(ename)=UPPER(v_ename);
DBMS_OUTPUT.PUT_LINE(INITCAP(v_ename) ||' makes '||v_emp_rec*12||' a year.');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Invalid employee name ['||v_ename||']');
END;
/

When the script above is run:

SQL> ex_1
Enter last name of an employee to determine salary.
Enter employee last name:GATES
Invalid employee name [GATES]

Syntax for Using Exceptions

The example below illustrates the basic steps involved in using an exception. Extending the example above, we restrict users from seeing the president's salary.

DECLARE
e_security EXCEPTION;
v_ename emp.ename%TYPE:='&p_ename';
v_emp_rec emp%ROWTYPE;
v_error_message VARCHAR2(255);
BEGIN
SELECT * INTO v_emp_rec
FROM emp
WHERE UPPER(ename)=UPPER(v_ename);

IF v_emp_rec.job='PRESIDENT' THEN
RAISE e_security;
END IF;
DBMS_OUTPUT.PUT_LINE(INITCAP(v_ename) ||' makes '||v_emp_rec*12||' a year.');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Invalid employee name ['||v_ename||']');

WHEN e_security THEN
DBMS_OUTPUT.PUT_LINE('Cannot access president''s salary ['||v_ename||']');
INSERT INTO messages(result)
VALUES ('Attempt access presidents salary');

WHEN OTHERS THEN
v_error_message:=SQLERRM;
v_error_code:=SQLCODE;
DBMS_OUTPUT.PUT_LINE('Unexpected error : ');
DBMS_OUTPUT.PUT_LINE(v_error_code||' : '||v_error_message);
INSERT INTO messages(result)
VALUES ('Unexpected error : ' ||v_error_code||' : '||v_error_message);
END;
/

The example above illustrates the syntax and use of a user-defined exception. There are actually four different categories of exception and each has it's own syntax and use. The four categories are

Predefined Oracle server exception
Non-predefined Oracle server exception
User-defined exception
Unnamed user-defined exception

The use and syntax for each type of exception is explained below.

Predefined Oracle server exception

Because it's a system exception, Oracle will raise the exception if an error occurs.

For your convenience, Oracle declares this exception, so you can reference it by name when raising the error or in the exception handler.

Examples of named system exceptions are :

DIVIDE_BY_ZERO	Error (-01476): Oracle detected an attempt to divide by zero.
NO_DATA_FOUND	Error (-01403): A singleton select returned no rows
TOO_MANY_ROWS	Error (-01422): A singleton select returned more than one row
DUP_VAL_ON_INDEX	Error (-00001): Oracle detected an attempt to violate a unique constraint

Our first exception handling example above used the NO_DATA_FOUND exception to deal with a singleton select which returns no rows. Another example PL/SQL block using a predefined Oracle server exception.

PROMPT Enter last name of an employee to give 10% raise.
PROMPT
SET SERVEROUTPUT ON
SET VERIFY OFF
ACCEPT p_ename PROMPT 'Enter employee last name:'
PROMPT
DECLARE
v_ename emp.ename%TYPE:='&p_ename';
BEGIN
UPDATE emp
SET sal=sal*1.1
WHERE UPPER(ename)=UPPER(v_ename);
/* Always keep in mind that an update statement may affect more
* than one row, and it may affect no rows. You must check
* if you require the statement to only affect one row.
*
* If the statement affects more or less than one row, Oracle won't raise an
* exception, but since that violates your business rules, you can raise
* an exception explicitly.
*/
IF SQL%ROWCOUNT<1 THEN
--The last SQL statement affected 0 rows
RAISE NO_DATA_FOUND; --You can raise an Oracle predefined error explicitly
ELSIF SQL%ROWCOUNT>1 THEN
--The last SQL statement affected more than 1 row
RAISE TOO_MANY_ROWS;
END IF;
DBMS_OUTPUT.PUT_LINE(INITCAP(v_ename)||' got a 10% raise .');
EXCEPTION
WHEN NO_DATA_FOUND THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Invalid employee');

WHEN TOO_MANY_ROWS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('More than one employee matches this name');

WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Unknown error.');
END;
/

Non-predefined Oracle server exception

Because it's a system exception, Oracle will raise the exception if an error occurs.

Since the exception is not named, you cannot raise it explicitly, and must reference it by error code in the exception handler.

The most common examples of an unnamed system exception are

Oracle error -02291	Integrity constraint violation. Occurs when attempting to insert a child record for which no parent record exists.
Oracle error -02292	Integrity constraint violation. Occurs when attempting to delete a parent record for which children records exist.

An example PL/SQL block using a predefined Oracle server exception

ACCEPT p_deptno PROMPT 'Department to remove : '
DECLARE
v_deptno dept.deptno%TYPE:=&p_deptno;
BEGIN
DELETE FROM dept
WHERE deptno=v_deptno;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
IF SQLCODE=-2292 THEN
DBMS_OUTPUT.PUT_LINE( 'Can not delete department.');
DBMS_OUTPUT.PUT_LINE( 'Delete employees for department first.');
END IF;
END;
/
SQL> @ex_4
Department to remove : 20

Can not delete department.
Delete employees for department first.

User-defined exception

Because it's an exception defined by you, you must detect the error condition and raise the exception explicitly.

Since the exception is named, you can reference it by name.

You can define any number of exceptions relevant to the block. The scope of user-defined exceptions is limited to the block.

An example PL/SQL block using user-defined exception.

PROMPT Enter last name of an employee to determine salary.
PROMPT
SET SERVEROUTPUT ON
SET VERIFY OFF
ACCEPT p_ename PROMPT 'Enter employee last name:'
PROMPT
DECLARE
  e_security EXCEPTION;
  v_ename emp.ename%TYPE:='&p_ename';
  v_emp_rec emp%ROWTYPE;
  v_error_message VARCHAR2(255);
BEGIN
  SELECT * INTO v_emp_rec
    FROM emp
    WHERE UPPER(ename)=UPPER(v_ename);
  IF v_emp_rec.job='PRESIDENT' THEN
    RAISE e_security;
  END IF;
  DBMS_OUTPUT.PUT_LINE(INITCAP(v_ename) ||' makes '||v_emp_rec*12||' a year.');
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE( 'Invalid employee name ['||v_ename||']');

  WHEN e_security THEN
    DBMS_OUTPUT.PUT_LINE('Cannot access president''s salary ['||v_ename||']');
    INSERT INTO messages(result)
      VALUES ('Attempt access presidents salary');
END;
/

User-defined errors and EXCEPTION_INIT

It is possible to associate an non-predefined Oracle server exception with a defined user exception. If you are expecting Oracle will raise a non-predefined Oracle server exception, you can associate this error code with an identifier that you declare. This enables you to handle the error by name, and can make your code easier to debug.
There are four steps in this process: declaring the exception, associating the exception with an Oracle error code (using PRAGMA EXCEPTION_INIT), raising the exception (and Oracle will raise this exception implicitly if it occurs), and handling the exception.
ACCEPT p_deptno PROMPT 'Department to remove : '
DECLARE
  e_integrity_violation EXCEPTION;  --First declare the exception

  --Associate the exception with an error code
  PRAGMA EXCEPTION_INIT(e_integrity_violation, -2292); 
  /* From this point on, if Oracle raises error code -2292, it is 
   * raising the exception named 'e_integrity_violation'.  Likewise, 
   * if you explicitly raise the exception, the error code associated 
   * with the exception will be -2292.
   */
  v_deptno dept.deptno%TYPE:=&p_deptno;
BEGIN
  --Oracle will implicitly raise a -2292 error if integrity is violated.
  DELETE FROM dept
    WHERE deptno=v_deptno;  
EXCEPTION
  WHEN e_integrity_violation THEN 
    --Because you declared this exception and associated it with
    --Oracle error -2292, you can handle it by name
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('Can not delete department.');
    DBMS_OUTPUT.PUT_LINE('Delete employees for department first.');
END;
/

SQL> @ex_5
Department to remove : 20

Can not delete department.
Delete employees for department first.

Unnamed user-defined exception

Because it's a programmer defined exception, you must detect the error condition and raise the exception.

Since the exception is not named, you must raise it with the procedure RAISE_APPLICATION_ERROR.

Usually, exceptions raised by RAISE_APPLICATION_ERROR, are not handled by the blocks in which they are raised. The RAISE_APPLICATION_ERROR is used almost exclusively to pass error messages to a calling environment. That said, you can reference an exception generated by RAISE_APPLICATION_ERROR it in the error handling section by error code.

This technique is rarely used in anonymous blocks, but fills a need when using stored PL/SQL on the server. More attention will be given to this technique in last third of this course when we discuss stored procedures and functions.

Here is an example PL/SQL block which uses an unnamed user-defined exception:

ACCEPT p_deptno PROMPT 'Department to remove : '
DECLARE
  v_deptno dept.deptno%TYPE:=&p_deptno;
BEGIN
  IF v_deptno=40 THEN
    RAISE_APPLICATION_ERROR(-20000, 'You cannot delete department 40');
  END IF;
  DELETE FROM dept
    WHERE deptno=v_deptno;
END;
/

SQL> @ex_6
Department to remove : 40
DECLARE
*
ERROR at line 1:
ORA-20000: You cannot delete department 40
ORA-06512: at line 5