oracle handle user defined exception in a loop

Question

I have a lookup table that contains the owner(schema) and the table_names. So I want to loop through this lookup table to insert the data into a new table from this owner.table. But I want to handle the exception in the loop just in case the owner/table doesn't exist.

But i'm getting this error message when compile the following code. Error(49,21): PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following: ( begin case declare end exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge json_exists json_value json_query json_object json_array

declare
table_does_not_exist exception;  
PRAGMA EXCEPTION_INIT(table_does_not_exist, -942);

cursor lookup_table is
select owner,table_name
from lookup;

begin
for rec in lookup_table loop
  execute immediate 'insert into new_table select * from '||rec.owner||'.'||rec.table_name;

  exception
  when table_does_not_exist then
      dbms_output.put_line('table dose not exist, continue the interation');
      continue;
end loop;

Belayer Belayer · Accepted Answer · 2020-04-28T16:18:38

Oracle's block structure permits block nesting. So inside your loop you can nest a block which handles your exception.

declare
table_does_not_exist exception;  
PRAGMA EXCEPTION_INIT(table_does_not_exist, -942);

cursor lookup_table is
select owner,table_name
from lookup;

begin
for rec in lookup_table loop 
    begin 
       execute immediate 'insert into new_table select * from '||rec.owner||'.'||rec.table_name;

    exception
        when table_does_not_exist then
          dbms_output.put_line('table dose not exist, continue the interation');
    end ; 
end loop;  
end ;

oracle handle user defined exception in a loop

1 Answers