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.

Answer 2

This seems like a bad idea and opens your database up to all sorts of SQL injection attacks. You should NOT use this on a production system or anything that takes unsanitised user input.

However, from a purely academic point-of-view, you can move the error handling into a statement that you evaluate using EXECUTE IMMEDIATE:

CREATE PROCEDURE EXECUTE_AND_IGNORE_ERROR(
  dmlStatement IN VARCHAR2,
  oraErrorCode IN NUMBER
) AS
BEGIN
  EXECUTE IMMEDIATE q'{DECLARE
    possibleException EXCEPTION;
    PRAGMA EXCEPTION_INIT (possibleException, }' || oraErrorCode || q'{);
  BEGIN
    EXECUTE IMMEDIATE :1;
  EXCEPTION
     WHEN possibleException THEN
       DBMS_OUTPUT.PUT_LINE(
         'Warning: Ignoring error [' || :2 || '] after executing [' || :3 || ']'
       );
       NULL;
  END;}'
  USING dmlStatement, oraErrorCode, dmlStatement;
END EXECUTE_AND_IGNORE_ERROR;
/

Then:

BEGIN
  DBMS_OUTPUT.ENABLE();
  EXECUTE_AND_IGNORE_ERROR('INSERT INTO does_not_exist (id) VALUES (1)', -942);
END;
/

Outputs:

Warning: Ignoring error [-942] after executing [INSERT INTO does_not_exist (id) VALUES (1)]

fiddle