Oracle SQ scripts

[laylay]

I use Infomaker as my isql editor to query an Oracle database (prod_db) to create reports. I only have select privs to the database.

I was just given access to the development database (dev_db) so that I can successfully write SQL scripts like...

CREATE TABLE tbl_temp....

which I cannot execute against prod_db due to my limited privs.

My problem: After I create my temp table (tbl_temp) in dev_db, I want to INSERT INTO tbl_temp

I want to insert rows from prod_db, but I am connected to dev_db at this point!

I do not have the ability to create procs. This must all be done in regular SQL.

Is it possible to switch connections on the fly in SQL, or to be connected to both dbs simultaneously? Can you show me the syntax for this?

thanks

[jkoopmann]

you can, if connectivity is available, create a database link to your production box and then select through the link.

for example:
create database link prod_db connect as user identified by password using 'tns';

insert into tbl_temp as select (something) from tbl_prod@prod_db;

hopefully you can follow this simplistic example since i don't know all the names of the objects and deffinitions that you have and you will need to supply the true object names, tns, user, password, and something.