Geek asks and answers

Pattern to replace Oracle MERGE INTO syntax when not allowed

I have an application that uses the Oracle MERGE INTO ... DML statement to update table A to match some changes in another table B (table A is a summary of the individual parts of table B as well as some other information). In a typical merge operation, 5-6 rows (out of 10 thousand) can be inserted into table B and 2-3 rows updated.

It turns out that the application should be deployed in an environment with a security policy in the target tables. The MERGE INTO ... statement cannot be used with these tables (ORA-28132: syntax concatenation does not support security policies)

So, we have to change the logic of MERGE INTO ... to use regular inserts and updates instead. Is this a problem that any other person has encountered? Is there a best practice scheme for converting WHEN MATCHED / WHEN NOT MATCHED logic into a merge operator in the INSERT and UPDATE statements? The merge is in the stored procedure, so for the decision to use PL / SQL in addition to DML, if necessary, is suitable.

+3
source share
2 answers

Another way to do this (other than Merge) would be to use two sql statements to insert and one to update. "WHEN MATCHES" and "WHEN DO NOT MATCH" can be processed using joins or "in the section".

, (sine ), , . , .

, , , Source Target . , .

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;

, SQL. , PL/SQL .

+1

, , , , , .

. , , , , , (.. , SQL),

begin
  for row in (select ... from source_table) loop
    update table_to_be_merged
    if sql%rowcount = 0 then -- no row matched, so need to insert
      insert ...
    end if;
  end loop;
end;

, , , , , ( , , , ).

, , , . , PLSQL, SQL .

0

More articles:


All Articles