Ex.

No: 7
Date:
EXCEPTION HANDLING

OBJECTIVE:
To write a PL/SQL block to handle all types of exceptions.

HARDWARE REQUIREMENTS:
Processor Type : Pentium IV
RAM : 1 GB
Hard Disk : 80GB
Frequency : 1.60 GHZ

SOFTWARE REQUIREMENTS:
Front End : VB/VC ++/JAVA or equivalent
Back End : Oracle /SQL /MySQL/PostGress /DB2 or equivalent
Platform : Windows/XP, Windows 7 or its higher versions

PROBLEM DEFINITION:
The main concept is to understand the basic concepts of Exceptions in PL/SQL and to
write some simple programs using it.

BASIC TERMINOLOGIES:

Exception Handling

PL/SQL provides a feature to handle the Exceptions which occur in a PL/SQL Block
known as exception Handling. Using Exception Handling we can test the code and avoid it from
exiting abruptly.
When an exception occurs messages which explains its cause is received.
PL/SQL Exception message consists of three parts.
1) Type of Exception
2) An Error Code
3) A message
By Handling the exceptions we can ensure a PL/SQL block does not exit abruptly.
General Syntax for coding the exception section

DECLARE
Declaration section
BEGIN
Exception section
EXCEPTION
WHEN ex_name1 THEN
-Error handling statements
WHEN ex_name2 THEN
-Error handling statements
WHEN Others THEN
-Error handling statements
END;

General PL/SQL statments can be used in the Exception Block

When an exception is raised, Oracle searches for an appropriate exception handler in the
exception section. For example in the above example, if the error raised is 'ex_name1 ', then the
error is handled according to the statements under it. Since, it is not possible to determine all the
possible runtime errors during testing fo the code, the 'WHEN Others' exception is used to
manage the exceptions that are not explicitly handled. Only one exception can be raised in a
Block and the control does not return to the Execution Section after the error is handled.

If there are nested PL/SQL blocks then

DELCARE
Declaration section
BEGIN
DECLARE
Declaration section
BEGIN
Execution section
EXCEPTION
Exception section
END;
EXCEPTION
Exception section
END;

In the above case, if the exception is raised in the inner block it should be handled in the
exception block of the inner PL/SQL block else the control moves to the Exception block of the
next upper PL/SQL Block. If none of the blocks handle the exception the program ends abruptly
with an error.
Types of Exception

There are 3 types of Exceptions.

a) Named System Exceptions
b) Unnamed System Exceptions
c) User-defined Exceptions

a) Named System Exceptions

System exceptions are automatically raised by Oracle, when a program violates a
RDBMS rule. There are some system exceptions which are raised frequently, so they are pre-
defined and given a name in Oracle which are known as Named System Exceptions.

For example: NO_DATA_FOUND and ZERO_DIVIDE are called Named System exceptions.

Named system exceptions are:

1) Not Declared explicitly,
2) Raised implicitly when a predefined Oracle error occurs,
3) caught by referencing the standard name within an exception-handling routine.

Exception Name Reason Error

Number
CURSOR_ALREADY_OPEN When you open a cursor that is already open. ORA-
06511
INVALID_CURSOR When you perform an invalid operation on a ORA-
cursor like closing a cursor, fetch data from a 01001
cursor that is not opened.
NO_DATA_FOUND When a SELECT...INTO clause does not return ORA-
any row from a table. 01403
TOO_MANY_ROWS When you SELECT or fetch more than one row ORA-
into a record or variable. 01422
ZERO_DIVIDE When you attempt to divide a number by zero. ORA-
01476

b) Unnamed System Exceptions

Those system exception for which oracle does not provide a name is known as unamed
system exception. These exception do not occur frequently. These Exceptions have a code and an
associated message.

There are two ways to handle unnamed system exceptions:

1. By using the WHEN OTHERS exception handler, or
2. By associating the exception code to a name and using it as a named exception.

We can assign a name to unnamed system exceptions using a Pragma called

EXCEPTION_INIT.
EXCEPTION_INIT will associate a predefined Oracle error number to a
programmer_defined exception name.
Steps to be followed to use unnamed system exceptions are
• They are raised implicitly.
• If they are not handled in WHEN Others they must be handled explicity.
• To handle the exception explicity, they must be declared using Pragma EXCEPTION_INIT as
given above and handled referecing the user-defined exception name in the exception section.

