Email Us : [email protected]
Free Training > SQL PL / SQL Forms Reports Oracle Applications
< Previous Next >

 
PL / SQL Exceptions

1) What is Exception?
Every error in Oracle is an exception.

2) Types of exceptions?

Exceptions are divided into three types
1) Pre definied exceptions
2) NoN pre definied exceptions
3) User definied exceptions

Pre Definied Exceptions:
-----------------------------
These exceptions will have exception name and exception number.
The following are some of the examples of predefinied exceptions.

EXCEPTION_NAME EXCEPTION_NUMBER
1) NO_DATA_FOUND
2) TOO_MANY_ROWS
3) ZERO_DIVIDE
4) VALUE_ERROR
5) DUP_VAL_ON_INDEX

1) NO_DATA_FOUND :
------------------------

This exception is raised when select does not return any row in PL/SQL block.

ex:
---

declare
l_sal emp.sal%type;
begin

dbms_output.put_line('Welcome');
select sal into l_Sal from emp
where empno = 2255;

dbms_output.put_line('The sal is ....'||l_sal);
dbms_output.put_line('Thank You');

end;
/

Output:
---------
Welcome
error

Note: In the above program, we get the output 'Welcome'.
This means that program execution is started.

As we dont have any employee with empno 2255, select stmt does not return any row.
When select stmt does not return any row, NO_DATA_FOUND exception is raised.

Once an exception is raised, control will not execute the remaining stmts of executable section, searches for Exception section.
As we do not have exception section in the program, it is terminated abnormally.

 

We can make sure that the program is completed normally by catching the exception using Exception section.

Syntax:
----------

Declare
.........
.........
begin
........
........
.........
Exception
When <Exception_handler> then
....................
....................
end;
/

Ex:
---

declare
l_sal emp.sal%type;
begin

dbms_output.put_line('Welcome');
select sal into l_Sal from emp
where empno = 2255;

dbms_output.put_line('The sal is ....'||l_sal);
dbms_output.put_line('Thank You');
Exception
when NO_DATA_FOUND then
dbms_output.put_line('Invalid empno');

end;
/

Output:
------
Welcome
Invalid empno

Pl/SQL Procedure successfully completed.

 

 

2) TOO_MANY_ROWS:
----------------------

TOO_MANY_ROWS exception is raised, when select stmt returns more than one row.

Ex:
----
declare
l_sal emp.sal%type;
begin

dbms_output.put_line('Welcome');
select sal into l_Sal from emp
where deptno=10;

dbms_output.put_line('The sal is ....'||l_sal);
dbms_output.put_line('Thank You');

end;
/

Output:
-----------
Welcome
Error

Note:
---------
As we get the output 'Welcome', this means that program execution is started.

As the select stmt returns more than one row, TOO_MANY_ROWS exception is raised.

As we know, Once an exception is raised control will not execute the remaining lines of excutable section, searches for the Exception section.
As we do not have exception section, program is terminated abnormally.

We can avoid abnormal termination of the program by catching the Exception.

Ex:
---
declare
l_sal emp.sal%type;
begin

dbms_output.put_line('Welcome');
select sal into l_Sal from emp
where deptno=10;

dbms_output.put_line('The sal is ....'||l_sal);
dbms_output.put_line('Thank You');
Exception
When TOO_MANY_ROWS then
dbms_output.put_line( 'Select stmt returns more than one row');

end;
/

Output:
--------

Welcome
Select stmt returns more than one row.

Pl/SQL Procedure successfully completed.

3) ZERO_DIVIDE:
-----------------

This exception is raised, when we divide a number by zero.

Ex:
----

Declare
a number(4);
begin
dbms_output.put_line('Welcome');

a := 10/0;

dbms_output.put_line(a);
dbms_output.put_line('Thank You');
end;
/

Output:
--------
Welcome
Error

Note:
------
In the above program, as we are dividing by zero, ZERO_DIVIDE exception is raised.

As we are not catching the exception, program is terminated abnormally.

As a developer, we need to make sure that programs are completed successfully at any case.
SO we need to handle exception which is raised by using the Exception Section.

Ex:
--------

Declare
a number(4);
begin
dbms_output.put_line('Welcome');

a := 10/0;

dbms_output.put_line(a);
dbms_output.put_line('Thank You');
Exception
When ZERO_DIVIDE then
dbms_output.put_line('DO not divide by 0');

end;
/

Output:
-------
Welcome
DO not divide by 0.

Pl/SQL Procedure successfully completed.

 

4) VALUE_ERROR:
-----------------------

This exception is raised, when the value which is returned does not match with the datatype variable.

Ex:
-------

Declare
l_ename number(10);

begin
dbms_output.put_line('Welcome');
select ename into l_ename from emp
where empno = 7369;

dbms_output.put_line('The employee name is...'||l_ename);
end;
/

Output:
-------
Welcome
Error

Note:
--------
As the select stmt returning char value, it cannot be stored in varible of number data.
In this case VALUE_ERROR exception is raised.
As we are not catching the exception, program is terminated abnormally.

We can avoid abnormal termination of the program by catching the exception using Exception Section.

Ex:
----

Declare
l_ename number(10);

begin
dbms_output.put_line('Welcome');
select ename into l_ename from emp
where empno = 7369;

dbms_output.put_line('The employee name is...'||l_ename);
Exception
when VALUE_ERROR then
dbms_output.put_line('Pl check the datatype of the local variables');

end;
/

