Home » SQL & PL/SQL » SQL & PL/SQL » Procedure with merge (Oracle 9i)
Procedure with merge [message #346012] Fri, 05 September 2008 10:26 Go to next message
pointers
Messages: 410
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 #346019 is a reply to message #346012] Fri, 05 September 2008 11:02 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>But the above code is not giving the needed output.
because it contain syntax error
Re: Procedure with merge [message #346025 is a reply to message #346012] Fri, 05 September 2008 11:27 Go to previous messageGo to next message
pointers
Messages: 410
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.

Re: Procedure with merge [message #346026 is a reply to message #346012] Fri, 05 September 2008 11:32 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above


You have data.
You have code.

Why do you expect folks to try to debug code which they can not see with data they do not have?

You're On Your Own (YOYO)!
Re: Procedure with merge [message #346029 is a reply to message #346012] Fri, 05 September 2008 11:47 Go to previous message
pointers
Messages: 410
Registered: May 2008
Senior Member
I checked, I dint get any syntax error....
Previous Topic: Which is better? For loop or Using cursor
Next Topic: Can i connect to telnet(send commands) from a procedure?
Goto Forum:
  


Current Time: Fri Dec 09 23:18:59 CST 2016

Total time taken to generate the page: 0.11775 seconds