The general syntax to declare unnamed system exception using EXCEPTION_INIT is:

DECLARE
exception_name EXCEPTION;
PRAGMA
EXCEPTION_INIT (exception_name, Err_code);
BEGIN
Execution section
EXCEPTION
WHEN exception_name THEN
handle the exception
END;

c) User-defined Exceptions
Apart from sytem exceptions we can explicity define exceptions based on business rules.
These are known as user-defined exceptions.

Steps to be followed to use user-defined exceptions:

• They should be explicitly declared in the declaration section.
• They should be explicitly raised in the Execution Section.
• They should be handled by referencing the user-defined exception name in the exception
section.

RAISE_APPLICATION_ERROR ( )

RAISE_APPLICATION_ERROR is a built-in procedure in oracle which is used to

display the user-defined error messages along with the error number whose range is in between -
20000 and -20999.

Whenever a message is displayed using RAISE_APPLICATION_ERROR, all previous

transactions which are not committed within the PL/SQL Block are rolled back automatically
(i.e. change due to INSERT, UPDATE, or DELETE statements).

RAISE_APPLICATION_ERROR raises an exception but does not handle it.

RAISE_APPLICATION_ERROR is used for the following reasons,

a) to create a unique id for an user-defined exception.
b) to make the user-defined exception look like an Oracle error.
The General Syntax to use this procedure is:

RAISE_APPLICATION_ERROR (error_number, error_message);

• The Error number must be between -20000 and -20999

• The Error_message is the message you want to display when the error occurs.

Steps to be followed to use RAISE_APPLICATION_ERROR procedure:

1. Declare a user-defined exception in the declaration section.
2. Raise the user-defined exception based on a specific business rule in the execution section.
3. Finally, catch the exception and link the exception to a user-defined error number in
RAISE_APPLICATION_ERROR.

PRE-DEFINED EXCEPTIONS

PL/SQL provides many pre-defined exceptions, which are executed when any database
rule is violated by a program. For example, the predefined exception NO_DATA_FOUND is
raised when a SELECT INTO statement returns no rows. The following table lists few of the
important pre-defined exceptions:

Oracle
Exception SQLCODE Description
Error

It is raised when a null object is automatically

ACCESS_INTO_NULL 06530 -6530
assigned a value.

It is raised when none of the choices in the

CASE_NOT_FOUND 06592 -6592 WHEN clauses of a CASE statement is
selected, and there is no ELSE clause.

It is raised when a program attempts to apply

collection methods other than EXISTS to an
uninitialized nested table or varray, or the
COLLECTION_IS_NULL 06531 -6531
program attempts to assign values to the
elements of an uninitialized nested table or
varray.

DUP_VAL_ON_INDEX 00001 -1 It is raised when duplicate values are

 attempted to be stored in a column with
unique index.

It is raised when attempts are made to make a

INVALID_CURSOR 01001 -1001 cursor operation that is not allowed, such as
closing an unopened cursor.

It is raised when the conversion of a character

INVALID_NUMBER 01722 -1722 string into a number fails because the string
does not represent a valid number.

It is raised when s program attempts to log on

LOGIN_DENIED 01017 -1017 to the database with an invalid username or
password.

It is raised when a SELECT INTO statement

NO_DATA_FOUND 01403 +100
returns no rows.

It is raised when a database call is issued

NOT_LOGGED_ON 01012 -1012
without being connected to the database.

It is raised when PL/SQL has an internal

PROGRAM_ERROR 06501 -6501
problem.

It is raised when a cursor fetches value in a

ROWTYPE_MISMATCH 06504 -6504
variable having incompatible data type.

It is raised when a member method is invoked,

SELF_IS_NULL 30625 -30625 but the instance of the object type was not
initialized.

It is raised when PL/SQL ran out of memory

STORAGE_ERROR 06500 -6500
or memory was corrupted.
 It is raised when s SELECT INTO statement
TOO_MANY_ROWS 01422 -1422
returns more than one row.

It is raised when an arithmetic, conversion,

VALUE_ERROR 06502 -6502
truncation, or size-constraint error occurs.

