Exception Handling

 EXCEPTION Handling

à An exception in PL/SQL block is raised during exception of a block. It terminates the main body of action means a block always terminates when pl/SQL raises an exception.

à We can specify an exception handler to perform final action.

à If an exception raised in the executable block with there is no exception handler, then the PL/SQL block terminates with the failure. If exception is handled then the Pl/SQL block terminates successfully.

à Only one exception section is allowed with in single block and if more than one exception is required, we must use nested blocks.

à The exception handling consists of handlers of all the exceptions.

à Each exception handler consists of a when clause which specifies an exception that has to be handled.

à Syntax

Exception

When <exception 1…> then

   Sql statement1;

   Sql statement2;

When others then

   Sql statement1;

   Sql statement2;

End;

à The other exception is optional and it should always be the last handler in the block.

à Types of exceptions

1.Raised implicitly (or) Implicit Exception: - This is predefined oracle server exception which are raised by oracle server

2.Raised explicitly (or) Explicit Exception: - These exceptions are defined by user and raised by the user itself.

Predefined exception list

Error no	Exception Name	Description
ORA-00001	Duplicate_Value_On_index	Unique constraint violated.
ORA-1001	Invalid_cursor	Illegal cursor operation.
ORA-1403	NO_DATA_FOUND	No data found.
ORA-1422	TO_MANY_ROWS	A select … into statement matches more than one row.
ORA-1722	Invalid_Number	Conversion to a number failed.
ORA-6502	Value_error	Truncation, arithmetic conversion error.
ORA-6511	Cursor_already_open	When a cursor is opened without clause.
ORA-01017	Login_denied	When enter wrong username and password.
ORA-01476	0_devide	Any value is divided by the zero.
ORA-06592	Hence_Not_Found	When case not matched.

Note: -A program can have ‘n’ no of exception and many nested blocks are needed.

Example

declare

i emp%rowtype;

begin

i.empno:=:p_empno;

select ename,sal,deptno into i.ename,i.sal,i.deptno from emp where empno=i.empno;

if i.sal<2000 then

i.sal:=i.sal+i.sal*0.25;

update emp set sal=i.sal where empno=i.empno;

display('The Employee details are '||i.ename||' '||i.sal||' '||i.deptno);

end if;

exception when no_data_found then

display('The employee no is not exists please enter correct empno');

when others then

display('There is some abnormal exception');

end;

Ø  Raise_application_error: - It is built in function this enables user defined error messages.

Syntax: raise_application_error(<errorno>,<errormessage>); error no range is -20000 to -20999.

Example

declare

Vempno number;

Vsal number;

begin

select sal,empno into Vsal,Vempno from emp where empno=:p_empno;

if Vsal<2000 then

raise_application_error(-20001,'The sal is less then 2000 then so no updation');

update emp set sal=sal+200 where empno=Vempno;

else

Vsal:=Vsal+Vsal*0.15;

update emp set sal=Vsal where empno=Vempno;

display('The employee details are '||Vempno||' '||Vsal);

end if;

exception when no_data_found then

display('Yhe employee no is invalid');

end;

à User defined exception

SQLCODE: - it codes the currently raised error no.

SQLERRM: - it codes the currently raised error message.

Example: - write pl/sql program to display ename,sal if emp sal is null then raise user defined error then add sal 3000 to that employee?

declare

salary_missing exception;

i emp%rowtype;

begin

select empno,ename,sal into i.empno,i.ename,i.sal from emp where empno=:p_empno;

if i.sal is null then

raise salary_missing;

else

i.sal:=i.sal+i.sal*0.25;

end if;

update emp set sal=i.sal where empno=i.empno;

display('The employee details are '||i.ename||' '||i.sal);

exception when no_data_found then

display('The employee not exists');

when salary_missing then

display('The employee is not having salary so give the salary as 3000');

display(SQLCODE||' '||SQLERRM);

update emp set sal=3000 where empno=i.empno;

when others then

display('unknown error');

end;

à Pragma exception_init: -

Which tells compiler to associated error no with user defined exception at compile time.

Example

declare

pk_vio exception;

pragma exception_init(pk_vio,-00001);

begin

insert into dept values(40,'EXPORT','Hyd');

commit;

exception when pk_vio then

display('Duplicate deptno not allowed here');

end;