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;