CODEWITHSUNDEEP
oracle-databaseexceptioncachingplsql
Jai Barathi
Jai Barathi

Reputation: 11

Catching multiple exception at once in oracle

Please let me know whether it is possible to catch multiple exception at same time in oracle. Not like 1 user defined and 1 is oracle default .I need to catch multiple user defined exception at same time . Kindly let me know how to do .

Thank you !

Upvotes: 0

Views: 6838

Answers (2)

Belayer
Belayer

Reputation: 14861

Yes, you can do what you want from "When Others" as indicated by @Littlefoot or bulk processing errors (not covered here). But additionally you can have an OR condition exception name clause on the WHEN . It's not very useful as generally requires more code the 2 separate WHEN condition, but it is valid syntax. The following demonstrates various error definition methods and exception processing.

create table except_demo ( id integer, col1 varchar2(20));
insert into except_demo (id, col1)
  select 5,'OK' from dual union all
  select 6,'Too Many' from dual union all 
  select 6,'Still' from dual;

select id, count(*) from except_demo group by id;

create or replace procedure catcher(which_in integer, select_in boolean default False)
is
  e_user_1 exception; 
  e_user_2 exception; 
  invalid_which_range exception;  
  appErr_inactive_acct exception;
  sample_ora_error exception;
  pragma exception_init (sample_ora_error, -00060);

  rae exception;
  rae_num constant integer := -20100;
  pragma exception_init  (rae, -20100);  

  col1_value except_demo.col1%type; 
begin
   dbms_output.put( 'Enter catcher(' || which_in || ') Result=>');
   if which_in > 8 
      then raise invalid_which_range;
   end if ;
   if select_in 
   then 
       select col1 
         into col1_value
         from except_demo
        where id = which_in;
        dbms_output.put_line('Select Successful 1 row selected.');         
   else 
       case which_in
            when 1 then raise e_user_1;
            when 2 then raise e_user_2;
            when 3 then raise appErr_inactive_acct;
            when 4 then raise sample_ora_error;
            else raise_application_error(rae_num, 'Which_In=' || which_in || ' invalid. Please specify number 1-7 only'); 
        end case;
    end if; 
exception 
   when e_user_1         
   then dbms_output.put_line('Error e_user_1');    -- user error

   when e_user_2         
   then dbms_output.put_line('Error e_user_2');

   when no_data_found or too_many_rows        
   then dbms_output.put_line('Select except_demo where id=' || which_in ||'; Returned 0 or more than 1 row. Must return exactly 1.' );    -- oracle predefined error

   when sample_ora_error           
   then dbms_output.put_line('Ora Error:: ' || sqlerrm );            -- oracle error NOT predefined 

   when appErr_inactive_acct 
   then dbms_output.put_line('Error Account id ' || which_in || ' is inactive.');  -- user error

   when rae 
   then dbms_output.put_line(sqlerrm); 

end catcher; 

declare 
  do_select boolean; 
begin 
    for i in 1..9
    loop
       do_select := (i between 5 and 7);
       catcher(i,do_select);
    end loop;
exception 
    when others 
    then 
        dbms_output.put_line('Error returned from catcher=>' || sqlerrm);
        raise; 
end ;  


drop procedure catcher;
drop table except_demo;

In a live environment the dbms_output statement would be replaced writing the message and other information to a exception log table and NOT dbms_output.

I have a very minor disagreement with Littlefoot. I firmly believe that what ever is written in development, whether intended or not, will run in production. Too often it is the unintended that gets you into trouble. Therefore the example of a misused WHEN OTHERS is invalid even in development.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142715

Certainly, there is - if I understood the question correctly. It is called WHEN OTHERS. Though, people usually misuse it, especially when they use

exception
  when others then 
    null;
end;

as it successfully hides any errors that might appear. WHEN OTHERS is OK during development process, but might be really bad in production, especially if it doesn't contain raise.

Upvotes: 2

Related Questions