Re: Transfer data from oracle to oracle using sqlplus only

Heinz wrote:
> I have an oracle db on one system and another on a completely separate
> system
> (no link between the two is possible).
> I want to take parts of tables from one oracle db to the other
> (specific customers data that is).
> eg one row from several tables and stick the rows into the other oracle
> db.
>
> I know about spooling, but what query reads from a spooled file and
> inserts the data
> into a table??
>
> I am very interested to know this as I cannot work it out myself!!
>
>
> Heinz
>
> ps I only have sqlplus on dos/unix screens to work with, and cannit
> install
> anything.

Perhaps if you more fully explained why the severe restrictions. Do you not have access to imp/exp? Sqlloader? Are you having to drive this all from your desktop with no direct access to the db servers? If so, why?

That said, there is no query that "reads from a spooled file and inserts the data into a table". You might try writing a script that writes a script, like this:

connect scott/tiger_at_sourcedb
set echo off feedback off pages 0
spool doit.sql
select 'insert into mytargettable values (' || col1 || ',' || col2 || ');'
from mysource table;
spool off

Then connect to your target db and execute doit.sql

This is off the top of my head, so you may have to play with the details. Received on Tue Aug 29 2006 - 07:30:56 CDT