Home » SQL & PL/SQL » Client Tools » sqlplus copy command and temporary tables (Oracle 9i HP-UX)
sqlplus copy command and temporary tables [message #355484] Fri, 24 October 2008 07:19 Go to next message
rmaire
Messages: 2
Registered: October 2008
Junior Member
Hello All

(Sorry for my english, I'm not a native speaker)

I have to do a data extraction via sqlplus on a schema where I can't create physical tables. This extraction needs keys from a different schema on another host. So I thought I could extract the keys on the remote schema and copy them to a temporary table to loop over them. So my question: Is it possible, to use the copy command for inserting into a temporary table?

Thanks in advance.

Kind regards,
Roman
Re: sqlplus copy command and temporary tables [message #355496 is a reply to message #355484] Fri, 24 October 2008 08:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So I thought I could extract the keys on the remote schema and copy them to a temporary table to loop over them.

NO! Not in Oracle.
Just use an "select ... from ... where key in (select ... from remotetable)".

Regards
Michel
Re: sqlplus copy command and temporary tables [message #355502 is a reply to message #355496] Fri, 24 October 2008 08:57 Go to previous messageGo to next message
rmaire
Messages: 2
Registered: October 2008
Junior Member
Michel Cadot wrote on Fri, 24 October 2008 08:40
Quote:
So I thought I could extract the keys on the remote schema and copy them to a temporary table to loop over them.

NO! Not in Oracle.
Just use an "select ... from ... where key in (select ... from remotetable)".



Unfortunately, this is not possible because I don't have a database link to the remote table. Creating one needs more time as I have.

I thought about something like this:

create global temporary table tmp_key_table
(
keys integer
) on commit preserve rows;

copy from from_user/from_password@from_database to
to_user/to_password@to_database
insert tmp_key_table using select keys from remote_key_table;

If this is not possible, are there any other possibilities? I just need to import a given list of integers to loop over in a PL/SQL block.

Kind regards,

Roman
Re: sqlplus copy command and temporary tables [message #355521 is a reply to message #355502] Fri, 24 October 2008 11:02 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just try it but if you need a database link for your application then create it (or make it created if you have not the privilege).

Regards
Michel
Previous Topic: connect to Remote Data base
Next Topic: to compare two dataabses tables
Goto Forum:
  


Current Time: Fri Dec 09 23:11:02 CST 2016

Total time taken to generate the page: 0.07704 seconds