Oracle catch exception code given at runtime

Question

I want to create an Oracle PLSQL procedure that receives a statement that will be executed and a number representing a possible exception code that can arise and should be handled. I tried something but it doesn't compile 'PLS-00702: second argument to PRAGMA EXCEPTION_INIT must be a numeric literal'. Reading the docs about PRAGMA I understand why. How can I do this?

This is what I tried:

CREATE OR REPLACE PROCEDURE EXECUTE_AND_IGNORE_ERROR(dmlStatement IN VARCHAR2, oraErrorCode IN NUMBER) AS
    possibleException EXCEPTION;
    PRAGMA EXCEPTION_INIT (possibleException, oraErrorCode);
BEGIN
    EXECUTE IMMEDIATE dmlStatement;
    EXCEPTION
       WHEN possibleException
       THEN
            DBMS_OUTPUT.PUT_LINE('Warning: Ignoring error [' || oraErrorCode ||'] after executing [' || dmlStatement || ']');
            NULL;
    END;
END EXECUTE_AND_IGNORE_ERROR;

Answer 1

Oracle requires literal parameters to the compiler directive PRAGMA because it's a compiler directive interpreted at compile time, not a function executed at runtime. Therefore you can't use it programmatically.

However, you shouldn't be trying to do this. Instead trap errors generically with WHEN OTHERS THEN... and consult either SQLCODE/SQLERRM or dbms_utility.format_error_stack and dbms_utility.format_error_backtrace for all the info you need.

Reserve PRAGMA EXCEPTION_INIT only for cases when there is a specific error you anticipate that you want to handle with its own exception handler block in a special manner, and that Oracle hasn't already assigned a common exception name for (e.g. DUP_VAL_ON_INDEX, TOO_MANY_ROWS, NO_DATA_FOUND). Those cover most errors, and OTHERS handles everything else. You won't need PRAGMA EXCEPTION_INIT very often.