469,622 Members | 2,170 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,622 developers. It's quick & easy.

accessing multiple instances

Hi,
I need to access more that 1 instance, is there a way to start a new
connection from a pl/sql script ?
eg.
inst1 has table I1
inst2 has table I2

I need to query I2 using columns from I1. It may sound unusual
etc.etc.

I have stored the field-values its just one column from I1 and want to
connect to I2.
I thought that the following would work, but it didnt
declare
begin
execute immediate 'conn abc/abc@inst2 ';
end;
/

Want to run something like this
declare
curosr c is
select 1,2,3,4 from inst1.table1 where ---- ;
c_rec is c%rowtype;

begin
for c_rec in c loop
-- get the c_rec.1 value and query inst2

select c1 into holder1 from inst2.table2 where (.....);

end loop;
end;
/
Thanks,
Naimy
Jul 19 '05 #1
1 4357
na****@yahoo.com (naimy) wrote in message news:<d0*************************@posting.google.c om>...
Hi,
I need to access more that 1 instance, is there a way to start a new
connection from a pl/sql script ?
eg.
inst1 has table I1
inst2 has table I2

I need to query I2 using columns from I1. It may sound unusual
etc.etc.

I have stored the field-values its just one column from I1 and want to
connect to I2.
I thought that the following would work, but it didnt
declare
begin
execute immediate 'conn abc/abc@inst2 ';
end;
/

Want to run something like this
declare
curosr c is
select 1,2,3,4 from inst1.table1 where ---- ;
c_rec is c%rowtype;

begin
for c_rec in c loop
-- get the c_rec.1 value and query inst2

select c1 into holder1 from inst2.table2 where (.....);

end loop;
end;
/
Thanks,
Naimy


You can't have multiple connections in PL/SQL.
As Oracle supports the distributed database concept, you also don't
need them, as you have database links.
You'll need to set up a database link
(create database link foo connect to bar identified by password using
'<tns servicename>'
)
and issue your query like
select * from <table>@<databaselink name>

Sybrand Bakker
Senior Oracle DBA
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Vivek Sharma | last post: by
3 posts views Thread by Marcin Kalicinski | last post: by
reply views Thread by devrayhaan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.