Oracle: Capturing Specific Oracle Message with EXCEPTION_INIT

Question

I want to handle an exception for ORA-000942 and am following this manual and this discussion. Since this is an ORA- error with no predefined name, I want to use exception_init.

When I run this code, I continue to get the ORA-000942 message, but not as expected via the procedure level handler.

create table foobar (foobar_id varchar(1));

declare

 procedure p_add_to_foobar
 is
 p_missing_table exception;
 pragma exception_init(p_missing_table, -00942);

 begin
 insert into foobaz   
 select '1' from dual;

  exception
  when p_missing_table then
  dbms_output.put_line('MISSING TABLE'); 
  end p_add_to_foobar;  


 begin
 p_add_to_foobar;
 dbms_output.put_line('DONE');
 end;

Question:

1. How do I get my procedure level exception to handle the -942 error?

Answer 1

The error you're getting is being thrown by the PL/SQL compiler when it tries to compile the statement

insert into foobaz
  select 1 from dual

because of course the foobaz table doesn't exist. In-line SQL statements have to be valid at compile time, and thus the compiler throws an exception.

To get around this you'll have to use dynamic SQL, as in:

declare
 procedure p_add_to_foobar is
   p_missing_table exception;
   pragma exception_init(p_missing_table, -00942);
 begin
   EXECUTE IMMEDIATE
     'insert into foobaz   
       select ''1'' from dual';
  exception
    when p_missing_table then
      dbms_output.put_line('MISSING TABLE'); 
  end p_add_to_foobar;
BEGIN
  p_add_to_foobar;
  dbms_output.put_line('DONE');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('WHEN OTHERS: ' || SQLERRM);
END;

The above produces the expected output:

MISSING TABLE
DONE

Best of luck.