Create a generic PL SQL procedure to log Bulk Collect errors dynamically

Question

I recently learned about the use of BULK COLLECT in SQL. I found a way to handle exceptions generated by DML statements :

SET SERVEROUTPUT ON SIZE 99999;
--
DECLARE
--
bulk_errors       exception;
PRAGMA exception_init(bulk_errors, -24381);
--
--
CURSOR cursEmployee IS
SELECT EMPLOYEE_ID, EMPLOYEE_NAME
FROM EMPLOYEE;

TYPE employee_table IS TABLE OF cursEmployee%rowtype;
employee_rec            employee_table;
--
BEGIN
--  
        OPEN cursEmployee;
        FETCH cursEmployee BULK COLLECT INTO employee_rec LIMIT 10000;
        --
        WHILE employee_rec.COUNT != 0 LOOP
            --
            FORALL indx IN INDICES OF employee_rec save exceptions
                --
                
                INSERT INTO EMPLOYEE (
                    EMPLOYEE_ID,
                    EMPLOYEE_NAME
                    )
                VALUES (
                    employee_rec.EMPLOYEE_ID,
                    employee_rec.EMPLOYEE_NAME
                    );
            --
            COMMIT;
            --
            FETCH cursEmployee BULK COLLECT INTO employee_rec LIMIT 10000;
            --
        
        END LOOP;
           exception when bulk_errors then
             
       
        for i in 1 .. sql%bulk_exceptions.COUNT loop
            dbms_output.put_line('Employee Id : ' || sql%bulk_exceptions(i).EMPLOYEE_ID);
            dbms_output.put_line('Employee Id : ' || sql%bulk_exceptions(i).EMPLOYEE_NAME);
            dbms_output.put_line('Error Message: '||sqlerrm(-sql%bulk_exceptions(i).error_code));
       
        end loop;
           
    CLOSE  cursEmployee;
       
END;
/

I then created a generic procedure to log the exceptions :

CREATE OR REPLACE PROCEDURE LOG_BULK_EXCEPTIONS IS
    BEGIN
        IF SQL%BULK_EXCEPTIONS.COUNT > 0 THEN
            DBMS_OUTPUT.PUT_LINE(' Errors occured during a BULK COLLECT statement : ');
            DBMS_OUTPUT.PUT_LINE(' Number of exceptions : ' || SQL%BULK_EXCEPTIONS.COUNT );
            --
            FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP    
                DBMS_OUTPUT.PUT_LINE(' Error : ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
                DBMS_OUTPUT.PUT_LINE('Backtrace : ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);    
            END LOOP;
        --
        END IF;
END;
/

I find this way of logging exceptions a bit limited : we just get the error (numeric value, cannot insert null into, etc...). I'm looking for a way to add information about the data / specific elements in the cursor that raised the error.

To do this, I need to pass a column name as parameter to my procedure, and concatenate it to obtain this sort of statement :

  dbms_output.put_line(' Internal Id : ' || sql%bulk_exceptions(i).MY_COLUMN_PARAMETER);

This way, I could use this logging procedure everywhere in the Database, which would be great.

Does anyone know how to concatenate a string parameter to this " sql%bulk_exceptions(i). " and execute it correctly ?

If I understood correctly, you can raise_application_error and dynamically set its values, like the example here : https://stackoverflow.com/questions/6020450/oracle-pl-sql-raise-user-defined-exception-with-custom-sqlerrm — planben, Aug 22 '20 at 10:57
It's an interesting and useful approach, but the SAVE EXCEPTIONS keywords do cache the exceptions : they are not raised. So we could loop through the exception collection, concatenate some information (ex : employee_id, employee_name) to the exception inside the collection, and then call the procedure that logs the exceptions. In this case we have to write more code, which is what I'd like to avoid since it won't be generic... — Tyss, Aug 22 '20 at 13:27
Have you looked at DML error logging instead? https://oracle-base.com/articles/10g/dml-error-logging-10gr2 That's going to be more efficient than dealing with bulk exceptions and will automatically log all the information into the error log table (but you'd end up with an `ERR_$` table for every table you're doing bulk loads into). — Justin Cave, Aug 22 '20 at 13:57
Great point, Justin. But do remember that they are very different in terms of behavior.SAVE EXCEPTIONS handles statement-level errors, LOG ERRORS suppresses errors at the row level. So you just have to make sure the latter is what you want. — Steven Feuerstein, Aug 22 '20 at 19:17
Paul there is no way I know of to "add to" the information cached in the bulk_exceptions pseudo-collection. We only store the error code, not the error message, so you do in effect lose some information (for example, the specific column on which the error occurred). That information was not stored because of the impact on performance (that is, the extra cycles needed to lookup the constraint information at runtime). I don't see how you can recover it at this point. — Steven Feuerstein, Aug 22 '20 at 19:19

score 1 · Answer 1 · answered Aug 22 '20 at 20:04

Yes, you can get what you are looking for provided what you want to add in in the original collection. The sql%bulk_exceptions collection has another column, ERROR_INDEX. It contains the index of the row in the original collection. This allows you to reference values from the that collection via

  employee_rec(sql%bulk_exceptions(i).error_index).id;
  employee_rec(sql%bulk_exceptions(i).error_index).name;

Your procedure has another issue. Your exception block is outside the you loop processing the bulk collection. As a consequence your bulk buffers will be processed only until the first buffer contains an error; no subsequent buffers will be processed. You can avoid this by creating a block inside the processing loop and handling exceptions within the inner block. Also, nice to see you went to the effort to actually close the cursor. However, it is in the exception block so it only executes if there is a exception. See here for example of each. Since I did not want to create over 10000 rows for demo I reduced the Bulk Limit to 3.

You're right. The syntax I used before didn't allow for a nested exception block, so I'll take yours. However, my question was : how can I build / concat a string like this : [employee_rec(sql%bulk_exceptions(i).error_index).id] and dynamically add a cursor name or a column name ? I tried a few things with escaping characters and use execute immediate but...I'm not sure it is feasible.My aim is to have a generic procedure where I could pass "employee_rec" and "Id" or "Name" as parameter and construct the line dynamically. I could then call the procedure inside the exception block. — Tyss, Aug 23 '20 at 11:09

Create a generic PL SQL procedure to log Bulk Collect errors dynamically

1 Answers1