Introduction of Exception Handling in PL/SQL

A PL/SQL block may contain statements that specify exception handling routines. Each error or warning during the execution of a PL/SQL block raises an exception. One can distinguish between two types of exceptions:

• system defined exceptions
• user defined exceptions (which must be declared by the user in the declaration part of a block
where the exception is used/implemented)

System defined exceptions are always automatically raised whenever corresponding errors or
warnings occur. User defined exceptions, in contrast, must be raised explicitly in a sequence of statements using raise . After the keyword exception at the end of a block, user defined exception handling routines are implemented. An implementation has the pattern

when then ;

The most common errors that can occur during the execution of PL/SQL programs are handled by system defined exceptions. The table below lists some of these exceptions with their names and a short description.

Exception name Number Remark
CURSOR ALREADY OPEN ORA-06511 – You have tried to open a cursor which is already open
INVALID CURSOR ORA-01001 Invalid cursor operation such as fetching from a closed cursor
NO DATA FOUND ORA-01403 A select . . . into or fetch statement returned no tuple
TOO MANY ROWS ORA-01422 A select . . . into statement returned more than one tuple
ZERO DIVIDE ORA-01476 You have tried to divide a number by 0

Example:
declare
emp sal EMP.SAL%TYPE;
emp no EMP.EMPNO%TYPE;
too high sal exception;
begin
select EMPNO, SAL into emp no, emp sal
from EMP where ENAME = ’KING’;
if emp sal * 1.05 > 4000 then raise too high sal
else update EMP set SQL . . .
end if ;
exception
when NO DATA FOUND – – no tuple selected
then rollback;
when too high sal then insert into high sal emps values(emp no);
commit;
end;

After the keyword when a list of exception names connected with or can be specified. The last
when clause in the exception part may contain the exception name others. This introduces the default exception handling routine, for example, a rollback.

If a PL/SQL program is executed from the SQL*Plus shell, exception handling routines may
contain statements that display error or warning messages on the screen. For this, the procedure
raise application error can be used. This procedure has two parameters and . is a negative integer defined by the user and must range
between -20000 and -20999. is a string with a length up to 2048 characters.
The concatenation operator “||” can be used to concatenate single strings to one string. In order
to display numeric variables, these variables must be converted to strings using the function
to char. If the procedure raise application error is called from a PL/SQL block, processing the PL/SQL block terminates and all database modifications are undone, that is, an implicit rollback is performed in addition to displaying the error message.

Example:
if emp sal * 1.05 > 4000
then raise application error(-20010, ’Salary increase for employee with Id ’
|| to char(Emp no) || ’ is too high’);