PL/SQL Exceptions

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
User-defined exceptions need to be defined in the declarative part of a PL/SQL block, subprogram or database trigger
Declared by naming the exception and defining it as datatype EXCEPTION
Example

DECLARE
past_due EXCEPTION;
zero_error EXCEPTION;

Like variables, user-defined exceptions must be given names
Unlike variables, user-defined exceptions cannot be assigned values and cannot be used in SQL statements
They need to be raised explicitly using the RAISE statement
A block should RAISE an exception only when an error makes it impossible or impractical to finish processing

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;

An exception raised inside a handler immediately propagates to the enclosing block, which is searched to find a handler for the newly raised exception
From there on, the exception propagates normally
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;

Posted in UncategorizedTagged