Re: merge statemet

Raj -

Maybe try package variables to track this:

drop table tgt;
drop table src;

create or replace package merge_cnt as
  i_cnt number := 0;
  u_cnt number := 0;
  function ctr( a char, b date ) return date;   procedure rst;
end merge_cnt;
/

create or replace package body merge_cnt as function ctr( a char, b date ) return date is begin
  if a = 'I' then
    i_cnt := i_cnt + 1;
  else
    u_cnt := u_cnt + 1;
  end if;
  return b;
end ctr;
procedure rst is
begin
  i_cnt := 0;
  u_cnt := 0;
end rst;
end merge_cnt;
/

create table tgt( x int primary key, y int, z date ); create table src( x int primary key, y int );

insert into src( x, y ) values (1, 1);
insert into src( x, y ) values (2, 2);
insert into src( x, y ) values (3, 3);

insert into tgt( x, y, z ) values (1, 1, sysdate);

exec merge_cnt.rst;

merge into tgt t1
using (
  select x, y from src
) t2
on (
  t1.x = t2.x
)
when matched then

    update set

        t1.y = t2.y, t1.z = merge_cnt.ctr( 'U', t1.z ) when not matched then

    insert (x, y, z)
    values (t2.x, t2.y, merge_cnt.ctr('I',sysdate)) /

set serverout on size 10000

begin
  dbms_output.put_line('inserts:'||merge_cnt.i_cnt);   dbms_output.put_line('updates:'||merge_cnt.u_cnt); end;
/

On 5/15/06, rjamya <rjamya_at_gmail.com> wrote:
> Maybe a this is a mental block, but anyone know if there are any
> attributes available that would tell us in a merge statement, how many
> were insert and how many were updates? 9i or 10g
>
> TIA
> Raj
> ----------------------------------------------
> Got RAC?
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l