Re: accessing multiple instances

naimy4_at_yahoo.com (naimy) wrote in message news:<d028e345.0407181148.89f12d0_at_posting.google.com>...
> 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_at_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>_at_<databaselink name>

Sybrand Bakker
Senior Oracle DBA Received on Mon Jul 19 2004 - 10:02:12 CEST