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.
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');
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.
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.
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.