Tuesday, December 4, 2012

Merge Operation In Oracle - ORA-30926

I had a situation to update or insert a table on the basis of source table. The Source and the destination tables were same except that the source table had some extra columns to indicate the operation type Insert update or delete to perform when inserting or updating the data.

I thought of using MERGE which is one that fastest way of doing it. After spending a few hours in came to now that i cannot use MERGE. As my source table can have one or more entries for pk_row. Which merge cannot handle and give  ORA-30926: unable to get a stable set of rows in the source tables

Actually i thought merge statement is some thing like

Select source table....
update destination table....

if sql%rowcount 0 then
    insert ....
end if;

But actually it is

for x in ( select * from source )
loop
    if record exists
    then
        if record hasn't been modified by us yet
        then
              update it
        else
              fail
        end if
    else
        insert it
    end if
end loop