Exception Handling in PL/SQL
¢An
exception is an error situation which may arise during program execution
¢PLSQL
supports Oracle named (predefined) and unnamed (not predefined) exceptions as
well as user-defined exceptions
¢In
any type of Exception, when the exception takes place the exception is said to
be “RAISED”
¢
¢An
Exception Handler is used to specify the response for a raised exception
¢When
an exception is raised, either implicitly or explicitly, the normal execution
of the program is abandoned
¢The
control then shifts to the corresponding exception handler if it is present
else control shifts to the calling environment
¢In
any case, when an exception is fired then the control can not return to the
executable section of the current block
¢If an
exception is handled then the program unit is considered as successful
(terminate with success) i. e. the effect of the DML operations performed
before the exception is raised, can be saved else the earlier operations are
rolled back (terminate with failure)
Types of Exceptions
a) Named Exceptions:
The exceptions which are
already given names by Oracle are declared in the STANDARD package in PL/SQL.
You do not have to declare them in your own programs. You only need to handle
them.
¢Oracle has around 20 named
predefined exceptions
Trapped
within the Exception handling block
Oracle Exception Name -Oracle
Error - Explanation
1.NO_DATA_FOUND ORA-01403 -
You executed a SELECT INTO statement and no rows were returned.
2.
TOO_MANY_ROWS ORA-01422 -You
tried to execute a SELECT INTO statement and more than one row was
returned.
3.
ZERO_DIVIDE ORA-01476 -You
tried to divide a number by zero.
4.
INVALID_NUMBER ORA-01722 -You
tried to execute an SQL statement that tried to convert a string to a
number, but it was unsuccessful.
Example:
SQL> insert into emp(empno, ename)
values('ppp','ppp');
insert into emp(empno, ename)
values('ppp','ppp')
*
ERROR at line 1:
ORA-01722: invalid
number
5..
VALUE_ERROR ORA-06502 -You
tried to perform an operation and there was a error on a conversion,
truncation, or invalid constraining of numeric or character data.
6.
CURSOR_ALREADY_OPEN ORA-06511 -You
tried to open a cursor that is already open
b) UnNamed Exceptions:
It is used to trap an
internal error raised by PL/SQL or the SQL engine, which is not been given a
predefined name. Although this error is identified only by its internal error
number, exception handlers need a name by which they can check for a match .The
following are some of the unnamed exceptions which don’t have standard Oracle
names but the names can be created and assigned with the error number by the
user by using PRAGMA EXCEPTION_INIT
1. ORA-00001: unique
constraint (SCOTT.SYS_C003701) violated - raised when trying
to put a duplicate value in a column that has UNIQUE / PRIMARY KEY constraint
2. ORA-02292: integrity
constraint (SCOTT.SYS_C003731) violated - child record Found – raised when
trying to delete a parent record that has child records and while
setting the referential integrity constraint in the referencing table no “ON
DELETE” clause used
Named Exceptions
|
Set of Oracle standard exceptions that are already
named
|
Do not declare and allow the Oracle server to
raise them implicitly
|
Unnamed Exceptions
|
Set of Oracle standard exceptions that are not
given name
|
Declare within the declarative section. Associate
an exception name with an Oracle error number and allow Oracle to raise them
implicitly.
|
UserDefined Exceptions
|
A condition that the developer determines is
abnormal
|
Declare within the
declarative section and
raise explicitly
|
e.g. # 1
DECLARE
v_emp_rec emp%ROWTYPE;
BEGIN
SELECT * INTO v_emp_rec FROM
emp
WHERE empno=1001;
DBMS_OUTPUT.PUT_LINE(‘Employee Number :’ || v_emp_rec.empno);
DBMS_OUTPUT.PUT_LINE(‘Employee Name :’ || v_emp_rec.ename);
DBMS_OUTPUT.PUT_LINE(‘Employee Salary :’ || v_emp_rec.sal);
DBMS_OUTPUT.PUT_LINE(‘Employee Deptno :’ || v_emp_rec.deptno);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘Employee
Does Not Exist’);
END;
e.g. # 2
DECLARE
v_emp_rec emp%ROWTYPE;
BEGIN
SELECT * INTO v_emp_rec FROM
emp;
DBMS_OUTPUT.PUT_LINE(‘Employee Number :’ || v_emp_rec.empno);
DBMS_OUTPUT.PUT_LINE(‘Employee Name :’ || v_emp_rec.ename);
DBMS_OUTPUT.PUT_LINE(‘Employee Salary :’ || v_emp_rec.sal);
DBMS_OUTPUT.PUT_LINE(‘Employee Deptno :’ || v_emp_rec.deptno);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE(‘Too Many
Employees’);
END;
/
Raising an Exception
1.An
exception is raised implicitly if any Oracle error occurs e. g. when no rows
are retrieved by SELECT
statement,
PL/SQL raises the exception NO_DATA_FOUND
2.We
raise an exception explicitly by issuing the RAISE statement within the block
User-defined Exception: Example
¢If an
exception is raised in the executable section and there is an associated
handler, the exception is trapped (handled)
¢Handlers
for various exceptions can be specified in the EXCEPTION section
¢A
handler for an exception is of the form
WHEN exception_name THEN
a
sequence of statements
¢Example
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('WRONG
INPUT');
END;
Exception Handling: Example
DECLARE
myName VARCHAR2(8);
BEGIN
SELECT
ename INTO myName FROM emp WHERE empno = &eno;
DBMS_OUTPUT.PUT_LINE('Name
of the employee : '|| myName);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('WRONG
EMPLOYEE NUMBER');
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE(‘
Employee Name size larger than 8’);
END;
--EXECUTION
SQL> /
Enter value for eno:
7839
old
4: SELECT ename INTO
myName FROM
emp
WHERE empno =
&eno;
new
4: SELECT ename INTO
myName FROM
emp
WHERE empno =
7839;
Name of the employee : KING
PL/SQL procedure successfully completed.
SQL> /
Enter value for eno:
4545
old
4: SELECT ename INTO
myName FROM
emp
WHERE empno =
&eno;
new
4: SELECT ename INTO
myName FROM
emp
WHERE empno =
4545;
WRONG EMPLOYEE NUMBER
PL/SQL
procedure successfully completed.
The OTHERS Handler
¢Most
of the times Exceptions occur because of inappropriate values passed for user
inputs
¢In a
good program the probable exceptions are visualized and handled
¢However
for a program, one may not be able to visualize all the probable exceptions
¢Handling
all possible exceptions may make the program very bulky
¢To
overcome this problem a “General Exception Handler” named “OTHERS” is provided
by PL/SQL
¢The
handler
OTHERS
traps exceptions not handled in the other handlers in the exception handling
section of the block
¢Thus
in a program separate handlers are written for those named exceptions, that
require some specific actions to be taken when the exception is fired while for
all the remaining exceptions, the general OTHERS handler is used
Guidelines for Trapping
Exceptions
¢WHEN
OTHERS should be put up as the last handler
¢Handlers
for several exceptions can be included in a single block but for a single
exception only one handler can be written
¢At a
time there can be only one active exception
Various Predefined Named Exceptions:
Example
DECLARE
myJob emp.job%TYPE;
empRec emp%ROWTYPE;
BEGIN
myJob :='&myJob';
SELECT
* INTO empRec FROM emp WHERE UPPER(job)= UPPER(myJob);
DBMS_OUTPUT.PUT_LINE(empRec.ename||'
has
salary '||empRec.sal);
EXCEPTION
-- Various error handlers for predefined named
exceptions
WHEN
NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('The
job does not exist');
WHEN
TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('More
than one employee with the job');
/* The handler OTHERS traps exceptions not
handled in the other handlers in the exception handling section*/
WHEN
OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Sorry
Boss! Something
went
wrong :( ‘);
END;
To test various exception handlers give
calls to the block passing different value of job each time
SQL> /
Enter value for myjob:
CLERK
old
5: myJob :='&myJob';
new
5: myJob :='CLERK‘;
More than one employee with the job
PL/SQL procedure successfully completed.
SQL> /
Enter value for myjob:
Secretary
old
5: myJob :='&myJob';
new
5: myJob :=‘Secretary';
The job does not exist
PL/SQL procedure successfully completed.
SQL> /
Enter value for myjob:
Assistant Manager
old
5: myJob :='&myJob';
new
5: myJob :=' Assistant Manager';
Other error
(ORA-06502: PL/SQL: numeric or value
error: character string buffer too small)
PL/SQL procedure successfully completed.
-- Call with correct value
SQL> /
Enter value for myjob:
PRESIDENT
old
5: myJob :='&myJob';
new
5: myJob :='PRESIDENT';
KING
has salary 8734
PL/SQL procedure successfully completed.
Functions for Trapping Error Code and
Message
¢There
are two built-in functions which can be used to find out which exception
occurred during an execution of a program unit
¢SQLCODE
Returns the error number of the corresponding
Oracle error that was raised
Returns 1 for user-defined exception
¢SQLERRM
Returns the error message associated with the
given error number
Returns “USER DEFINED EXCEPTION” for
user-defined exceptions
SQLCODE and SQLERRM: Example
CREATE
TABLE errors (
erCode NUMBER,
erMessage VARCHAR2(512), who varchar2(30) ,
when date);
DECLARE
v_code
NUMBER;
v_errm VARCHAR2(512);
myNo NUMBER(1);
BEGIN
myNo := '&myNo';
EXCEPTION
WHEN
OTHERS THEN
v_code := SQLCODE;
v_errm := SQLERRM;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
INSERT INTO errors VALUES(v_code, v_errm, user, sysdate);
END;
/
Note that SQLCODE and SQLERRM can not be
directly used inside SQL statement; hence their values are assigned to
variables first and then used in INSERT statement.Maximum size of Oracle error message is
512 characters.
SQL> /
Enter value for myno: 45
old
6: myNo :=
'&myNo';
new
6: myNo :=
'45';
ORA-06502: PL/SQL: numeric or value
error: number precision too large
PL/SQL procedure successfully completed.
SQL> /
Enter value for myno: Meenal
old
6: myNo :=
'&myNo';
new
6: myNo := 'Meenal';
ORA-06502: PL/SQL: numeric or value
error: character to number conversion error
PL/SQL procedure successfully completed.
SELECT * FROM errors;
ERCODE ERMESSAGE
---------
----------------------------------------------------------------------
-6502 ORA-06502: PL/SQL: numeric or value error: number precision too
large
-6502 ORA-06502: PL/SQL: numeric or value
error: character to number conversion error
Exception Handling in PL/SQL
¢Unnamed
Oracle Exceptions
¢Unnamed
exceptions can be trapped by using OTHERS handler
¢Unnamed
exceptions can be handled by PRAGMA EXCEPTION_INIT which tells the compiler to associate an
exception name with an oracle error number
¢In
Oracle PL/SQL, PRAGMA refers
to a compiler directive or "hint"
¢PRAGMA keyword
signifies that the statement is a compiler directive, which is not processed
when the PL/SQL block is executed
¢PRAGMA is
used to provide an instruction to the compiler. The 5 types of Pragma
directives available in Oracle are listed below:
* PRAGMA AUTONOMOUS_TRANSACTION
* PRAGMA SERIALLY_REUSABLE
* PRAGMA RESTRICT_REFRENCES
* PRAGMA EXCEPTION_INIT
* PRAGMA INLINE
* PRAGMA AUTONOMOUS_TRANSACTION
* PRAGMA SERIALLY_REUSABLE
* PRAGMA RESTRICT_REFRENCES
* PRAGMA EXCEPTION_INIT
* PRAGMA INLINE
Associating
a User Defined Exception with an Error Number (or Exception Code)
A
user defined exception can be associated with an error number using PRAGMA
EXCEPTION_INIT. The pragma is a compiler directive which hints the compiler to
accept the directions provided in the program. The PRAGMA EXCEPTION_INIT
directs the compiler to align the user defined exception with a self assigned
error number. The error number must be one of the valid ORA error codes, which
are defined by the server.
PRAGMA EXCEPTION_INIT: Example
DECLARE
e_emp_exist
EXCEPTION;
--Naming
non-predefined oracle error
PRAGMA EXCEPTION_INIT(e_emp_exist , -2292);
v_deptno
dept.deptno%TYPE
:= &dno;
BEGIN
DELETE FROM dept
WHERE deptno = v_deptno;
COMMIT;
EXCEPTION
WHEN e_emp_exist
THEN
DBMS_OUTPUT.PUT_LINE(‘Can
not delete dept Employees
exist ’|| SQLERRM);
END;
To demonstrate the working of the above
program you have to verify that there is a primary key constraint on deptno
column of dept
table and referential integrity constraint on deptno column of the emp
table with deptno
column of dept
table as its referenced column
For Oracle predefined exceptions the
error numbers are negative except for NO_DATA_FOUND (+100). The error number
for a specific oracle exception can be found from the oracle documentation
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm
The error code -2292 given in above example, is for the
exception that is fired by oracle when one is trying to delete a parent record
that has child records. This exception does not have a name. So we can not
write a separate handler for it as the handler syntax needs exception name.
Hence in the above program we are associating a name with this Oracle exception
using the PRAGMA EXCEPTION_INIT so that we can then write a separate handler
for this exception using the name associated with it. However note that this
name association is local and is limited only to the above code block.
We can also locally rename the predefined
named exceptions to another name that may be more relevant for our program,
using PRAGMA EXCEPTION_INIT
User-defined Exception
¢Declared in the Declare Section with data type as EXCEPTION
¢Raised Explicitly by using the RAISE keyword
¢Local to a PL/SQL block
User-defined Exception: Example
DECLARE
e_myException EXCEPTION;
vn1
NUMBER:=10;
vn2
NUMBER:=5;
BEGIN
IF
vn1+vn2>=12 THEN
RAISE e_myException;
ELSE
DBMS_OUTPUT.PUT_LINE('no
problem');
END IF;
EXCEPTION
WHEN
OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'
'||SQLERRM);
END;
DECLARE
-- Declaring user defined exception
e_check_sal
EXCEPTION;
v_empno emp.empno%TYPE :=&empno;
emp_sal emp.sal%TYPE
:=&empsal;
mgr_sal emp.sal%TYPE;
BEGIN
SELECT e1.sal INTO mgr_sal FROM emp e,emp e1
WHERE e.mgr=e1.empno and e.empno=v_empno;
IF(emp_sal > mgr_sal) THEN
RAISE
e_check_sal; -- Raising user-defined exception
ELSE
UPDATE
emp SET sal=emp_sal WHERE empno=v_empno;
END IF;
DBMS_OUTPUT.PUT_LINE('Salary updated');
COMMIT;
EXCEPTION
--Handling user defined exception
WHEN
e_check_sal THEN
DBMS_OUTPUT.PUT_LINE('Employee
salary can not be greater than Manager salary');
WHEN
OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘ Wrong input/Some other Error’);
END;
Consider the following business rule
Revised Company policy imposed the rule
that the salary of employee should not be greater than his manager’s salary. So
for every update in salary the above checking is done using the user defined
exception e_check_Sal.
If any violation, then error message is
given else the salary is updated.
In the above program we are dealing with
only those employees that have a manager.
Exception Propagation
¢Exceptions
Raised in the Executable Section
PL/SQL uses the following rule to determine
which exception handler to invoke:
1. Current block has a handler for the exception,
execute it and terminate the block successfully. Control then passes to the
enclosing block.
2. No handler for current exception, propagate
the exception by raising it in the enclosing block. Step 1 is executed for the
enclosing block. If there is no enclosing block, the exception will be
propagated out to the calling environment, such as SQL* Plus.
¢The figure below shows the program flow,
which is followed when an exception situation occurs in the PL/SQL block and
the exception has been handled. Once the exception has been raised and trapped,
and the control moves to EXCEPTION section, the program propagates in the
forward direction.
¢If the exception has not been handled,
the program terminates abruptly or propagates to the calling environment.
¢Fig 1a: Program flow when the Exception has
been trapped and handled
¢Fig 1b: Program flow when the Exception
has not been handled
EXAMPLE:
declare
e_myexception
exception;
a number:=10;
b number:=5;
begin
if a+b>=12 then
raise xe_myexception;
else
dbms_output.put_line('no
problem');
end if;
exception
when others then
dbms_output.put_line(sqlcode||'
'||
sqlerrm);
end;/
output :
1 User-Defined Exception
No comments:
Post a Comment