How to catch all the errors happening during the execution of an SQL file containing multiple INSERT INTO statements to insert multiple rows?

Question

I've got a sql file TW_popul.sql containing multiple INSERT statement:

INSERT INTO conversion_fee (FEE_ID, FEE_AMOUNT, DISCOUNT_AMOUNT, FEE_TYPE, PAYMENT_ORDER_ID) 
VALUES (32353357, 8.67, 0, 'com.transferwise.fx.payment.fee.RequestFee', 29639547);

INSERT INTO conversion_fee (FEE_ID, FEE_AMOUNT, DISCOUNT_AMOUNT, FEE_TYPE, PAYMENT_ORDER_ID) 
VALUES (32358776, 1.84, 0, 'com.transferwise.fx.payment.fee.RequestFee', 29643922);

INSERT INTO conversion_fee (FEE_ID, FEE_AMOUNT, DISCOUNT_AMOUNT, FEE_TYPE, PAYMENT_ORDER_ID) 
VALUES (32374786, 11.26, 0, 'com.transferwise.fx.payment.fee.RequestFee', 29656743);

INSERT INTO conversion_fee (FEE_ID, FEE_AMOUNT, DISCOUNT_AMOUNT, FEE_TYPE, PAYMENT_ORDER_ID) 
VALUES (32374911, 1.49, 0, 'com.transferwise.fx.payment.fee.RequestFee', 29656849);

Some of those insert statements a breaking constraint rules and throwing errors(ex. "ERROR at line 1: ORA-00001: unique constraint (TW.BT_BANK_TRANSACTION_ID_PK) violated" ).

1 row created.


1 row created.


1 row created.

INSERT INTO conversion_fee (FEE_ID, FEE_AMOUNT, DISCOUNT_AMOUNT, FEE_TYPE, PAYMENT_ORDER_ID)
*
ERROR at line 1:
ORA-00001: unique constraint (TW.CF_FEE_ID_PK) violated



1 row created.


1 row created.

How do I collect all the rows generating an error (in an Error_table for example)?

I've read about TRY... CATCH but i'm not sure it applies to catching multiple errors.

Answer 1

Inserting Data with DML Error Logging

Example:

begin
  DBMS_ERRLOG.CREATE_ERROR_LOG(dml_table_name => 'conversion_fee');
end;
/

This creates a table called err$_conversion_fee that can collect the errors for table conversion_fee if specified in the INSERT statement:

INSERT INTO conversion_fee (FEE_ID, FEE_AMOUNT, DISCOUNT_AMOUNT, FEE_TYPE, PAYMENT_ORDER_ID) 
VALUES (32353357, 8.67, 0, 'com.transferwise.fx.payment.fee.RequestFee', 29639547)
LOG ERRORS INTO err$_conversion_fee ('daily_load') REJECT LIMIT UNLIMITED;

Answer 2

Maybe a better approach would be to verify if the record already exists and would violate the constraint, and only then try to insert it. You would have to script a bit around this file.

Another possibility is to insert everything to a temp table first, and then do the insert to table conversion_fee in batch, selectively with a WHERE/IN/EXISTS clause, rather than on a row by row basis. The temp table should simply be a copy of conversion_fee, without data and the constraints.

Then it should not be too difficult to idenfity the duplicate rows, for example by doing a JOIN between conversion_fee and the temp table.

With a text editor, you can easily replace the table name in your source file to something else (the name of a temp table). Import your file to that temp table, and then compare with conversion_fee.

I can't provide full code right now, since the table structure is not known, nor is the constraint. But this is an idea.