plsql - Oracle: Capturing Specific Oracle Message with EXCEPTION_INIT -

i want handle exception ora-000942 , following manual , discussion. since ora- error no predefined name, want use exception_init.

when run code, continue ora-000942 message, not expected via procedure level handler.

create table foobar (foobar_id varchar(1));  declare   procedure p_add_to_foobar   p_missing_table exception;  pragma exception_init(p_missing_table, -00942);   begin  insert foobaz     select '1' dual;    exception   when p_missing_table   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 procedure level exception handle -942 error?

the error you're getting being thrown pl/sql compiler when tries compile statement

insert foobaz   select 1 dual 

because of course foobaz table doesn't exist. in-line sql statements have valid @ compile time, , compiler throws exception.

to around you'll have use dynamic sql, in:

declare  procedure p_add_to_foobar    p_missing_table exception;    pragma exception_init(p_missing_table, -00942);  begin    execute immediate      'insert foobaz           select ''1'' dual';   exception     when p_missing_table       dbms_output.put_line('missing table');    end p_add_to_foobar; begin   p_add_to_foobar;   dbms_output.put_line('done'); exception   when others     dbms_output.put_line('when others: ' || sqlerrm); end; 

the above produces expected output:

missing table done 

best of luck.