EXCEPTION HANDLING

EXCEPTION HANDLING

An Exception is an identifier in PL/SQL that is raised during execution that terminates main body of action.

EXCEPTION is raised by two methods
- An Oracle error occurs and then associated exception is raised automatically.
- Using RAISE statement within the block, the exception being raised may be user defined or predefined

EXCEPTION is three type
1) Pre Defined Exception
2) User-Defined Exception
3) Non-Pre Defined Exception

1) Pre Defined Exception
The predefined oracle package standard has defined exceptions for certain common errors. Exceptions are handled using names not using error numbers.
The errors which are not handles using exception, can be handled usinf exception ‘OTHERS’
Examples
Too_many_rows
No_data_found
Zero_divide
others

Some Sample Programs Using Pre-Defined Exceptions

1) Using no_data_found Exception
declare
mename emp.ename%type;
msal emp.sal%type;
begin
select ename,sal into mename,msal from emp where empno=7902 ;
if sql%rowcount > 0 then
dbms_output.put_line(mename||msal) ;
end if;
exception
when no_data_found then
dbms_output.put_line('Invaild empno') ;
end;

2) Using zero_divide Exceptions
declare
x number(2):=22 ;
y number(2):=0 ;
z number ;
begin
z:=x/y ;
dbms_output.Put_line(z) ;
exception
when zero_divide then
dbms_output.Put_line('The values is divided by Zero') ;
end;

3) Using too_many_rows Exception
declare
mename emp.ename%type ;
msal emp.sal%type ;
begin
select ename,sal into mename,msal from emp where deptno=30 ;
dbms_output.Put_line('The emp name is '||mename||' salary is '||msal) ;
exception
when too_many_rows then
dbms_output.Put_line(' Too many rows returned') ;
end;

4) Using too_many_rows and others Exception

declare
x emp.sal%type;
y emp.deptno%type:=&deptno;
begin
select sal into x from emp where deptno=y;
dbms_output.put_line('sal of empno is='||x);
dbms_output.put_line('wellcome');
exception
when too_many_rows then
dbms_output.put_line('more then one error selected');
when others then
dbms_output.put_line('no record found');
end;

User Defined Exception

User defined exceptions must be
- declared in the declare section of a PL/SQL Block
- Raised Explicitely with RAISE statements

Some Sample Programs Using User Defined Exceptions
1)
declare
v_empno number(4):=7902 ;
E_invalidDept Exception;
begin
delete from emp where empno=v_empno ;
if SQL%NOTFOUND Then
RAISE E_invalidDept;
end if ;
Exception
WHEN E_invalidDept then
DBMS_OUTPUT.PUT_LINE('Wrong EMPNO') ;
end ;
2)
declare
mename emp.ename%type ;
msal emp.sal%type ;
emp_exc exception ;
begin
select ename,sal into mename,msal from emp where empno=7902 ;
if msal > 4000 then
Raise emp_exc ;
else
dbms_output.Put_line(msal) ;
end if;
exception
when emp_exc then
dbms_output.put_line('salary is greater than 4000') ;
end;

RAISE_APPLICATION_ERROR Procedure:
Is used to communicate a predefined exception interactively by defining a non standard error code and error message.

RAISE_APPLICATION_ERROR can be used in either the executable section or exception section of a PL/SQL program.

1) declare
v_empno number(4):=&empno ;
begin
delete from emp where empno=v_empno ;
if SQL%NOTFOUND Then
RAISE_APPLICATION_ERROR (-20202,'wrong empno') ;
end if ;
end ;

2)
declare
x emp.sal%type;
y emp.empno%type:=&empno;
sal_exc exception ;
begin
select sal into x from emp where empno=y;
if x > 3000 then
raise sal_exc;
else
dbms_output.put_line('sal of empno is='||x);
dbms_output.put_line('wellcome');
end if;
exception
when sal_exc then
raise_application_error(-20001,'Salary is greater than 3000') ;
when others then
dbms_output.put_line('no data found');
end;