Types of exceptions
Predefined exceptions
User defined exceptions
Exception propagation
PL / SQL
Exceptions
Ines BAKLOUTI
ines.baklouti@esprit.tn
Private Higher School of Engineering and Technology
Outlines
1 Types of exceptions
2 Predifined exceptions
3 User-defined exceptions
Declaration of predicted exceptions
Raising exceptions explicitly
4 Exceptions propagation
2/22
Types of exceptions
Predefined exceptions
User defined exceptions
Exception propagation
Introduction
An exception is any warning or error detected when compiling a
PL/SQL block
Exceptions due to compilation errors can be lifted due to syntax flaws,
coding errors, and several other sources.
You cannot anticipate all possible exceptions, but you can write
exception managers that allow your program to continue operating in
their presence.
Each PL/SQL block can have an exception management part, which can
have one or more exception handlers.
3/22
Types of exceptions
Predefined exceptions
User defined exceptions
Exception propagation
Introduction
4/22
Outlines
1 Types of exceptions
2 Predifined exceptions
3 User defined exceptions
Declaration of predicted exceptions
Raising exceptions explicitly
4 Exceptions propagation
5/22
Types of exceptions
Predefined exceptions
User defined exceptions
Exception propagation
Types of exceptions
Predefined exception: is an internally defined exception by PL/SQL and is implicitly
raised by the oracle server. For example: NO DATA FOUND, TOO MANY ROWS, etc.
User-defined exception: You can declare your own exceptions in the
declarative part of an anonymous PL/SQL block, sub-program (procedure,
function or trigger), or package.
Exception Has error code Has a name Implicitly lifting Explicitly lifted
Predefined always always yes Optionally
User-defined only if you give it a always No always
code
The functions of interception of exceptions are:
SQLCODE : returns the numeric value of the error code
SQLERRM : returns the error code message
6/22
Outlines
1 Types of exceptions
2 Predifined exceptions
3 User-defined exceptions
Declaration of predicted exceptions
Raising exceptions explicitly
4 Exceptions propagation
7/22
Types of exceptions
Predefined exceptions
User defined exceptions
Exception propagation
Predifined exceptions
Syntax
DECLARE
/* statements */
BEGIN
/* treatments */
EXCEPTION
WHEN exception1[OR exception2 . . .]THEN
statement1; statement2;
.. .
[WHEN exception3 [OR exception4 . . .] THEN
statement1; statement2;
. . .]
[WHEN OTHERS THEN
statement1; statement2;
. . .]
END;
8/22
Types of exceptions
Predefined exceptions
User defined exceptions
Exception propagation
Predifined exceptions
The most used predefined exceptions are:
Exception Name Code
NO DATA FOUND +100
TOO MANY ROWS -1422
ZERO DIVIDE -1476
DUP VAL ON INDEX -1
INVALID CURSOR -1001
CURSOR ALREADY OPEN -6511
INVALID NUMBER -1722
ROWTYPE MISMATCH -6504
9/22
Types of exceptions
Predefined exceptions
User defined exceptions
Exception propagation
Predifined exceptions
Exemple 1
DECLARE
lname VARCHAR2(15);
BEGIN
SELECT last name INTO lname FROM employees
WHERE first name=’John’;
DBMS OUTPUT.PUT LINE ('John's name is : ’ ||lname);
EXCEPTION
WHEN TOO MANY ROWS THEN
DBMS OUTPUT.PUT LINE ('The query returns multiple rows, use a cursor’);
WHEN NO DATA FOUND THEN
DBMS OUTPUT.PUT LINE ('No employee has the first name John');
END;
10/22
Types of exceptions
Predefined exceptions
User defined exceptions
Exception propagation
Predifined exceptions
Exemple 2
CREATE OR REPLACE PROCEDUREpr nom(prenom varchar2)IS
lname VARCHAR2(15);
BEGIN
SELECT last name INTO lname FROM employees
WHERE first name=prenom;
DBMS OUTPUT.PUT LINE ('The name of '|| first_name||’
is:’||lname); EXCEPTION
WHEN TOO MANY ROWS THEN
DBMS OUTPUT.PUT LINE ('The query returns multiple rows, use a cursor’);
WHEN OTHERS THEN
ROLLBACK;
DBMS OUTPUT.PUT LINE(’Error code: ’||SQLCODE); DBMS
OUTPUT.PUT LINE(‘Error message: ’||SQLERRM);
END;
11/22
Outlines
1 Types of exceptions
2 Predifined exceptions
3 User-defined exceptions
Declaration of predicted exceptions
Raising exceptions explicitly
4 Exceptions propagation
12/22
Types of exceptions
Predefined exceptions Declaration of predicted exceptions
User defined exceptions Raising exceptions explicitly
Exception propagation
Declaration of predicted exceptions
Example
DECLARE
a number:=100; b
number:=0;
division zero EXCEPTION;
PRAGMA EXCEPTION INIT(division zero, 01476);
BEGIN
a:=a/b;
EXCEPTION
WHEN division zero THEN
DBMS OUTPUT.PUT LINE(ERROR: Division by 0’);
END;
13/22
Types of exceptions
Predefined exceptions Declaration of predicted exceptions
User defined exceptions Raising exceptions explicitly
Exception propagation
Raising exceptions explicitly
14/22
Types of exceptions
Predefined exceptions Declaration of predicted exceptions
User defined exceptions Raising exceptions explicitly
Exception propagation
With the keyword RAISE
Syntax
DECLARE
exception name EXCEPTION;– declare the exception [PRAGMA
EXCEPTION INIT(exception name,error code)];
... BEGIN
...
RAISE exception name;– raise the exception
EXCEPTION
WHEN exception name THEN– handle exception - processing;
.. .
END;
15/22
Types of exceptions
Predefined exceptions Declaration of predicted exceptions
User defined exceptions Raising exceptions explicitly
Exception propagation
With the keyword RAISE
Exemple
CREATE OR REPLACE PROCEDURE pr check salary (current salary number) IS
salary too high EXCEPTION; – declare the exception
PRAGMA EXCEPTION INIT(salary too high,-20100); – optionally associate the code -20100
with the exception max salary NUMBER := 10000; BEGIN
IF current salary > max salary THEN
RAISE salary too high; – raise the exception
END IF;
EXCEPTION
WHEN salary too high THEN – handle the exception
DBMS OUTPUT.PUT LINE(’ERREUR : ’||SQLCODE);
DBMS OUTPUT.PUT LINE(’MESSAGE : ’||SQLERRM);
DBMS OUTPUT.PUT LINE(’Salary ’||current salary||’ is very high’);
DBMS OUTPUT.PUT LINE ('The maximum salary is’ || max salary);
END;
16/22
Types of exceptions
Predefined exceptions Declaration of predicted exceptions
User defined exceptions Raising exceptions explicitly
Exception propagation
With the RAISE APPLICATION ERROR procedure
Syntax
RAISE APPLICATION ERROR (error number, message);
With:
Error number represents a negative integer between -20000
and -20999
message is the text of the error message with a maximum
length of 2048 bytes
The RAISE APPLICATION ERROR procedure can be used in two
places:
Executable section
Exception handling section
17/22
Types of exceptions
Predefined exceptions Declaration of predicted exceptions
User defined exceptions Raising exceptions explicitly
Exception propagation
With the RAISE APPLICATION ERROR procedure
Example 1
BEGIN
DELETE FROM employees WHERE manager id = 500;
IF SQL%NOTFOUND THEN
RAISE APPLICATION ERROR(-20200, 'Invalid manager number’);
END IF;
END;
Example 2
DECLARE
mgr id NUMBER;
BEGIN
SELECT manager id INTO mgr id FROM employees WHERE employee id=300;
EXCEPTION
WHEN NO DATA FOUND THEN
RAISE APPLICATION ERROR (-20999, 'Invalid employee number’);
END;
18/22
Types of exceptions
Predefined exceptions
User defined exceptions
Exception propagation
Outlines
1 Types of exceptions
2 Predifined exceptions
3 User-defined exceptions
Declaration of predicted exceptions
Raising exceptions explicitly
4 Exceptions propagation
19/22
Types of exceptions
Predefined exceptions
User defined exceptions
Exception propagation
Exception propagation
Example 1
20/22
Types of exceptions
Predefined exceptions
User defined exceptions
Exception propagation
Exception propagation
Example 2
21/22
Types of exceptions
Predefined exceptions
User defined exceptions
Exception propagation
Exception propagation
Example 3
Rollback of all
subsequent
instructions
22/22
