Re: merge statemet
- From: "Glenn Santa Cruz" <glenn.santacruz@xxxxxxxxx>
- To: rjamya@xxxxxxxxx
- Date: Mon, 15 May 2006 10:33:42 -0500
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@xxxxxxxxx> 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?
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
