According to the Oracle docs. “Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view. This statement is a convenient way to combine multiple operations. It lets you avoid multiple INSERT, UPDATE and DELETE DML statements.”

If you search for term “oracle merge sql rowcount” you will notice that there is alot of people out their asking for how to capture the number of rows affected by a MERGE statement divided by each DML operations.

Today the Oracle database provides us with the SQL%ROWCOUNT attribute to capture the number of rows affected by a single DML operation. For a MERGE statement SQL%ROWCOUNT will then return the number of merged rows.

If you need a count of the affected rows done by a MERGE seperated by each DML operation, you should really ask yourself if MERGE is the right tool for the job. Don’t forget that the MERGE statment does not support the RETURNING clause as well.

Anyway, there is a lot of handmade helper package on the big WWW to solve this “problem”. But you can also use the Oracle’s dbms_application_info package to address this issue. dbms_application_info gives the ability to add additional application attributes for the specific session.

Let’s create an example that will do a merge and print’s the number of rows affected by the insert and update separately. The steps are as follows.

  • create table
  • insert some rows
  • create a function that count’s up the session attribute client_info using dbms_application_info package.
  • Do the merge, call the function on insert
  • Display the result
rem create a table
create table emojis(smiley_name varchar2(100),tale varchar2(200),
constraint emojis_pk primary key (smiley_name));

Table EMOJIS created.

rem insert some rows
insert all
into emojis(smiley_name,tale)
values('Grinning','A yellow face grinning')

into emojis(smiley_name,tale)
values('Smiling','A yellow face with a modest smile, rosy cheeks')

into emojis(smiley_name,tale)
values('Disappointed','A yellow face with a frown and closed, downcast eyes')

into emojis(smiley_name,tale)
values('Angry','A red face with a frowning mouth and eyes and eyebrows scrunched downward in anger')
select 1 from dual;

4 rows inserted.

rem create a function which will count the inserted rows
create or replace function merge_ins_cnt return pls_integer
  as
 begin
   dbms_application_info.set_client_info (sys_context 
   ('userenv','client_info') + 1);
   return 0;
end;
/
Function MERGE_INS_CNT compiled

rem lets do a MERGE and print our the affected insert,affected update
set serveroutput on
declare
    v_merged_rows pls_integer;
    v_number_of_ins pls_integer;
    v_number_of_upd pls_integer;
begin
    -- lets start at 0
    dbms_application_info.set_client_info( 0 );
    --for each row that is inserted call the merge_ins_cnt function
    merge into emojis a
         using (
                select 'Grinning'smiley_name,'Updated Row'tale
                from dual
                union 
                select 'Star-Struck'smiley_name,'New Row' tale
                from dual) b
       on (a.smiley_name = b.smiley_name )                 
    when matched
    then
       update set a.tale = b.tale
    when not matched
    then
       insert(a.smiley_name,
              a.tale)
       values (case merge_ins_cnt when 0 then b.smiley_name end,
               b.tale);
    
    --total affected rows by the MERGE
    v_merged_rows:=sql%rowcount;

    --get the number of inserted rows
    v_number_of_ins:=sys_context('userenv','client_info');
    --calculate the number of updated rows
    v_number_of_upd:=v_merged_rows-v_number_of_ins;
    
    --reset the counter
    dbms_application_info.set_client_info( 0 );     
    --display the result
    dbms_output.put_line('Merged rows: '||v_merged_rows); 
    dbms_output.put_line('Inserted rows: '||v_number_of_ins); 
    dbms_output.put_line('Updated rows: '||v_number_of_ins); 
    commit;
    end;
    /
    
Merged rows: 2
Inserted rows: 1
Updated rows: 1

PL/SQL procedure successfully completed.

Is this the right way to solve this problem? Is this really a problem? A MERGE is a MERGE as the name implies. I think it is useful in a test environment during development.

Over&Out