Procedure with merge [message #346012] |
Fri, 05 September 2008 10:26  |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
Hi,
There are 3 tables as application,app_contact,xml and the table structre are as follows.
SQL> desc application;
Name Null? Type
----------------------------------------- -------- ----------------------------
APP_ID NOT NULL NUMBER(10)
SUB_BUS_ID NOT NULL NUMBER(10)
APP_NAME NOT NULL VARCHAR2(250)
APP_URL VARCHAR2(2000)
APP_CLICKSTREAM VARCHAR2(2000)
APP_COST_CENTER VARCHAR2(20)
APP_SSO_ENABLED_IND NUMBER(1)
APP_SSO_PAIR VARCHAR2(50)
APP_SILENCED_IND NUMBER(1)
APP_LOCATION_ID NUMBER(10)
RUN_INTERVAL NUMBER(4)
SQL> desc app_contact;
Name Null? Type
----------------------------------------- -------- --------------------------
app_id
APP_NAME VARCHAR2(250)
APP_TYPE VARCHAR2(250)
CIS_APP_LEAD_NAME VARCHAR2(250)
CIS_APP_LEAD_SSO NUMBER(9)
L1_NAME VARCHAR2(250)
L1_SSOID NUMBER(9)
L2_NAME VARCHAR2(250)
L2_SSOID NUMBER(9)
L3_NAME VARCHAR2(250)
L3_SSOID NUMBER(9)
SQL> desc xml;
Name Null? Type
----------------------------------------- -------- ----------------------------
APP_NAME VARCHAR2(250)
APP_ID NUMBER(10)
APP_TYPE VARCHAR2(250)
APP_OWNER_NAME VARCHAR2(250)
LEADER_NOTIFIED VARCHAR2(250)
The actual thing is to update the target table(XML) with the source table (app_contact), if matching field(app_id) is there. Otherwise insert should be taken in the target table. But for this the app_id in the source table should be checked with the application table that contains all the app_ids.
I have written a code, it actually does the following..
My procedure should compare app_id from the app_contact table withe the app_id column in the application table. If there is matching then the app_id of app_contact table should be compared with the app_id in the XML table. If it exists then the XML table should be updated with the app_contact rows.
If the data is not matched then the corresponding data should be inserted into the XML table.
I have created the following procedure and used merge for it.
create or replace procedure xml_p as
cursor check_cursor is select app_id from application;
cursor source_cursor is select app_id from app_contact;
id application.app_id%type;
sid app_contact.app_id%type;
begin
for a in source_cursor loop
for b in check_cursor loop
if(a.app_id=b.app_id) then
merge into xml t using app_contact o
on (t.app_id=b.app_id)
when matched then
update set t.APP_TYPE=o.APP_TYPE,
t.app_name=o.app_name,
t.APP_OWNER_NAME=(select L1_NAME||';'||L2_NAME||';'||L3_NAME from app_contact where app_id=o.app_id)
when not matched then
insert(t.APP_NAME,t.APP_TYPE,t.APP_OWNER_NAME,t.app_id) values(o.APP_NAME,o.APP_TYPE,o.L1_NAME||';'||o.L2_NAME||';'||o.L3_NAME,o.app_id);
end if;
end loop;
end loop;
end;
But the above code is not giving the needed output. Please suggest where my logic is missing.
I can use some temporary tables but I tried with merge.
Regards,
Pointers.
|
|
|
|
Re: Procedure with merge [message #346025 is a reply to message #346012] |
Fri, 05 September 2008 11:27   |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
I have manipulated the actual code. Syntactically the earlier one was fine. Please correct if anything is missed out
The Problem what i am facing is, the merge statement is doing update or insert though there is not an equvalent app_id in the application with the app_id in the app_contact.
|
|
|
|
|