Is it possible to LOG ERRORS INTO - a collection or a temporary table- for erred records from a DML operation?

Question

I have a requirement to insert records (or any other DML) into a table - but in case of encountering error records, the inserts must continue and the errors must be logged.

Logically I thought of using the LOG ERRORS INTO a table.

I have a few questions pertaining to this -

1. Can a collection or a temporary table be used instead of a error log table (which is created using DBMS_ERRLOG.create_error_log or a manual table)

2. Is LOG ERRORS INTO the only way to "continue insert in case of erred records and return log info for erred records" scenario?

3.Will LOG ERRORS INTO still work if the RETURNING INTO clause is used in the DML statement?

Thank you!

Answer 1

1 - A collection can't be used and loading error into a temporary table has no advantage over using the actual table. You can use an error table with the name of your choice by passing an option of err_log_table_name DBMS_ERRLOG.CREATE_ERROR_LOG. So, your application can directly read from the table instead of using a collection.

2 - No it's not the only way in PL/SQL. You can use a BEGIN.. EXCEPTION END to skip the error using an autonomous procedure.

LOOP
  BEGIN
  INSERT INTO .. --your insert statement that may cause error
  EXCEPTION
   autonomous_procedure_to_log_errors(error_params); 
                            --pass appropriate error messages 
                            --and table names
  END;
END LOOP

3 - RETURNING INTO can be used with LOG ERRORS INTO

A point I would like to add is that If you wish to use a Bulk DML using BULK COLLECT and FORALL There is an option to SAVE EXCEPTIONS into a collection and then read from the inbuilt collection SQL%BULK_EXCEPTION. Check this post to know more.