PL/SQL - Exceptions

PL/SQL - Exceptions, AskHareesh Blogspot

EXCEPTIONS

Introduction to Exceptions

  • An error condition is called an Exception
  • When an error occurs, an exception is raised i.e. normal execution stops and control transfers to the exception handling part of the PL/SQL block or subprogram
  • To handle raised exceptions, separate routines called exception handlers are written
  • There are two types of exceptions
    • Pre-defined exceptions (Internal Exceptions)
    • User-defined exceptions
  • You cannot declare an exception twice in the same block, but can declare the same exception in two different blocks
  • Exceptions declared in a block are local to that block and global to all its sub-blocks
  • Enclosing blocks cannot reference exceptions declared in a sub-block because blocks can only reference local or global exceptions
Predefined Exceptions
  • Are implicitly raised whenever a PL/SQL block violates an Oracle rule or exceeds a system-dependent limit
  • Every Oracle error has a number, but exceptions must be handled by name
  • PL/SQL predefines some common Oracle errors as exceptions
  • These predefined exceptions are declared globally by PL/SQL
  • Some Pre-defined Exceptions
    • CURSOR_ALREADY_OPEN
    • NO_DATA_FOUND
    • TOO_MANY_ROWS
    • VALUE_ERROR
    • ZERO_DIVIDE

  • More than one exception can be handled in a single exception handler by separating them with the keyword OR
EXCEPTION
WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
statements;
WHEN OTHERS THEN
statements;
END;

  
Examples of Exception handling –
1)      NO_DATA_FOUND error (Variable is not having any value.)

declare
     n emp.ename%type;
    s emp.sal%type;
begin
 select  sal into s
 from emp
 where  ename = '&n';
dbms_output.put_line('Salary is '|| s);

/* Exception
         When NO_DATA_FOUND then
           dbms_output.put_line('No record'); */
end;


2)      TOO_MANY_ROWS error (Variable is having more than one value)

declare
  s emp.sal%type;
 begin
     select sal into s
      from emp;
      dbms_output.put_line('The salary is '|| s );
 Exception
       When TOO_MANY_ROWS then
          dbms_output.put_line('Variable can hold only one value at a time');
           dbms_output.put_line('Please specify the name of person for getting the salary');
end;


3) ZERO_DIVIDE error (A number divided by zero)
declare
    x number;
    y number;
    z number;
begin
  x := &x;
  y  := &y;
   z := x/y;
  dbms_output.put_line('The answer is  '  || z);

Exception
    When  ZERO_DIVIDE  then
           dbms_output.put_line('Cannot divide by zero!!!');
    end;


4)      DUP_VAL_ON_INDEX error (When a duplicate value is entered in a column having Unique constraint)
declare
e emp.empno%type;
begin
 e := &e;
 insert into emp (empno )
values(e);
 dbms_output.put_line('Successful');
Exception
     When DUP_VAL_ON_INDEX then
         dbms_output.put_line('Value already exists');
end;


5)      VALUE_ERROR (Error in conversion of string to number)

declare
  n number;
begin
n := '&n';
dbms_output.put_line(n);
Exception
        When VALUE_ERROR then
     dbms_output.put_line('Please enter number only');
end;


6)      OTHERS (If no error handler works then at least OTHERS will work)

declare
    x number;
    y number;
    z number;
begin
  x := &x;
  y  := &y;
   z := x/y;
  dbms_output.put_line('The answer is  '  || z);

Exception
    When too_many_rows  then
           dbms_output.put_line('More than one value');
    When no_data_found then
          dbms_output.put_line('No value');
    /*When OTHERS then
     dbms_output.put_line('Some run time error has occurred');
    dbms_output.put_line('Please execute the program again with proper values.');
   rollback;*/
end;


Pragma Exception


create table dept1(deptno number primary key, dname varchar2(10));
create table emp1(empno number, ename varchar2(10),
deptno number references dept1(deptno));
insert into dept1 values(10,'Acc');
insert into emp1 values(1,'abc',10);