Output:
--------
Welcome
Pl check the datatype of the local variables

5) DUP_VAL_ON_INDEX:
----------------------------------------
This exception is raised when we try to insert a dulicate value on a primary key or unique key.

ex:
--------
Create the following table:

create table student ( sno number(3) primary key,
sname varchar2(20),
marks number(3));

insert a row in the table:
insert into student values (101,'arun',40);

commit;

begin
dbms_output.put_line ( 'Welcome');
insert into student values (101,'vijay',50);
dbms_output.put_line ( 'Thank You');
end;
/

Output:
-----------
Welcome
Error

Note:
---------
As we are inserting a duplicate value in a primary key column, DUP_VAL_ON_INDEX exception is raised. As we are not catching the exception program is terminated abnormally.

We can avoid abnormai termination of the program by catching the exception.

Ex:
-------

begin
dbms_output.put_line ( 'Welcome');
insert into student values (101,'vijay',50);
dbms_output.put_line ( 'Thank You');
Exception
when DUP_VAL_ON_INDEX then
dbms_output.put_line('Do not insert duplicate value in a primary key');
end;
/

Output:
-------------
Welcome
Do not insert duplicate value in a primary key

When Others handler:
---------------------------------
When others can handle any type of exception

Ex1:
--------

Declare
a number(4);
begin
dbms_output.put_line('Welcome');

a := 10/0;

dbms_output.put_line(a);
dbms_output.put_line('Thank You');
Exception
When others then
dbms_output.put_line('Pl check the code');

end;
/

Output:
-----------
Welcome
Pl check the code

Note:
Exception that is raised is ZERO_DIVIDE.
We do not have ZERO_DIVIDE handler, but When Others can handler can handle this exception.

 

Ex2:
---------

declare
l_sal emp.sal%type;
begin

dbms_output.put_line('Welcome');
select sal into l_Sal from emp
where deptno=10;

dbms_output.put_line('The sal is ....'||l_sal);
dbms_output.put_line('Thank You');
Exception
When others then
dbms_output.put_line('Pl check the code');

end;
/

Output:
-------------
Welcome
Pl check the code

+++++++++++++++++++++++++++++++++++
Non predefinied exception:
-------------------------------------
These exceptions will have exceptio number , but does not have exception name.

Ex:
-------
ORA-2292 exception. This exception is raised when we try to delete from row from the parent table if correspoding row exists in the child table.

First lets establish parent-child relationship between two tables.

create table student2( sno number(3) primary key,
sname varchar2(20),
marks number(3));

insert into student2 values (101, 'arun',40);
insert into student2 values (102, 'varun',50);
insert into student2 values (103, 'kiran',60);

create table library2 ( roll_no number(3) references student2(sno),
book_name varchar2(20));

insert into library2 values (101,'Java');
insert into library2 values (102,'C++');
insert into library2 values (102,'Oracle');

commit;

begin
dbms_output.put_line('Welcome');
delete from student2 where sno =101;
dbms_output.put_line('Thank You');
end;
/

Output:
-----------
Welcome
Error

Note: We are deleteting the row from the parent table and the corresponding row exists in the child table. So exception is raised. The exception which is raised in the above program is ORA-2292. This exception does not have any name. This is an example of non -predefinied exception.

The following steps are to followed to handle non-pre definied exception.

Step 1: Declare the exception
Step 2: Associate the exception
Step 3: Handle then exception.

Syntax:
-----------
Step 1: Declare the exception

<Exception_name> Exception;

Step 2: Associate the exception

raise_application_error ( <exception_no> , <Exception_name> );

Step 3: Handle the exception

Exception
When < Exceptionb_name> then
............
...........
............
end;
/

 

Ex:
---

In the follwoing program , we perform the three step process to handle Non-pre definied exceptions.

Declare
MY_EX1 Exception;
Raise_application_error ( -2292 , MY_EX1 );

begin
dbms_output.put_line('Welcome');
delete from student2 where sno =101;
dbms_output.put_line('Thank You');

Exception
When MY_EX1 then
dbms_output.put_line('Cannot delete from the parent table');
end;
/

Output:
------------
Welcome
Cannot delete from the parent table

 

3) User definied exceptions:
--------------------------------

These exceptions are definied by the user.

Following steps are to be followed to handle user definied exceptions.

Step 1: Declare the exception
Step 2: Raise the exception
Step 3: Handle the exception.

Ex:
---

Declare
l_sal emp.sal%type;
my_ex1 exception;

begin
dbms_output.put_line('Welcome');
select sal into l_sal from emp
where empno =7902;

if l_sal > 2000 then
raise my_ex1;
end if;
dbms_output.put_line('The sal is ....'||l_sal);

Exception
When my_ex1 then
dbms_output.put_line(' Sal is too high');
When others then
dbms_output.put_line('Pl check the code');
end;
/

Output:
------------
Welcome
Sal is too high

Using raise_application_error:
------------------------------------------

raise_application_error is a procedure which is used to throw a user defined error error_number and error_message to the application.

Ex:
------

Declare
l_sal emp.sal%type;
begin
dbns_output.put_line('Welcome');

select sal into l_sal from emp where empno = 7902;

if l_sal > 2000 then
raise_application_error ( -20150, ' Sal is too high');
end if;

dbms_output.put_line('The sal is ....'||l_sal);
end;
/

Ouptut:
----------
Welcome
ORA-20150 , Sal is too high

Error Reporting functions:
------------------------------------


< Previous Next >