Re: Merge command - Delete - Part Two...

Follow-up question.... Has anyone had a problem with using the merge command 
and the delete option. I've been working on a real simple test case and this is 
looking like a bug.... feedback? This is running in 11.1.0.7. Is there 
something wrong in my merge command or am I just missing some key bit of 
understanding about merge and the delete option somewhere?

Drop table dodo;
Create table dodo (id number);
Create view  view_dodo as select * from dodo;
Drop table dodo;

-- This creates the table we will merge data into.
-- It will have the one valid record in it.
Drop table  merge_target;
create table merge_target
as select object_id, object_name,  object_type, status
from user_objects
where status = 'INVALID';
Alter table  merge_target
Add constraint pk_merge_target primary key (object_id);

select  object_id, object_name, object_type, status
from user_objects
where status =  'INVALID';


OBJECT_ID OBJECT_NAME                    OBJECT_TYPE          STATUS
---------- ------------------------------ ------------------- -------
70803 VIEW_DODO                      VIEW                INVALID

select  object_id, object_name, object_type, status
from merge_target
where status =  'INVALID';

OBJECT_ID OBJECT_NAME                    OBJECT_TYPE          STATUS
---------- ------------------------------ ------------------- -------
70803 VIEW_DODO                      VIEW                INVALID

-- Recreate the table, re-compile the view.
-- USER_OBJECTS will now be valid for this  object.
SQL> Create table dodo (id number);

Table created.

SQL> Alter view  view_dodo compile;

View altered.

-- Make sure the object_id is the same. This would break the merge statement
-- Also note the valid status.
SQL> Select  object_id, status from dba_objects where object_name='VIEW_DODO';

OBJECT_ID  STATUS
---------- -------
70803 VALID

-- issue the merge command. Note  that the record in the merge_target table does
-- not go away like it  should.

SQL> merge into merge_target mt
2  using user_objects uo
3  on  (uo.object_id=mt.object_id)
4  when matched then
5  update set  mt.object_name=uo.object_name,
6             mt.object_type=uo.object_type,
7             mt.status=uo.status
8         where uo.status='INVALID'
9   delete where (uo.status='VALID')
10  when not matched then
11       insert  (mt.object_id, mt.object_name, mt.object_type, mt.status)
12        values(uo.object_id, uo.object_name, uo.object_type, uo.status)
13       where uo.status='INVALID';

0 rows merged.

SQL> select * from merge_target;

OBJECT_ID OBJECT_NAME                    OBJECT_TYPE         STATUS
----------  ------------------------------ ------------------- -------
70803 VIEW_DODO                      VIEW                INVALID


 Robert G. Freeman
Oracle ACE
Author:
Oracle Database 11g RMAN Backup and Recovery (Oracle Press) - ON IT'S WAY SOON!
OCP: Oracle Database 11g Administrator Certified Professional Study Guide 
(Sybex)
Oracle Database 11g New Features (Oracle Press)
Portable DBA: Oracle  (Oracle Press)
Oracle Database 10g New Features (Oracle Press)
Oracle9i RMAN Backup and Recovery (Oracle Press)
Oracle9i New Features (Oracle Press)
Other various titles out of print now...
Blog: http://robertgfreeman.blogspot.com 
The LDS Church is looking for DBA's. You do have to be a Church member in
good standing. A lot of kind people write me, concerned I may be breaking
the law by saying you have to be a Church member. It's legal I promise! :-)
http://pages.sssnet.com/messndal/church/parachurch.pdf

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

Other related posts: