Is there anyway in Oracle to get the error code associated with a user-defined exception type without that exception being actually raised? I don't want to hard-code the code in the entire pl/sql package, and it should be persistent.


create package body some_package is

  item_not_found exception;
  pragma exception_init(item_not_found , -20010);

  function do_something is


    -- a select statement --

    exception when no_date_found then
    raise_application_error( [ here I need to pass the error code associated with item_not_found ], 'The item you requested was not found');

  end ;

end some_package;

In this example, sqlerr is not what I need.

1 Answer 1


I don't know whether this is possible. In my application I define all errors in an generic PL/SQL package like this:


   E_ItemNotFound CONSTANT INTEGER := -20010;
   ItemNotFound EXCEPTION;
   PRAGMA EXCEPTION_INIT(ItemNotFound, -20010); 

END Errors;

Then in exception handlers I use either the EXCEPTION or the constant error number, depending on my needs. The error messages are define in a table.

create table t_error_messages as 
select -20010 ERR_CODE, 'Item not found' as ERR_MESSAGE
from dual;
  • Thanks, but pragma exception_init does not accept a constant as a second argument. It must be a literal.
    – Nina
    Commented May 19, 2022 at 8:09
  • @Nina you are right, I corrected it. Commented May 19, 2022 at 8:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.