Pattern to substitute for MERGE INTO Oracle syntax when not allowed

Question

I have an application that uses the Oracle MERGE INTO... DML statement to update table A to correspond with some of the changes in another table B (table A is a summary of selected parts of table B along with some other info). In a typical merge operation, 5-6 rows (out of 10's of thousands) might be inserted in table B and 2-3 rows updated.

It turns out that the application is to be deployed in an environment that has a security policy on the target tables. The MERGE INTO... statement can't be used with these tables (ORA-28132: Merge into syntax does not support security policies)

So we have to change the MERGE INTO... logic to use regular inserts and updates instead. Is this a problem anyone else has run into? Is there a best-practice pattern for converting the WHEN MATCHED/WHEN NOT MATCHED logic in the merge statement into INSERT and UPDATE statements? The merge is within a stored procedure, so it's fine for the solution to use PL/SQL in addition to the DML if that is required.

Answer 1

Another way to do this (other than Merge) would be using two sql statements one for insert and one for update. The "WHEN MATCHED" and "WHEN NOT MATCHED" can be handled using joins or "in" Clause.

If you decide to take the below approach, it is better to run the update first (sine it only runs for the matching records) and then insert the non-Matching records. The Data sets would be the same either way, it just updates less number of records with the order below.

Also, Similar to the Merge, this update statement updates the Name Column even if the names in Source and Target match. If you dont want that, add that condition to the where as well.

create table src_table(
   id number primary key,
   name varchar2(20) not null
);

create table tgt_table(
   id number primary key,
   name varchar2(20) not null
);

insert into src_table values (1, 'abc');
insert into src_table values (2, 'def');
insert into src_table values (3, 'ghi');

insert into tgt_table values (1, 'abc');
insert into tgt_table values (2,'xyz');

SQL> select * from Src_Table;

        ID NAME
---------- --------------------
         1 abc
         2 def
         3 ghi

SQL> select * from Tgt_Table;

        ID NAME
---------- --------------------
         2 xyz
         1 abc

Update tgt_Table tgt
   set Tgt.Name = 
      (select Src.Name
          from Src_Table Src
          where Src.id = Tgt.id
      );

2 rows updated. --Notice that ID 1 is updated even though value did not change

select * from Tgt_Table;

   ID NAME
----- --------------------
    2 def
    1 abc

insert into tgt_Table
select src.*
  from Src_Table src,
       tgt_Table tgt
  where src.id = tgt.id(+)
    and tgt.id is null;

1 row created.

SQL> select * from tgt_Table;

        ID NAME
---------- --------------------
         2 def
         1 abc
         3 ghi

commit;

There could be better ways to do this, but this seems simple and SQL-oriented. If the Data set is Large, then a PL/SQL solution won't be as performant.