4.Exceptions

PL/SQL

Exception Handling in PLSQL

Handling errors raised from executable statement like select/dml operations.

Types of exceptions

  1. Pre-defined
  2. User-Defined

Pre-defined: Exceptions that are available with plsql and raised automatically
when select/dml commands failed

List of of pre-defined exceptions:

  1. When no_data_found : This will be raised when select statement returns no records
  2. When too_many_rows : This will be raised when select statement returns more than one record
  3. When divide_by_zero: This will be raised when a number divided by zero
  4. When others : This will be raised when no exception exist to the error generated by the executable statement

SYNTAX : begin
..
..
exception
when then
statement(s);
end;


Example : write a program to select employee information from the table
and handle execptions

SQL> ed filename.sql

declare
ln_eno emp.eno%type;
lc_ename emp.ename%type;
ln_sal emp.sal%type;
ln_tax number;
begin
begin
select eno,
ename,
sal
into ln_eno,
lc_ename,
ln_sal
from emp
where eno in (115);

ln_tax:=ln_sal*2/100;

dbms_output.put_line ('Eno '||ln_eno);
dbms_output.put_line ('Ename '||lc_ename);
dbms_output.put_line ('Sal '||ln_sal);
dbms_output.put_line ('Tax '||ln_tax);

exception

when no_data_found then
 dbms_output.put_line ('Emp does not exist'); 

when too_many_rows then
     dbms_output.put_line ('Mutilple records selected'); 

when others then
 dbms_output.put_line ('Other error '||SQLERRM);  

END;

begin
update emp
set sal=sal+500;

IF SQL%NOTFOUND then
dbms_output.put_line ('No records  updated');
else
dbms_output.put_line ('Updated successfully');
end if;

exception
when others then
dbms_output.put_line (‘Error in updation ‘||sqlerrm);
end;

begin
DELETE FROM emp
where eno = 100;

IF SQL%NOTFOUND then
dbms_output.put_line ('No records to DELETE');
else
dbms_output.put_line ('deleted successfully');
end if;

exception
when others then
dbms_output.put_line (‘Error in updation ‘||sqlerrm);
end;

end;

sql> @ filename.sql

Table: Bank

Acno Amt ttype

100001 15000 d
100002 25000 d
100001 5000 w
100001 2000 w
100002 4000 w

100001 5000 d

Task: Read bank acno and get the balance available. Use proper execeptions

Output
Enter value for acno : 100001
Balance available = 13000