How to merge rows + retrieve new and existing keys

Question

In an Oracle table (e.g. MYTABLE, with a numeric sequenced field as primary key), I have to insert several thousand of rows, but some of them are supposed to already exist in the table.

Naturally, I should try to use MERGE but I need, as well, to retrieve all created (when inserting) and existing (when updating) primary keys.

As well, it should be as fast as possible.

Is the following attempt (pseudo code) the only way to go? Thanks.

keys_list = empty array
for each row to merge
    do query 'SELECT PK_MYTABLE FROM MYTABLE WHERE PK_MYTABLE = '+row.pk_mytable
        ==> retrieve key
    if found then:
        add key to keys_list
    else:
        do query 'INSERT INTO MYTABLE (PK_MYTABLE, ...) VALUES (SEQ_MYTABLE.NEXTVAL, ...)'
        do query 'SELECT SEQ_MYTABLE.CURRVAL FROM DUAL' ==> retrieve key
        add key to keys_list

Answer 1

Add a MODIFICATION_DATE column to the table

Grab and save the sysdate.

When you merge update/insert the value of the sysdate as well.

When the merge is complete, select the rows where the MODIFICATION_DATE = SYSDATE and you have the set you are interested in.

Answer 2

Why can't you use a MERGE statement for this? This is exactly what a MERGE is for. Here is a rough idea of how it would look...

merge into mytable mt
using 
(
    select key_field, value_field from sourcetable
) st
on 
( mt.key_field = st.key_field )
when matched then update
    set mt.value_field = st.value_field
when not matched then insert
    ( key_field, value_field )
    values 
    ( st.key_field, st.value_field )
;

Using a MERGE statement is fast because it is a single statement and the Oracle optimizer can utilize indexes and choose a better explain path than iterating through a cursor using PL/SQL.

Answer 3

If the keys are being generated from a sequence, then the normal way to get the key generated by that insert is to use the returning clause:

declare
  v_insert_seq integer;
begin
  insert into t1 (pk, c1)
  values (myseq.nextval, 'value') returning pk into v_insert_seq;
end;
/

However, as best as I can tell, the merge statement doesn't support that returning feature.

Depending on the source of your new rows, there are different ways you could do this. If you are inserting one row at a time, then the approach above will work pretty well.

To detect the duplicate records, just catch the exceptions when you are inserting (when dup_val_on_index) and then handle them with updates.

If your source of rows is another table, you probably want to look at bulk inserts, and allowing Oracle to return you an array of new PK values. I tried this, but couldn't get it working, so perhaps it's not supported (or I'm missing something today - it gives a syntax error):

declare
  type t_type is table of t1.pk%type;
  v_insert_seqs t_type;
begin

   insert into t1 (pk, c1)
   select level newpk, 'value' c1value
   from dual
   connect by level <= 10 returning pk bulk collect into v_insert_seqs;

exception 
  when dup_val_on_index then
    raise;
end;
/

The next best thing is to select the rows into arrays and then use bulk binds with the returning clause to capture the new PK IDs and also use Save Exceptions to catch all the rows that failed to inserted. Then you can process any of the failed inserted afterwards:

set serveroutput on
declare
  type t_pk is table of t1.pk%type;
  type t_c1 is table of t1.c1%type;
  v_pks t_pk;
  v_c1s  t_c1;
  v_new_pks t_pk;

  ex_dml_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);

begin
  -- get the batch of rows you want to insert
  select level newpk, 'value' c1
  bulk collect into v_pks, v_c1s
  from dual connect by level <= 10;

  -- bulk bind insert, saving exceptions and capturing the newly inserted
  -- records
  forall i in v_pks.first .. v_pks.last save exceptions 
    insert into t1 (pk, c1)
    values (v_pks(i), v_c1s(i)) returning pk bulk collect into v_new_pks;

exception
 -- Process the exceptions 
  when ex_dml_errors then
    for i in 1..SQL%BULK_EXCEPTIONS.count loop
      DBMS_OUTPUT.put_line('Error: ' || i || 
          ' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
          ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
    end loop;
end;
/

Answer 4

If you are running Oracle 10 or better, you might be able to do much the same thing, for nearly free by issuing a commit before the merge to update the SCN, then after the merge, use the ORA_ROWSCN to detect which rows have changed.