How do I know what action was taken by the MERGE statement (ORACLE)?

Question

Is there a way to find out what action was taken by the MERGE statement in an ORACLE procedure?

For example, if I need to subsequently perform different procedures depending on the action performed (INSERT or UPDATE)

p.s. Forgot to clarify, I am considering a case where the MERGE statement processes exactly one row

MERGE works on multiple records so for a few of them it has an UPDATE action and for a few of them, it is INSERT. What you are expecting then? It is better to find it using altogether different queries. — Popeye
Is a MERGE the correct choice if you need to take different actions depending on INSERT or UPDATE ? Why not split your code up in a block for INSERT and a block for UPDATE — Koen Lostrie
You could use triggers to maintain counts of the number of separate operations. Then, just look at the counts before and after the merge -- assuming nothing else is going on. — Gordon Linoff
@GordonLinoff The use of triggers is prohibited in our company. It seems like I need to use separately INSERT and UPDATE in my case. I was just curious if there is a way — Zurab-D
There's this from oracle-developer.net, but it needs a PL/SQL package. — William Robertson

Connor McDonald Connor McDonald · Accepted Answer · 2020-12-09T08:26:55

Without modifying the table, its hard to capture what has been done. There are solutions out there that add a PL/SQL layer into the MERGE statement to force the execution of PL/SQL function for each row, but that will hurt performance.

If you really need it, an additional column could be added, eg

SQL> create table t as select empno, ename, sal, ' '  tag from scott.emp where empno != 7934;

Table created.

SQL> create table t1 as select empno, ename, sal*5 sal from scott.emp where job = 'CLERK';

Table created.

SQL>
SQL> select * from t;

     EMPNO ENAME             SAL T
---------- ---------- ---------- -
      7369 SMITH             800
      7499 ALLEN            1600
      7521 WARD             1250
      7566 JONES            2975
      7654 MARTIN           1250
      7698 BLAKE            2850
      7782 CLARK            2450
      7788 SCOTT            3000
      7839 KING             5000
      7844 TURNER           1500
      7876 ADAMS            1100
      7900 JAMES             950
      7902 FORD             3000

13 rows selected.

SQL> select * from t1;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH            4000
      7876 ADAMS            5500
      7900 JAMES            4750
      7934 MILLER           6500

SQL>
SQL> merge into t
  2  using ( select * from t1) t1
  3  on ( t.empno = t1.empno )
  4  when matched then
  5    update
  6    set t.sal = t1.sal, t.tag = 'U'
  7  when not matched then
  8    insert (t.empno,t.ename,t.sal,t.tag)
  9    values (t1.empno,t1.ename,t1.sal,'I');

4 rows merged.

SQL>
SQL> select * from t;

     EMPNO ENAME             SAL T
---------- ---------- ---------- -
      7369 SMITH            4000 U
      7499 ALLEN            1600
      7521 WARD             1250
      7566 JONES            2975
      7654 MARTIN           1250
      7698 BLAKE            2850
      7782 CLARK            2450
      7788 SCOTT            3000
      7839 KING             5000
      7844 TURNER           1500
      7876 ADAMS            5500 U
      7900 JAMES            4750 U
      7902 FORD             3000
      7934 MILLER           6500 I

14 rows selected.

I've just used U/I but this column could be (say) a numeric field or similar to handle multiple MERGE's over time.

But most people heading down this route, typically end up using separate INSERT and UPDATE blocks

How do I know what action was taken by the MERGE statement (ORACLE)?

2 Answers