PRAGMA à PRAGMA EXCEPTION_INIT tells the compiler to associate an exception name with an Oracle error number. That allows you to refer to any internal exception by name and to write a specific handler for it.

declare
   referential_integrity EXCEPTION;
   PRAGMA EXCEPTION_INIT(   referential_integrity, -2292);
  begin
    Delete from dept1
     where deptno = &deptno;
    commit;
   exception
   when    referential_integrity then
    dbms_output.put_line('The record cannot be deleted, because related record found in emp1 table');
end;


SQLCODE AND SQLERRM

SQLCODE à Returns the numeric value for the error code.
SQLERRM à Returns the message associated with the error number.

create table error_log(error_number number, error_message varchar2(255));

declare
 s emp.sal%type;
  v_error_code number;
  v_error_message varchar2(255);
begin
select sal into s from emp;
exception
   when others then
     v_error_code := SQLCODE;
     v_error_message := SQLERRM;
    Insert into error_log values(v_error_code, v_error_message);
    commit;
end;


Exception Handlers in nested block to continue after run time error comes


declare
  loan_amt  number;
  no_of_months number;
  installment_rate number;
  roi number;
  tot_amt number;
begin
   loan_amt := &loan_amt;
   no_of_months := &  no_of_months;
      begin
         installment_rate :=  loan_amt / no_of_months;
       exception
         when zero_divide then
           no_of_months := 3;
           installment_rate :=  loan_amt / no_of_months;
        end;
       /* In any case the last 3 lines will get executed */
  
    roi := installment_rate * 0.2;    -- 20% roi
    tot_amt := roi + loan_amt;
    dbms_output.put_line('The total amount to be paid is '|| tot_amt);
end;

User-defined Exceptions

u User-defined exceptions need to be defined in the declarative part of a PL/SQL block, subprogram or database trigger
u Declared by naming the exception and defining it as data type EXCEPTION
u Example
DECLARE
past_due EXCEPTION;
zero_error EXCEPTION;

u Like variables, user-defined exceptions must be given names
u Unlike variables, user-defined exceptions cannot be assigned values and cannot be used in SQL statements
u They need to be raised explicitly using the RAISE statement

u A block should RAISE an exception only when an error makes it impossible or impractical to finish processing
u RAISE statement for a given expression can be coded anywhere within the scope of that expression
IF mrec.ss_fare <= 0 THEN
RAISE zero_error;
END IF;

u An exception raised inside a handler immediately propagates to the enclosing block, which is searched to find a handler for the newly raised exception
u From there on, the exception propagates normally
u To re-raise an exception place a RAISE statement in its local handler



Example of Exception variable using Raise key word
declare
  p number;
  n number := 6;
  si number;
  r number := 10.5;
 EX exception;

Begin
         p := &p;
         if p < 100 then
           raise EX;
        else
          si := (p * n * r) / 100;
           dbms_output.put_line('The Simple Interest is  '|| si);
          end if;
Exception
        When EX then
               dbms_output.put_line('The principle amt should be greater than or equal to 100.');
 end;
  --------------------------------------------------------------------------------------

RAISE_application_error 

            This can be used to create user defined error message, which can be more descriptive than named exceptions.

Syntax - :
                        Raise_application_error(error number,error message);
            where error number is any parameter between -20,000 and -20,999.Error message is text that is associated with this error. The message parameter must be less than 512 characters.

Example  of  Raise_application_error
declare
   maths number;
    Begin
    maths  := &maths;
   
           if maths < 35 then
          raise_application_error(-20001,'Failed');
           else
           dbms_output.put_line('Passed');
           end if;
 end;
   ---------------------------------------------------------------------------------------------------

Example of  Raise_application_error and error handling together –

declare
 x number;
 begin
 x := '&x';
 if x < 0 then
 raise_application_error(-20009,'ty');
 end if;
 exception
 when value_error then
 dbms_output.put_line('ff');
 end;
<<<Previous                                                                                                                                                                                  Next>>>

Complete Tutorial

*/