The main classification is Oracle Defined and User defined Exceptions
User Defined:
User defined exceptions are defined in many ways.
a.New user defined Exceptions can be created using existing Oracle defined Exceptions
b.Entirely new Exceptions can be according to a users need.
Eg: Raise an exception if employee salry should not be negative value.
There are mainly 3 ways to use User Defined Exceptions
A. RAISE EXCEPTION
declare
exc_user Exception; –declare exception
begin
–code–
exception when others then
raise exc_user;
–exception raised
exception
when exc_user then
–handler for exc_user. exception handled
when others then
–handler for others
end;
B. RAISE_APPLICATION_ERROR
declare
exc_user Exception; –declare exception
begin
if (<logic>) then
RAISE_APPLICATION_ERROR(-20001, exc_user);
–code greater than -20000
exception
when exc_user then
–handled when error occurs with the specified Oracle Code
when others then
–handler for others
C. PRAGMA EXCEPTION_INIT
declare
exc_user Exception; –declare exception
PRAGMA EXCEPTION_INIT(exc_user, -<oracle_error_code>);
— Oracle code with ‘-‘sign
begin
–code–
exception
when exc_user then
–handler for exc_user –handled when error occurs with the specified Oracle Code
when others then
–handler for others
Oracle defined:
There are many built in exceptions which we can use. Most commonly used ones are:
a.NO_DATA_FOUND
b.TOO_MANY_ROWS_FOUND
c.ZERO_DIVIDE
d.CURSOR_ALREADY_OPEN
e.INVALID_CURSOR
f.DUP_VALUE_ON_INDEX
g.VALUE_ERROR
h.INVALID_NUMBER
Difference between Value Error and Invalid Number
See below are the examples:
Eg1:
SQL> select to_number (‘a’)
from dual
Error :ORA-01722: invalid number
Eg2:
SQL> declare
n number;
begin
n := ‘a’;
exception
when value_error
then
dbms_output.put_line (‘Value Error’);
end;
Error: Value Error
Eg3:
SQL> declare
n number;
begin
select ‘a’ into n
from dual ;
exception
when value_error then
dbms_output.put_line (‘Value Error’);
end;
Error: Value Error
Eg4:
SQL> declare
n number;
begin
select to_number(‘a’) into n
from dual;
exception
when value_error then
dbms_output.put_line (‘Value Error’);
when invalid_number then
dbms_output.put_line (‘Invalid Number’);
end;
Error:Invalid Number
User Defined:
User defined exceptions are defined in many ways.
a.New user defined Exceptions can be created using existing Oracle defined Exceptions
b.Entirely new Exceptions can be according to a users need.
Eg: Raise an exception if employee salry should not be negative value.
There are mainly 3 ways to use User Defined Exceptions
A. RAISE EXCEPTION
declare
exc_user Exception; –declare exception
begin
–code–
exception when others then
raise exc_user;
–exception raised
exception
when exc_user then
–handler for exc_user. exception handled
when others then
–handler for others
end;
B. RAISE_APPLICATION_ERROR
declare
exc_user Exception; –declare exception
begin
if (<logic>) then
RAISE_APPLICATION_ERROR(-20001, exc_user);
–code greater than -20000
exception
when exc_user then
–handled when error occurs with the specified Oracle Code
when others then
–handler for others
C. PRAGMA EXCEPTION_INIT
declare
exc_user Exception; –declare exception
PRAGMA EXCEPTION_INIT(exc_user, -<oracle_error_code>);
— Oracle code with ‘-‘sign
begin
–code–
exception
when exc_user then
–handler for exc_user –handled when error occurs with the specified Oracle Code
when others then
–handler for others
Oracle defined:
There are many built in exceptions which we can use. Most commonly used ones are:
a.NO_DATA_FOUND
b.TOO_MANY_ROWS_FOUND
c.ZERO_DIVIDE
d.CURSOR_ALREADY_OPEN
e.INVALID_CURSOR
f.DUP_VALUE_ON_INDEX
g.VALUE_ERROR
h.INVALID_NUMBER
Difference between Value Error and Invalid Number
See below are the examples:
Eg1:
SQL> select to_number (‘a’)
from dual
Error :ORA-01722: invalid number
Eg2:
SQL> declare
n number;
begin
n := ‘a’;
exception
when value_error
then
dbms_output.put_line (‘Value Error’);
end;
Error: Value Error
Eg3:
SQL> declare
n number;
begin
select ‘a’ into n
from dual ;
exception
when value_error then
dbms_output.put_line (‘Value Error’);
end;
Error: Value Error
Eg4:
SQL> declare
n number;
begin
select to_number(‘a’) into n
from dual;
exception
when value_error then
dbms_output.put_line (‘Value Error’);
when invalid_number then
dbms_output.put_line (‘Invalid Number’);
end;
Error:Invalid Number
Recent Comments