title: Bulk INSERT or UPDATE ignoring errors in Oracle
body: <p>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.</p>
  
  <p>This works perfectly when the imported data is correct, but sometimes it may contain <strong>errors such as references to unexisting foreign keys or duplication of primary keys</strong>.</p>
  
  <p>I would like to achieve something similar as the following example (which works when the data has no errors), but <strong>instead of failing</strong> the whole operation, I would like to <strong>log the conflictive rows and continue processing</strong>.</p>
  
  <p><br/></p>
  
  <h3>Example</h3>
  
  <p>Let's suppose I have an <code>items</code> table and an <code>items_operations</code> staging table. Then, after populating <code>items_operations</code> I can...</p>
  
  <p><strong>Create new items</strong></p>
  
  <pre><code>INSERT INTO items (id, name, manufacturer_id, price)
  SELECT
    item_id,
    item_name,
    item_manufacturer_id
    item_price
  FROM items_operations
  WHERE operation = 'creation'
  </code></pre>
  
  <p>(This might fail if there is a duplicate <code>id</code> or unexisting FK for <code>manufactuer_id</code>)</p>
  
  <p><br/>
  <strong>Update existing items</strong></p>
  
  <pre><code>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')
  </code></pre>
  
  <p>(This will fail in the case of unexisting FK for <code>manufacturer_id</code>)</p>
  
  <p><br/></p>
  
  <h3>Additional remarks</h3>
  
  <ul>
  <li>Since <strong>SQL</strong> operations must be atomic, I think that what I need maybe would require use of <strong>PL/SQL</strong>, but I'm not quite sure about what approach should I follow.</li>
  <li>Also, it might be important to know that my <code>items</code> table is really huge (~300MM records), so unnecesary joins should be avoided.</li>
  <li>Finally, sometimes <code>items_operations</code> contains updates for unexisting items (there is no <code>id</code> on <code>items</code> matching <code>item_id</code>). This does not fail, but it would be ideal if I could be able to log those unmatched items, too.</li>
  </ul>