This example will function as long as the specified name returns exactly one row. If the singleton select returns more or less than one row, Oracle will raise an exception:



Exceptions

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

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.

PL/SQL procedure successfully completed.


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.


SQL> BEGIN
2 FOR i IN REVERSE 0..5 LOOP
3 DBMS_OUTPUT.PUT_LINE(120/i); --Statement will fail when i=0
4 END LOOP;
5 END;
6 /
24
30
40
60
120

BEGIN
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 3


SQL> BEGIN
2 INSERT INTO emp (empno, ename)
3 VALUES (7839,'GATES'); --Insert statement violates table constraints
4* END;

SQL> /

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]

PL/SQL procedure successfully completed.

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

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

Predefined Oracle server exception

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

User-defined exception

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.

PL/SQL procedure successfully completed.

Unnamed user-defined exception