PRAGMA EXCEPTION_INIT

You can use one of these to link an exception name with an Oracle error number. Once you have done this, you can use the exception name in the exception block which follows the declaration. You can see what I mean in the example below, which I tested in an Oracle 11.2 database. First I set up a table so I could test the procedure I was going to create:

SQL> create table real_table
  2  as select * from dba_tables
  3  /

Table created.

SQL> 

Then I created a procedure to count the rows in a table. On line 5, I created an exception called table_not_found. On line 6, I associated this with ORA-00942. On line 12, I tested for this exception and displayed an error message if appropriate: 

SQL> create or replace procedure count_rows
  2  (tab in varchar2)
  3  is
  4   row_count number;
  5   table_not_found exception;
  6   pragma exception_init(table_not_found,-942);
  7  begin
  8   execute immediate 'select count(*) from '||tab
  9   into row_count;
 10   dbms_output.put_line(tab||' has '||row_count||' rows');
 11  exception
 12   when table_not_found then
 13   dbms_output.put_line('I cannot find '||tab);
 14  end;
 15  /

Procedure created.

SQL> 

When I tested the procedure, it counted the rows in real_table and told me it could not find imaginary_table:

SQL> set serveroutput on

SQL> exec count_rows('real_table');
real_table has 3172 rows

PL/SQL procedure successfully completed.

SQL> exec count_rows('imaginary_table');
I cannot find imaginary_table

PL/SQL procedure successfully completed.

SQL>