Home » SQL & PL/SQL » SQL & PL/SQL » how to use db link in procedure
how to use db link in procedure [message #244997] Thu, 14 June 2007 17:36 Go to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
I am writing a procedure which will insert or update rows in table.
actually this will load data from one database to another. i did it with Merge statement that works fine,but now rquirement is to use procedure for insert/update.

SQL> CREATE OR REPLACE
  2  Procedure PULL_data
  3        IS
  4  BEGIN
  5       INSERT INTO abc
  6       (REQUEST_TIME,SERVER,SERVICE,CLIENT,TS_TRANSACTION,ELAPSED_TIME)
   ( Select REQUEST_TIME,SERVER,SERVICE,CLIENT,TS_TRANSACTION,ELAPSED_TIME
  7    8       From abc@link.serverabc.com) ;
  9  EXCEPTION
 10      WHEN DUP_VAL_ON_INDEX THEN
 11         UPDATE abc
 12          SET
 13          REQUEST_TIME = REQUEST_TIME
 14            WHERE
            SERVER= SERVER                       and
 15         SERVICE  = SERVICE                    and
 17         CLIENT = CLIENT                        AND
 18         TS_TRANSACTION =TS_TRANSACTION         AND
 19         ELAPSED_TIME= ELAPSED_TIME   ;
 20         END PULL_DATA;
 21          /

Procedure created.




Now how i will use DB LINK in update clause?

Thanks.

[Updated on: Thu, 14 June 2007 17:38]

Report message to a moderator

Re: how to use db link in procedure [message #244998 is a reply to message #244997] Thu, 14 June 2007 17:48 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
This does NOT answer you question but might be a viable alternative.
One of my design goals is to "always" operate against local data; in order to minimize moving large result sets across network.
Can you modify application such that a package resides on the "remote" database & operates against now "local" data there?
Re: how to use db link in procedure [message #244999 is a reply to message #244998] Thu, 14 June 2007 17:54 Go to previous message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Although Merge was working fine but our team lead hate Merge.
Actually we populate data from our productions to test and development enviroment on daily basis.
Although it doesn't generate error but as well as rows count is also different.
like


SQL>  execute PULL_DATA;

PL/SQL procedure successfully completed.

SQL>  commit;

Commit complete.

SQL>  select count(*) from abc;

  COUNT(*)
----------
    432707




SQL>  select count(*) from abc;

  COUNT(*)
----------
    981580
Previous Topic: Day of the week function in PL/SQL
Next Topic: Hello, how to get last week DAY from sysdate
Goto Forum:
  


Current Time: Tue Dec 06 12:29:51 CST 2016

Total time taken to generate the page: 0.08063 seconds