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;
No comments:
Post a Comment