1

I have an Oracle procedure in which I would like to do the following:

  1. Copy records from one table to another
  2. Update original records to indicate they have been copied

Currently, I am using a FOR loop to process each record individually. This works, but it is a lot of code to do something I think could be simpler.

I would like to:

  1. Eliminate the for loop and copy the records with an INSERT INTO ... SELECT ... statement
  2. If copy is successful, update all selected records.

This may seem simple, just one INSERT and an UPDATE statement with the same WHERE clause. However, in the time between execution of the two statements, more records may be inserted that need to be copied. If I use the same WHERE clause, I may mark records as processed that have not actually been processed.

Is there a way I can save a list of the Primary Keys for use in both statements or merge the statements? Or would you recommend I stick with the FOR loop and process the records one at a time? All constructive input welcome.

Kent Anderson
  • 486
  • 2
  • 16

2 Answers2

3

If you run the two statements within a transaction, any failures will cause both the insert and the update to be rolled back.

insert into tgt..
select * from src
where <condition>
  and row_status <> 'copied'; --ignoring records after they have been copied once?

update src
set row_status = 'copied'
where <same_where_condition_as_before>

commit;

If there are new rows inserted in the source table after they have been read, you might need to run the block again, with a changed where condition if appropriate.

Rajesh Chamarthi
  • 18,568
  • 4
  • 40
  • 67
  • Jon - With Oracle's read consistency model, the serializable shouldn't be necessary for the OP's question. I would have gone with "select for update" instead, if I thought the OP needed to serialize the current transaction. What do you think? – Rajesh Chamarthi Dec 23 '14 at 17:01
0

Rajesh's answer should work well if there is a small chance of the source table being updated concurrently. If the source table is updated frequently the serializable transaction will fail too often.

Another approach would be to use flashback query to get a point-in-time look at the table. The as of syntax is a convenient way to see what a table looked like in the (recent) past.

declare
    v_current_scn number;
begin
    v_current_scn := dbms_flashback.get_system_change_number;

    insert into tgt..
    select * from src
    where <condition>
        and row_status <> 'copied'; --ignoring records after they have been copied once?

    update src
    set row_status = 'copied'
    where rowid in
        (
            select rowid
            from src as of scn v_current_scn
            where <same_where_condition_as_before>
        );
end;
/
Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • Interesting. Would you recommend this approach over using "set transaction read only" ? – Kent Anderson Dec 22 '14 at 19:43
  • It depends on how often the source table gets updated. If it's infrequent, then take Rajesh's answer, put it in a loop, and re-run it if it fails with `ORA-08177: Cannot serialize access for this transaction `. If it's frequently updated, then I'd use flashback. Another approach would be to use `returning into` syntax, but that would require some more code to hold the IDs. – Jon Heller Dec 22 '14 at 19:44