Sam Sam - 2 months ago 6
SQL Question

Bulk INSERT or UPDATE ignoring errors in Oracle

For bulk loading imported data I make some processing and store the result of that processing in a staging table. Then, I can apply changes to other tables using the staged data.

This works perfectly when the imported data is correct, but sometimes it may contain errors such as references to unexisting foreign keys or duplication of primary keys.

I would like to achieve something similar as the following example (which works when the data has no errors), but instead of failing the whole operation, I would like to log the conflictive rows and continue processing.




Example



Let's suppose I have an
items
table and an
items_operations
staging table. Then, after populating
items_operations
I can...

Create new items

INSERT INTO items (id, name, manufacturer_id, price)
SELECT
item_id,
item_name,
item_manufacturer_id
item_price
FROM items_operations
WHERE operation = 'creation'


(This might fail if there is a duplicate
id
or unexisting FK for
manufactuer_id
)



Update existing items

UPDATE items t1
SET (name, manufacturer_id, price) =
(SELECT
t2.item_name,
t2.item_manufacturer_id,
t2.item_price
FROM items_operations t2
WHERE t1.id = t2.item_id
AND t2.operation = 'modification')
WHERE EXISTS (
SELECT 1
FROM items_operations t2
WHERE t1.id = t2.item_id
AND t2.operation = 'modification')


(This will fail in the case of unexisting FK for
manufacturer_id
)




Additional remarks




  • Since SQL operations must be atomic, I think that what I need maybe would require use of PL/SQL, but I'm not quite sure about what approach should I follow.

  • Also, it might be important to know that my
    items
    table is really huge (~300MM records), so unnecesary joins should be avoided.

  • Finally, sometimes
    items_operations
    contains updates for unexisting items (there is no
    id
    on
    items
    matching
    item_id
    ). This does not fail, but it would be ideal if I could be able to log those unmatched items, too.


Answer

It sounds like you want to use DML error logging

Create the error table (once per table that you are loading)

begin
  dbms_errlog.create_error_log( dml_table_name => 'ITEMS' );
end;
/

Then you can use the LOG ERRORS INTO clause

INSERT INTO items (id, name, manufacturer_id, price)
   SELECT
      item_id,
      item_name,
      item_manufacturer_id
      item_price
 FROM items_operations
WHERE operation = 'creation'
  LOG ERRORS INTO err$_items
    REJECT LIMIT UNLIMITED;

Then you can query err$_items to see the errors that were thrown.

For things that are not failures (i.e. items in item_operations that do not have a matching row in items), you'd need to run a query to find those. Going forward, I would define a foreign key constraint that prevents you from recording operations on non-existent items.

Comments