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
-
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.
PL/SQL procedure successfully completed.
|
-
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.
PL/SQL procedure successfully completed.
|
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
|