Standard exception pragmas

Oracle provides several standard exception pragmas (essentially, a compiler directive) for exceptions found on a fairly frequent basis. These are documented in the PL/SQL User Guide and Reference, but you can also get them at runtime using what is below:

SQL> select text from dba_source where name = 'STANDARD' and lower(text) like '%pragma%exception%';

TEXT
--------------------------------------------------------------------------------
    pragma EXCEPTION_INIT(CURSOR_ALREADY_OPEN, '-6511');
    pragma EXCEPTION_INIT(DUP_VAL_ON_INDEX, '-0001');
    pragma EXCEPTION_INIT(TIMEOUT_ON_RESOURCE, '-0051');
    pragma EXCEPTION_INIT(INVALID_CURSOR, '-1001');
    pragma EXCEPTION_INIT(NOT_LOGGED_ON, '-1012');
    pragma EXCEPTION_INIT(LOGIN_DENIED, '-1017');
    pragma EXCEPTION_INIT(NO_DATA_FOUND, 100);
    pragma EXCEPTION_INIT(ZERO_DIVIDE, '-1476');
    pragma EXCEPTION_INIT(INVALID_NUMBER, '-1722');
    pragma EXCEPTION_INIT(TOO_MANY_ROWS, '-1422');
    pragma EXCEPTION_INIT(STORAGE_ERROR, '-6500');
    pragma EXCEPTION_INIT(PROGRAM_ERROR, '-6501');
    pragma EXCEPTION_INIT(VALUE_ERROR, '-6502');
    pragma EXCEPTION_INIT(ACCESS_INTO_NULL, '-6530');
    pragma EXCEPTION_INIT(COLLECTION_IS_NULL , '-6531');
    pragma EXCEPTION_INIT(SUBSCRIPT_OUTSIDE_LIMIT,'-6532');
    pragma EXCEPTION_INIT(SUBSCRIPT_BEYOND_COUNT ,'-6533');
  pragma EXCEPTION_INIT(ROWTYPE_MISMATCH, '-6504');
  PRAGMA EXCEPTION_INIT(SYS_INVALID_ROWID, '-1410');
    pragma EXCEPTION_INIT(SELF_IS_NULL, '-30625');
    pragma EXCEPTION_INIT(CASE_NOT_FOUND, '-6592');
    pragma EXCEPTION_INIT(USERENV_COMMITSCN_ERROR, '-1725');
    pragma EXCEPTION_INIT(NO_DATA_NEEDED, '-6548');
pragma EXCEPTION_INIT(INVALID_USERENV_PARAMETER, -2003);
pragma EXCEPTION_INIT(ICD_UNABLE_TO_COMPUTE, -6594);

25 rows selected.

SQL>

This makes it easy to use in your PL/SQL programs, as they are available to all programs since they are in the STANDARD package.

For example, you can do something like:

SQL> declare
  2    l number;
  3  begin
  4    l := 'a';
  5  exception
  6    when value_error then
  7      dbms_output.put_line('Please enter a number rather than a letter');
  8  end;
  9  /
Please enter a number rather than a letter

PL/SQL procedure successfully completed.
SQL>

We can also create exceptions for the standard exceptions with a predefined pragma in the standard package. For example, we can either:

SQL> declare
  2    customer_id number;
  3  begin
  4    select c into customer_id from t0909 where c = 'a';
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 4

…or, in addition to the standard exception pragma, we can provide a more descriptive exception to the calling program, such as java by adding a pragma to a custom package…

SQL> create or replace package my_exceptions is
  2    invalid_customer_id exception;
  3    pragma exception_init(invalid_customer_id,-1722);
  4  end;
  5  /

Package created.

…and use our custom exception definition from our program by doing something like what is below:

SQL> declare
  2    customer_id number;
  3  begin
  4    select c into customer_id from t0909 where c = 'a';
  5  exception
  6    when my_exceptions.invalid_customer_id then
  7      dbms_output.put_line('Please ensure you search using a valid numeric customer_id.');
  8  end;
  9  /
Please ensure you search using a valid numeric customer_id.

PL/SQL procedure successfully completed.

SQL>