How to get error code of an exception type without raising it?



  • 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.

    example:

    create package body some_package is
    

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

    function do_something is

    begin

    -- 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.



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

    CREATE OR REPLACE PACKAGE Errors IS
    

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

    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;
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2