Re: Transfer data from oracle to oracle using sqlplus only

EdStevens wrote:
> 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.

Heinz, the program that can read a fixed position or delimited spool file created from sqlplus and put it back in the database is sqlldr. By defining an External Table in the target database for each file created by the source you could then use an insert/select to invoke sqlldr from sqlplus on the target and populate your target tables with the extracted data.

See the Oracle Concepts and DBA Administration manuals for your version for more information.

Ed, had a good question: why can't you define a link that has read only access to the source table for the purpose of the transfer? The link could be dropped as soon as the transfer is complete. Though a sqlplus unload script, ftp task, and another sqlplus plus load script makes a nice repeatable job at first glance. I suspect that the extract queries will need some minor treaking each time you run this anyway so I would want to use a link if it were me doing the work.

HTH -- Mark D Powell -- Received on Tue Aug 29 2006 - 08:54:15 CDT