Propagating triggered exceptions through SQL%BULK_EXCEPTIONS

Question

I'm handling a PL/SQL package containing a {FORALL .. SAVE EXCEPTIONS .. UPDATE} statement.

The validity of the data (to the business rules) is checked with a trigger on the table with the data that will be updated. This trigger calls a procedure that is able to generate an error using:

RAISE_APPLICATION_ERROR( -20002, 'message');

It is thus possible to generate a user defined error during the {FORALL .. SAVE EXCEPTIONS .. UPDATE} DML statement. However, when attempting to log the error in the exception handling using the SQL%BULK_EXCEPTIONS I observe the following output;

SQLCODE:                                       ORA-24381
SQLERRM:                                       ORA-24381: error(s) in array DML
SQL%BULK_EXCEPTIONS(i).ERROR_CODE:             20002
SQLERRM(-(SQL%BULK_EXCEPTIONS(i).ERROR_CODE)): ORA-20002:

Note that the error_code from the sql%bulk_exceptions loses the minus-sign. Also the error message is empty save for the error's code.

There are 2 problems for me here:

The (user defined) error's message is not returned through the bulk_exceptions. (This is not the case for standard errors! f.e.: would the FORALL-UPDATE fail because of a column size constrain, the error message from bulk_exceptions could be: "ORA-12899: value too large for column", and will not be empty.)
When this exception is raised, the SQLCODE+SQLERRM "ORA-24381: error(s) in array DML" are passed through, instead of the actual triggered (user defined, -20000) error code and message.

Is it possible to tie the message from RAISE_APPLICATION_ERROR to the user defined error code so that SQLERRM will show it? And how can I propagate this error without it changing to the ORA-24381 error?

Justin Cave Justin Cave · Accepted Answer · 2014-03-17T19:06:39

In general, it is not possible for sqlerrm to return the user-defined error message because it is entirely possible (and, in most applications, likely) that the same error code maps to multiple error messages. If your particular application is designed to have a single definition for each user-defined error with a single message corresponding to the error code, you could maintain your own collection that mapped the error codes to error messages and call that in your error handler. Something like

CREATE OR REPLACE PACKAGE pkg_error_codes
AS
  TYPE error_code_tbl IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
  g_error_codes error_code_tbl;

  FUNCITON My_SQLERRM( p_error_code IN INTEGER )
    RETURN VARCHAR2;
END;

CREATE OR REPLACE PACKAGE BODY pkg_error_codes
AS 
  FUNCITON My_SQLERRM( p_error_code IN INTEGER )
    RETURN VARCHAR2
  IS
  BEGIN
    RETURN g_error_codes( p_error_code );
  END;

BEGIN
  -- Initialize your error codes
  g_error_codes( 20001, 'Some error message' );
  g_error_codes( 20002, 'Another error message' );
END;

You can then call My_SQLERRM in your code (or just directly use pkg_error_codes.g_error_codes( SQL%BULK_EXCEPTIONS(i).ERROR_CODE ) if you'd prefer).

You may want to create a table that maps error numbers to error messages and read that in your initialization block rather than putting the mapping in code. And you may want to name the exceptions and associate them with the error code via pragma exception_init calls that are part of the package body (though that would eliminate the ability to have the error message in the error stack in normal operations).

Depending on what you are doing, it may make more sense to use SQL rather than PL/SQL using the dbms_errlog table to create the error log table and using the EXCEPTIONS INTO clause in your SQL statement to write any errors to the error log. That would include the actual error message that was raised.

Propagating triggered exceptions through SQL%BULK_EXCEPTIONS

1 Answers