OraFAQ Forum: SQL & PL/SQL » how to assign the database link dynamically to a table

Home » SQL & PL/SQL » SQL & PL/SQL » how to assign the database link dynamically to a table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to assign the database link dynamically to a table [message #38587]

Mon, 29 April 2002 08:24

ksr
Messages: 112
Registered: January 2002

Senior Member

Hi,

I want to select the records from different database(same table existing on all the database) and update them.
I have 3 separate database,so i have created 3 different database links.I want to assign the 3 different database links to the select statement one after the another and update the tables.

I want something like this..

create or replace procedure test() is

arr1(1):= lnk.db1.com;
arr1(2):= lnk.db2.com;
arr1(3):= lnk.db3.com;

cursor c1 is select * from table1@ --want to assign database links
---------------------------------------
How to assign the differnt database links to the cursor (table name )dynamically..or if not through cursor is there any other way...

begin
for 1 ..array count loop
for cursor in c1 loop
update table@ --again assign the database link here..
end loop;
end loop;
end;

How do i write this code..

Report message to a moderator

Re: how to assign the database link dynamically to a table [message #38590 is a reply to message #38587]

Mon, 29 April 2002 09:29

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Do you really need to have a cursor loop here? Why not just:

for i in 1..arr1.count loop
  execute immediate 'update table@' ||  arr1(i) || ' set ...';
end loop;

Report message to a moderator

Re: how to assign the database link dynamically to a table [message #38610 is a reply to message #38587]

Tue, 30 April 2002 06:25

ksr
Messages: 112
Registered: January 2002

Senior Member

Hi,
Actually i will be retrieving lot of records based on various conditions, then i will crosscheck with another table and wither insert ot update accordingly,
so i need a cursor at any cost.

How to do with the cursor

Report message to a moderator

Re: how to assign the database link dynamically to a table [message #38617 is a reply to message #38610]

Tue, 30 April 2002 09:05

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Well, you'll have to use dynamic SQL as described in my reply to you in the other thread (weak ref cursor open v_rc for 'select * from table@' || v_dblink(i) || ...') and then execute immediate to do the update.

Report message to a moderator

Previous Topic:	limiting number of rows in a group by function call
Next Topic:	how to get the value of count(*) in plsql

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 21:02:21 CDT 2024