PL SQL Tutorial
In PL SQL There are 2 types of errors. 1. Compile Time Error 2. Run-time Errors. Exceptions comes under Run-Time Errors. Exception are raised by PL-SQL Code while executing. Exceptions can occur during program execution, may be due to hardware failure, storage error, some arithmetic error division by zero etc., Whenever there is a Exception in Pl-SQL Code, program will terminate abruptly.Programmer cannot anticiate all possible exceptions. Diligently Programmer can handle exceptions using Exception Handlers.
Exception Block is part of Excection Block. Exception Block can be included in Anonymous blocks, stored procedures, functions, and in Packages too.
There are 2 types of Exceptions
|NO_DATA_FOUND||When SELECT INTO query returns no rows found|
|TOO_MANY_ROWS||Record variable can hold single record at a time, When SELECT INTO Query returns more than one row,|
|OTHERS||general exception,i.e it can handle any type of exception. It should be last in the Exception Block|
|ZERO_DIVIDE||Dividing any number by zero, raises this exception|
|VALUE_ERROR && INVALID_NUMBER||INVALID_NUMBER occurs unable to convert charater String to number.
VALUE_ERROR ocuurs when truncation or constraint violation
BEGIN ...... EXCEPTION WHEN EXCEPTION1 THEN --Handler 1 WHEN EXCEPTION2 THEN --Handler 2 WHEN EXCEPTION3 THEN --Handler 3 WHEN EXCEPTION4 THEN --Handler 4 ..... WHEN OTHERS THEN -- OTHERS Handler END;
Exception Block should be included at the end of the Execution Block, This should be last in the Execution Section, after that no SQL Statements are permitted. Exception1..N are built-in Exceptions or user defailed exceptions. OTHERS exception handler should be in the last Handler in the Exception Block, if it included somewhere in the exception block, compiler throws an error
ORA-01476: divisor is equal to zero
DECLARE A NUMBER :=10; B NUMBER :=0; C NUMBER :=0; BEGIN SELECT A/B INTO C FROM DUAL; EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUPUT.PUT_LINE('DIVISOR SHOULD NOT BE ZERO'); END ;
NO_DATA_FOUND exception is thrown,when given condition in where clause doesnot satisfy any resultsetORA-01403: no data found
DECLARE DEPT_REC dept%ROWTYPE; BEGIN SELECT deptno,dname,loc INTO dept_rec from dept WHERE 1=2; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('NO DATA AVAILABLE'); END;OUTPUT:
SQL> / NO DATA AVAILABLE PL/SQL procedure successfully completed.
OTHERS captures all types of exception raised by database server.The Exception Handler for OTHERS should be in the last in the Exception Block, if Exception Block has more than one Exception Handler.
set serveroutput on DECLARE EMP_REC EMP%ROWTYPE; BEGIN SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO INTO EMP_REC FROM EMP WHERE DEPTNO=10; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('OTHERS EXCEPTION HANDLER RAISED'); END;OUTPUT:
OTHERS EXCEPTION HANDLER RAISED PL/SQL procedure successfully completed.
In above case, we don't know exact reason, what caused the database to raise OTHERS exception Handler. In order to find out the reason, PL/SQL supports 2 keywords for execption details, SQLERRM, SQLCODE
SQLCODE returns Error Number
Exception Details stored in SQLERRM, These details stored by Database Server
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('OTHERS EXCEPTION HANDLER RAISED'); DBMS_OUTPUT.PUT_LINE(SQLCODE||' -->'||SQLERRM); END;OUTPUT:
OTHERS EXCEPTION HANDLER RAISED -1422 -->ORA-01422: exact fetch returns more than requested number of rows
User can define their own Exceptions, Every built-in exception has SQLCODE and SQLERRM, i.e number and message associated with that excpetion, Similarly Programmer can assign number and message to User-Defined Exceptions. Main difference between Built-in Exceptions vs User-Defined Exceptions is,
Raise statement is used to raise an exception, Once this statement is executed, Control will automatically goes to Exception Block, and search for this Exception.Synatx:
DECLARE E EXCEPTION; BEGIN RAISE E; EXCEPTION WHEN E THEN DBMS_OUTPUT.PUT_LINE('USER DEFINED EXCEPTION'); END; SQL> / user defined exception
Database can raise exceptions , this process is Automatic.
Programer can also raise exceptions manually or whenever he needs in the prorgam.
Scope of the Exceptions are within the Block,or Exception Raised in Inner Block,is handled in that block only,If not it should be handled immediate outer block.
DECLARE BEGIN DECLARE E EXCEPTION; BEGIN RAISE E; EXCEPTION WHEN E THEN DBMS_OUTPUT.PUT_LINE('INNER E'); RAISE E; END; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('OUTER OTHERS'); END; SQL>/ INNER E OUTER OTHERS