Welcome

PL SQL Tutorial

---

PL SQL Exceptions

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

• Database defined Exceptions, Built-in Exceptions
• User-Defined Exceptions Custom 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

Syntax: for exception handling

      		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

PL/SQL Exception ZERO_DIVIDE

        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 ;

PL/SQL Exception NO_DATA_FOUND

    NO_DATA_FOUND exception is thrown,when given condition in where clause doesnot satisfy any resultset

ORA-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.

PL/SQL Exception OTHERS

   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

     SQLCODE returns Error Number

• SQLERRM

     Exception Details stored in SQLERRM, These details stored by Database Server

Replace above exception handler for OTHERS with the following code

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

PL/SQL User Defined Exceptions

    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,

• Built-in Exceptions are raised by Database Server
• Manually Programmer has to raise User-Defined Exceptions.
• Built-in Exceptions,Error No, and Message associated with that error are predefined in the database server. Where as in User-Defined Exceptions,Programmer to choose this option.

Raising Exceptions

    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:

      			RAISE exception__;
      		

User-Defined Exception Example

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.

Propogating Exceptions

Exceptions in Nested Blocks

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