Exception is an uneven break in the program flow due to logical issues in the PL/SQL block. It is raised by Oracle server and the execution process is aborted as soon as it is encountered. It can be trapped and handled in the EXCEPTION section of a PL/SQL block. Note that it is an optional section in a block, which plays a vital role in large applications.
From the above figure, the execution control moves to EXCEPTION section (if available), to check whether the current exception has been handled or not. If it has been handled, then set of statements are executed before successful termination, else it terminated and the exception raised propagates to the host.
For example, the below PL/SQL block selects an employee with the name CLUB, which does not exists. Therefore, Oracle server raises an exception NO_DATA_FOUND and terminates the block just after raising the exception.
Code sql:
DECLARE L_EMPID NUMBER; L_DEPTNO NUMBER; BEGIN DBMS_OUTPUT.PUT_LINE ('Selecting an Employee'); SELECT EMPID, DEPTNO INTO L_EMPID, L_DEPTNO FROM EMPLOYEE WHERE ENAME = 'CLUB'; DBMS_OUTPUT.PUT_LINE ('Employee selected'); END; / Selecting an Employee DECLARE * ERROR at line 1: ORA-01403: no DATA found ORA-06512: at line 6
2. Trapping Exceptions
All exceptions raised in the PL/SQL section are trapped in EXCEPTION section of the block. Below is the syntax of catching and handling exceptions.
Code :
EXCEPTION
WHEN exception1 [OR exception2 . . .] THEN
statement1;
statement2;
. . .
[WHEN OTHERS THEN
statement1;
statement2;
. . .]3. Notes
i. Multiple exception handlers can reside in an EXCEPTION section, where each one must be handled once.
ii. Only one exception can be executed in a single program execution.
iii. WHEN OTHERS is a generic exception, which can substitute any unknown exception. It must be handled once and at the end of all handlers.
4. Handling Exceptions in PL/SQL
As we saw in first example, Oracle server implicitly raises the exception encountered during block execution, if it is not handled by the programmer. These exceptions can be handled to direct an alternate action in the Exception section. Note that once the execution pointer enters EXCEPTION section, it moves forward direction only to terminate the block.
Predefined exception can be explicitly raised at any stage in the block, if required, by the developer using RAISE statement.
Programmer can also prototype his own exception and raise the same using RAISE statement.
5. Pre – defined exceptions
Oracle server defines a bracket of exceptions. If unhandled, they are raised implicitly by the server, which we have already noticed in earlier examples. Else they can be raised and handled explicitly too.
Below table lists some commonly used exceptions. They are predefined in oracle STANDARD package.
No code has to be inserted here.
6. Raising Pre-defined exceptions explicitly
System defined exceptions can be explicitly raised and handled by the programmer. The PL/SQL block below fetches the employees with JOB_ID as QTY. If no employee exists with such job id, program must show appropriate message.
Code sql:
DECLARE CURSOR C IS SELECT EMPID FROM EMPLOYEE WHERE JOB_ID = 'QTY'; L_EMPID VARCHAR2(100); BEGIN OPEN C; FETCH C INTO L_EMPID; IF C%ROWCOUNT = 0 THEN RAISE NO_DATA_FOUND; END IF; CLOSE C; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('NO EMPLOYEE EXIST WITH JOB ID QTY'); END; / NO EMPLOYEE EXIST WITH JOB ID QTY PL/SQL PROCEDURE successfully completed.
7. User defined exceptions
These exceptions are not defined by the oracle server. They are declared in the DECLARE section with type EXCEPTION and used in the execution block based on the requirement. There are two ways of using user defined exceptions in the PL/SQL blocks.
i. PRAGMA EXCEPTION_INIT to associate exception with an error number
ii. Just raise the defined exception to pass the control to EXCEPTION section
Below is the PL/SQL block structure showing usage of User defined exception
Code :
DECLARE
[EXP] EXCEPTION;
PRAGMA EXCEPTION_INIT([EXP],[ERROR NUMBER], [TRUE | FALSE])
BEGIN
[STATEMENTS]
IF [CONDITION] THEN
RAISE [EXP]
END IF;
..
..
EXCEPTION
WHEN [EXP] THEN
[ACTION]
…
END;In the above syntax, EXP is the user defined exception name. PRAGMA is a compiler directive which instructs the compiler to interpret occurrences of exception name as that of the error number instead of compilation. Third parameter, FALSE by default, allows to keep error stack contents.
Example, the PL/SQL block below associates error number -1422 with the user defined exception EX_TEST. Note that -1422 corresponds to exception NO_DATA_FOUND. At any instance of the program execution, if oracle server encounters -1422, it raises EX_TEST exception and jumps to EXCEPTION section.
Code sql:
DECLARE L_EMPID NUMBER; EX_TEST EXCEPTION; PRAGMA EXCEPTION_INIT(EX_TEST,-1422); BEGIN SELECT EMPID INTO L_EMPID FROM EMPLOYEE WHERE JOB_ID = 'QTY'; EXCEPTION WHEN EX_TEST THEN DBMS_OUTPUT.PUT_LINE('Control Moved to Exception Block'); END; / Control Moved TO Exception Block PL/SQL PROCEDURE successfully completed.
Above exception could have also been raised using RAISE statement without using PRAGMA and slight change in code. Execution flow remains same as above. Note the use of cursor in the block.
Code sql:
DECLARE CURSOR C IS SELECT EMPID FROM EMPLOYEE WHERE JOB_ID = 'QTY'; L_EMPID NUMBER; EX_TEST EXCEPTION; BEGIN OPEN C; FETCH C INTO L_EMPID; IF C%ROWCOUNT = 0 THEN RAISE EX_TEST; END IF; CLOSE C; EXCEPTION WHEN EX_TEST THEN DBMS_OUTPUT.PUT_LINE('Control Moved to Exception Block'); END; / Control Moved TO Exception Block PL/SQL PROCEDURE successfully completed.
8. Exception trapping functions
Example, the PL/SQL block below attempts to divide an integer with zero. The exception is handled
Code sql:
DECLARE L_NUM1 NUMBER; L_NUM2 NUMBER; BEGIN L_NUM1 := 10; L_NUM2 := 0; DBMS_OUTPUT.PUT_LINE('RESULT:'||L_NUM1/L_NUM2); EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE(SQLCODE); DBMS_OUTPUT.PUT_LINE(SQLERRM); END; / -1476 ORA-01476: divisor IS equal TO zero PL/SQL PROCEDURE successfully completed.
9. Exception propagation
Below figures show the propagation of exception.
Figure 1: Inner block raises and handles exception A. After inner block is terminated, control resumes with statements following the inner block in outer block.
Figure 2: Exception A is raised but not handled in the inner block. It propagates to the Exception section of Outer block, which handles it.
Figure 3: Inner block raises exception A but not handles it. It propagates to Exception section of Outer block, which has no handling for A. As the result, unhandled exception error is raised i.e. the exception is propagated to the host with an error message and operation aborted.
