EXCEPTIONS
Basically or generally errors are of two types
- Syntactical
- Run time
·
We will get syntactical errors or compilation time they itself
we can rectify the compilation errors
·
To handle the run time errors we use exceptions
·
Types of exceptions
o User defined
o Pre-defined
o Non predefined
·
In user defined exceptions user has to declared the exceptions,
raise and the handle the exceptions
·
We handle the errors in exception section by using exception
handlers
·
Whereas in the case of predefined manufacturer as to define
system has to raise and user as to handle
Syntax:
Declare
Exceptioname
exception;
Begin
Raise exceptionname;
Exception
When exceptionname then
When
Exception then
End;
-------------------------------------------------------------------------------------
Predefined Exceptions:
Ex:-
Declare
Vno emp.empno%type
:=&n;
Vname emp.ename%type;
Begin
Select ename into
vname from emp where empno = vno;
Dbms_output.put_line(‘hello’);
Exception
When too_many_rows
then
Dbms_otuput.put_line(‘more
than one row’);
When no_data_found
then
Dbms_output.put_line(‘no
such employee’);
When others then
Dbms_output.put_line(‘other
error’);
End;
Oracle
Err #
|
Exception
name
|
NO_data_found
|
100
|
TOO_MANY_ROWS
|
-1422
|
CASE_NOT_FOUND
|
-6592
|
INVALID_NUMBER
|
-1722
|
VALUE_ERROR
|
-6502
|
CURSOR_ALREADY_OPEN
|
-6511
|
INVALID_CURSOR
|
-1001
|
ZERO_DIVIDED
|
-1476
|
DUP_VAL_ON_INDEX
|
-0001
|
COLLECTION_IS_NULL
|
-6531
|
SUBSCRIPT_BEYOND_COUNT
|
-6533
|
SUBSCRIPT_OUTSIDE_LIMIT
|
-6532
|
PROGRAM_ERROR
|
-6501
|
STORAGE_ERROR
|
-6500
|
LOGIN_DENIED
|
-1017
|
MEMORY_ERROR
|
|
SELF_IS_NULL
|
-30625
|
ROWTYPE_MISMATCH
|
-6504
|
NO_DATA_NEEDED
|
-6548
|
OTHERS
|
User_defined exceptions:
Declare
Ex exception;
Vno emp.empno%type
:=&n;
Vrow emp%rowtype;
Begin
Select * into vrow
from emp where empno = vno;
If vrow.comm is null
then
---------------------------------------------------------------------------------------
CASES IN EXCEPTIONS OR
EXCEPTIONAL CASES
CASE: 1
DECLARE
----------------;
BEGIN
----------------;
EXCEPTION
WHEN OTHERS THEN;
----------------------------------;
WHEN EX THEN;
---------------------------------;
END;
NOTE: It throws an error. Others should not be at first in the list of
exceptions. It should be always at last among exceptions.
------------------------------------------------------------------------------
CASE: 2
DECLARE
----------------;
BEGIN
----------------;
EXCEPTION
WHEN EX1 OR EX2 THEN;
---------------------------------------;
END;
NOTE: It is possible to
mention the exception in series by separating with ‘OR’ operator.
----------------------------------------------------------------------------
CASE: 3
DECLARE
-------------
BEGIN
-------------
EXCEPTION
WHEN EX1 OR OTHERS
THEN;
-----------------------------------------------
END;
NOTE: It is not possible to
mention the exception in series by separating with’OR’ operator.
---------------------------------------------------------------------------------
CASE: 4
DECLARE
----------------;
BEGIN
----------------;
EXCEPTION
WHEN EX1 AND EX2 THEN;
------------------------------------------;
END;
NOTE: It is not possible to
mention the exception in series by separating
with AND operator.
-------------------------------------------------------------------------------
CASE: 5
DECLARE
------------------
BEGIN
BEGIN
RAISE EX;
EXCEPTION;
WHEN EX2 THEN;
END;
EXCEPTION
WHEN EX THEN;
END;
NOTE: Exceptions raised in inner block can be handled in the outer
block. This is called
as EXCEPTION PROPAGATION.
--------------------------------------------------------------------------------
CASE:6
DECLARE
---------------;
BEGIN
RAISE EX2;
BEGIN
RAISE EX;
EXCEPTION
WHEN EX2
THEN;
END;
EXCEPTION
WHEN EX THEN;
END;
NOTE: Exception raised in outer block cannot be handled in inner
block.
----------------------------------------------------------------------------------
CASE: 7
DECLARE
----------------
BEGIN
BEGIN
RAISE
EX
EXCEPTION
WHEN
EX THEN;
--------------------------
RAISE
WHEN EX
THEN;
-----------------------;
END;
EXCEPTION
WHEN EX THEN;
-------------------------;
END;
NOTE: Exception raised in exception section cannot be handled in the
same block exceptional section. But
possible to handle outer bock exceptional sections which is also called as
EXCEPTION PROPAGATION
---------------------------------------------------------------------------
CASE: 8
DECLARE
---------------
BEGIN
DECLARE
V
NUMBER (2):=12345
BEGIN
----------------
EXCEPTION
WHEN VALUE_ERROR THEN
----------------------------------------;
END;
EXCEPTION
WHEN VALUE_ERROR THEN
------------------------------------------;
END;
NOTE: Exceptions raised in declarative section cannot be handled in
same block exception sections.
---------------------------------------------------------------------------------
TRACING AN EXCEPTION:
DECLARE
BEGIN
SELECT...................................;
SELECT...................................;
SELECT...................................;
SELECT...................................;
SELECT...................................;
SELECT...................................;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
-------------------------------------------------;
END;
NOTE: In above case we are
not able to identify in which select statement the exception has occurred. so
to overcome this situation we writ as follows.
DECLARE
----------------- ;
BEGIN
DBMS_OUTPUT.PUT_LINE('H');
SELECT............................................;
DBMS_OUTPUT.PUT_LINE('A');
SELECT............................................;
DBMS_OUTPUT.PUT_LINE('R');
SELECT............................................;
DBMS_OUTPUT.PUT_LINE('I');
SELECT............................................;
DBMS_OUTPUT.PUT_LINE('S');
SELECT............................................
DBMS_OUTPUT.PUT_LINE ('H');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
END;
NOTE:
o
If we get only 'H' as output then in first select statement the
exception has occurred.
o
If we get 'H','A' as
output then in second select statement the exception has occurred.
o
If we get 'H','A','R' as
output then in third select statement the exception has occurred.
o
If we get 'H','A','R','I'
as output then in fourth select statement the exception has occurred.
o
If we get 'H','A','R',I','S'
as output then in third select statement the exception has occurred.
o
If we get 'H','A','R','S',
H' as output then there are no error in
select statements.