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

From: Robert Freeman <robertgfreeman_at_yahoo.com>
Date: Fri, 12 Jun 2009 10:04:01 -0700 (PDT)
Message-ID: <159858.3463.qm_at_web38905.mail.mud.yahoo.com>

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 Received on Fri Jun 12 2009 - 12:04:01 CDT