, , , ,

What are the different types of Oracle Exceptions?

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
/by
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply