Re: dumping in ascii format

This certainly isn't the best way to load a database, but if you want to do it this way write a sqlplus script like this:

set pages 0;
set feedback off;
set termout off;
set recsep off;
spool data.sql
whenever sqlerror exit failure;
select 'insert into table2 (col1,col2,col3) values ('||col1||','||col2||','||','||col3||');' from table1;

Of course, you would substitute your real table names and the real names of your columns.

However, what you really need to do is learn sqlloader. Once you do that you can extract
your data like this:

set pages 0;
set feedback off;
set termout off;
set recsep off;
spool data.dat
whenever sqlerror exit failure;
select col1||','||col2||','||','||col3
from table1;

and you'll get a comma delimited data file that you can load with sqlloader.

Ken

Sarah Officer wrote:

> Our old database had a way to dump the data in ascii format. That
> was very useful in migrating to oracle. I specified that I only
> wanted data dumped, not objects, and the system created a bunch of
> statements:
> insert into foo(bar, baz, yaz) values('abc', 'def', 17);
>
> Is there a way to do this for oracle, or do I have to write it
> myself? The documentation seems to only show how to make a binary
> dump.
>
> I have oracle8 by the way.
>
> Thanks,
>
> Sarah Officer
> officers_at_aries.tucson.saic.com
Received on Fri Jul 09 1999 - 06:42:37 CDT