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 ?

Answer 1

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.