It is raised when an attempt is made to divide

ZERO_DIVIDE 01476 1476
a number by zero.
EXERCISE:

1) PL/SQL program to implement Check Exception Type

SQL>
SQL> DECLARE
2 num_a NUMBER := 6;
3 num_b NUMBER;
4 BEGIN
5 num_b := 0;
6 num_a := num_a / num_b;
7 num_b := 7;
8 dbms_output.put_line(' Value of num_b ' || num_b);
9 EXCEPTION
10 WHEN ZERO_DIVIDE
11 THEN
12 dbms_output.put_line('Trying to divide by zero');
13 dbms_output.put_line(' Value of num_a ' || num_a);
14 dbms_output.put_line(' Value of num_b ' || num_b);
15 END;
16 /

OUTPUT
Trying to divide by zero
Value of num_a 6
Value of num_b 0

PL/SQL procedure successfully completed.

2) PL/SQL program to implement When Other Exceptions Then

SQL> -- when others then

SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
2 PARENT_ERROR exception;
3 BEGIN
4 DECLARE
5 CHILD_ERROR exception;
6 BEGIN
7 raise CHILD_ERROR;
 8 EXCEPTION
9 WHEN CHILD_ERROR THEN
10 dbms_output.put_line('nested block exception handler');
11 raise;
12 END;
13 EXCEPTION
14 WHEN PARENT_ERROR THEN
15 dbms_output.put_line('parent block exception handler');
16 WHEN OTHERS THEN
17 dbms_output.put_line('Caught the OTHERS exception');
18 raise;
19 END;
20 /
22

OUTPUT

nested block exception handler

Caught the OTHERS exception
declare
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 19

3)PL/SQL program to implement Declaration Exception

SQL> DECLARE
2 myNumber number default 'MY NUMBER';
3 BEGIN
4 NULL;
5 EXCEPTION
6 WHEN OTHERS THEN
7 dbms_output.put_line('Exception caught');
8 raise;
9 END;
10 /
OUTPUT

declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 2

4) PL/SQL program to display the details of customer using Exception Handling

SQL> select * from customer;

C_ID C_NAME C_ADDR

---------- -------------------- --------------------

11 arun mumbai

22 harish delhi

33 deepak kolkata

SQL> declare

2 cid customer.c_id%type;

3 cname customer.c_name%type;

4 caddr customer.c_addr%type;

5 begin

6 cid:=&cid;

7 select c_name,C_addr into cname,caddr

8 from customer

9 where c_id=cid;

10 dbms_output.put_line('Name:'||cname);

11 dbms_output.put_line('Address:'||caddr);

12 exception
13 when no_data_found then

14 dbms_output.put_line('No such customer!');

15 when others THEN

16 dbms_output.put_line('Error!');

17 end;

18 /

OUTPUT

/* for a valid customer id */

Enter value for cid: 22

old 6: cid:=&cid;

new 6: cid:=22;

Name:harish

Address:delhi

PL/SQL procedure successfully completed.

/* for an Invalid customer id */

SQL> /

Enter value for cid: 44

old 6: cid:=&cid;

new 6: cid:=44;

No such customer! (Exception raised)

PL/SQL procedure successfully completed.

MERITS:

 Using exceptions for error handling has several advantages. With exceptions, you can
reliably handle potential errors from many statements with a single exception handler

 Instead of checking for an error at every point where it might occur, add an exception
handler to your PL/SQL block. If the exception is ever raised in that block (including
inside a sub-block), it will be handled.

 Isolating error-handling routines makes the rest of the program easier to read and
understand.

DEMERITS:

Using exceptions for error handling has two disadvantages:

 First, exceptions can trap only runtime errors. Therefore, a PL/SQL program cannot trap
and recover from compile-time (syntax and semantic) errors such as table or view does
not exist.
 Second, exceptions can mask the statement that caused an error

URLs:

 [Link]
[Link]
 [Link]
keys
 [Link]
 http:/[Link]/oracle/catalog/[Link]

VIVA QUESTIONS:

1. What is an Exception?
2. What is the function of Exception Handler?
3. Mention the different types of Exception.

RESULT:

Thus the PL/SQL block is created to handle all types of exceptions and are well executed.