In our current requirement, we capture the total number of updates and inserts for our application. When ever we run the ETL job, we capture insert and update details. But by using merge, we are unable to capture those details. but the requirement specs, says, we need to capture those details.. hence my question.

I have noticed that MERGE statement is faster compared to the loop we use (FOR x IN (SELECT * FROM Emp)).

regards,
satish

I knew that we have at least two ways to update the previous question. The first is to update a join view and the second is to use a correlate query.

update
(select ....
from table_a, table_b
where (join condition))
set column_in_a = column_in_b

or

update table_a
set column_in_a =
(select column_in_b
from table_b
where (join condition)
where exists (select ...)

Could you explain how we can choose these two choices?

Thank you

I used search facility from your site to find, about triggers and realised that, it won't be feasiable.

Is it possible, oralce would provide the number of inserts or updates in future relase.

regards,
satish

--1 Compile the ETL PKG
--2 SAVE the RUN_MRG to a .SQL FILE
--3 Do the Data Settings and then Run
-----------------------------------------------------------------------------------------------------
--------------------------------------- START OF ETL PKG --------------------------------------------
-----------------------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE etl AS

c_inserting CONSTANT PLS_INTEGER := 0;
c_updating CONSTANT PLS_INTEGER := 1;

FUNCTION merge_counter (
action_in IN PLS_INTEGER DEFAULT c_inserting
) RETURN PLS_INTEGER;

FUNCTION get_merge_update_count RETURN PLS_INTEGER;

FUNCTION get_merge_update_count (
merge_count_in IN PLS_INTEGER
) RETURN PLS_INTEGER;

FUNCTION get_merge_insert_count RETURN PLS_INTEGER;

FUNCTION get_merge_insert_count (
merge_count_in in PLS_INTEGER
) RETURN PLS_INTEGER;

PROCEDURE reset_counters;

END etl;
/

CREATE OR REPLACE PACKAGE BODY etl AS

g_update_counter PLS_INTEGER NOT NULL := 0;
g_insert_counter PLS_INTEGER NOT NULL := 0;

/*-------------- START OF FUNCTION merge_counter --------------------*/
FUNCTION merge_counter (
action_in IN PLS_INTEGER DEFAULT c_inserting
) RETURN PLS_INTEGER IS
BEGIN
CASE action_in
WHEN c_updating
THEN g_update_counter := g_update_counter + 1;
WHEN c_inserting
THEN g_insert_counter := g_insert_counter + 1;
ELSE
RAISE PROGRAM_ERROR;
END CASE;
RETURN 0;
END merge_counter;

/*----------- START OF FUNCTION get_merge_update_count V1 ---------------*/
FUNCTION get_merge_update_count
RETURN PLS_INTEGER is
BEGIN
RETURN g_update_counter;
END get_merge_update_count;

/*----------- START OF FUNCTION get_merge_update_count V2 ---------------*/
FUNCTION get_merge_update_count (
merge_count_in IN PLS_INTEGER
) RETURN PLS_INTEGER IS
BEGIN
RETURN NVL( merge_count_in - g_insert_counter, 0 );
END get_merge_update_count;

/*----------- START OF FUNCTION get_merge_insert_count V1 ---------------*/
FUNCTION get_merge_insert_count
RETURN PLS_INTEGER IS
BEGIN
RETURN g_insert_counter;
END get_merge_insert_count;

/*----------- START OF FUNCTION get_merge_insert_count V2 ---------------*/
FUNCTION get_merge_insert_count (
merge_count_in IN PLS_INTEGER
) RETURN PLS_INTEGER IS
BEGIN
RETURN NVL( merge_count_in - g_update_counter, 0 );
END get_merge_insert_count;

/*-------------- START OF FUNCTION reset_counters --------------------*/
PROCEDURE reset_counters IS
BEGIN
g_update_counter := 0;
g_insert_counter := 0;
END reset_counters;

END etl;
/

-----------------------------------------------------------------------------------------------------
--------------------------------------- END OF ETL PKG ----------------------------------------------
-----------------------------------------------------------------------------------------------------
--
--
--
-----------------------------------------------------------------------------------------------------
--------------------------------------- START OF DATA RUN_MRG ---------------------------------------
-----------------------------------------------------------------------------------------------------
set serverout on
set echo on
set pagesize 100
--
-- Run merge...
--
begin

etl.reset_counters;

merge into target tgt
using source src
on (src.id = tgt.id)
when matched then
update
set value = (case etl.merge_counter(etl.c_updating)
when 0
then src.value
end)
when not matched then
insert
( tgt.id
, tgt.value )
values
( case etl.merge_counter(etl.c_inserting)
when 0
then src.id
end
, src.value );

/* Use update count... */
dbms_output.put_line(sql%rowcount || ' rows merged.');
dbms_output.put_line(etl.get_merge_update_count || ' rows updated.');
dbms_output.put_line(etl.get_merge_insert_count(sql%rowcount) || ' rows inserted.');

/* Use insert count... */
dbms_output.put_line(etl.get_merge_update_count(sql%rowcount) || ' rows updated.');
dbms_output.put_line(etl.get_merge_insert_count || ' rows inserted.');

end;
/

-----------------------------------------------------------------------------------------------------
--------------------------------------- END OF DATA RUN_MRG -----------------------------------------
-----------------------------------------------------------------------------------------------------

--
--
--
-----------------------------------------------------------------------------------------------------
--------------------------------------- START OF DATA SETTINGs --------------------------------------
-----------------------------------------------------------------------------------------------------

spool mg
drop table source;
drop table target;
--
-- Setup source and target tables...
--
create table source ( id int, value varchar2(1) ) tablespace small_data;
create table target ( id int, value varchar2(1) ) tablespace small_data;

insert into source select rownum, substr(object_type,1,1) from user_objects where rownum <= 15;
insert into target select * from source where rownum <= 10;
commit;

-----------------------------------------------------------------------------------------------------
--------------------------------------- END OF DATA SETTINGs ----------------------------------------
-----------------------------------------------------------------------------------------------------


select * from source;
select * from target;
--

select * from source
minus
select * from target;
--
select * from target
minus
select * from source;
--
-- Notice the Difference between two

-- Run the RUN_MRG to MERGE and SEE the RESULTs
@run_mrg


select * from source
minus
select * from target;
--
select * from target
minus
select * from source;
--
---

-- Rollback to Duplicate some source data before Merge
-- this doesn't work becase the source has some dups
-- unable to get a stable set of rows in the source tables ERROR
--
Rollback;
insert into source select id, lower(value) from source where rownum <= 5;
@run_mrg
----




--
-- Rollback and Duplicate some target data & Then MERGE
--
Rollback;
insert into target select id, lower(value) from target where rownum <= 5;

select * from source
minus
select * from target;
--
select * from target
minus
select * from source;
--
@run_mrg
--


select * from source
minus
select * from target;
--
select * from target
minus
select * from source;
--
commit;

drop table source;
drop table target;

spool off
set echo off

Hi Tom,

I came across a interesting substitution to merge statement and just wanted to know your views on it.

we have one fact say TAB1 and one daily incremental table say TAB2. We used to merge the data of TAB2 to TAB1.
Our TA suggested instead of doing a Merge on tables we use a NVL(tab2.col1,tab1.col2) and similiar on other columns and do a full outer join on the tables TAB1 and TAB2. This way we get the inserts as well as the updates as we do a NVL with the first parameter of NVL as the column of incremental table. There are only 8 columns in the table.


Can you please comment of this approach.


Regards,
Snehasish Das,

Right after a MERGE statement in a PL/SQL block...

1) Will SQL%ROWCOUNT return the number of rows affected, just like after UPDATE, DELETE and INSERT ?

2) Are there other bluit-in counters like SQL%ROWCOUNT specifically for INSERTS, UPDATES and DELETES within a MERGE ?

Thanks Tom =)

(cont'd) I'm using 10.2 for the question right above.