Linked Servers

[Phoenix]

Greetings all,
I am collecting data from 16 different Oracle databases into a single
database. I have a stored procedure to do this, but I'd like to pass the
procedure a value (linked Oracle database name) and have the stored procedure do
something similar to:

select * from table@v_remoteserver

where v_remoteserver is a variable.

Can I do this?

Thanks all,

Ron Hagerman
http://www.rons-sandbox.com

[Billy Verreynne]

Phoenix <ne**@rons-world.net> wrote

I am collecting data from 16 different Oracle databases into a single
database. I have a stored procedure to do this, but I'd like to pass the
procedure a value (linked Oracle database name) and have the stored procedure do
something similar to:

select * from table@v_remoteserver

where v_remoteserver is a variable.

Can I do this?

Dynamic SQL. In it simplest form, something like this in PL/SQL:

EXECUTE IMMEDIATE 'select * from table@'||v_remoteserver;

See the PL/SQL manual for more details.

--
Billy