Re: How to export to comma delimited file from Oracle8?

From: Ian Cary <cary_at_gatwick.geco-prakla.slb.com>
Date: 1997/09/30
Message-ID: <3430EBDB.337D_at_gatwick.geco-prakla.slb.com>#1/1


Robert wrote:
>
> spool the data to A file and then using sql*plus issue a command that
> resembles this
>
> spool test.txt
>
> select
> rtrim(col1) || ',' || rtrim(col2) || ',' || rtrim(col3) || ',' ||
> rtrim(col4) || ',' || rtrim(col5) || ',' || rtrim(col6) || ',' ||
> rtrim(col7) || ',' || rtrim(col8) from table;
>
> spool off

Hi again Kelly,

What Robert wrote will enable you to output comma delimited files, however having read your mail I was wondering whether or not the following approach may me better.
It seems as if you will be creating a new set of tablespaces and tables and will want to migrate your existing data to these new tables. As you want to migrate the data I assume the columns will be similar - if so it will be easier to do the following;

If the columns are defined with the same data types in the same order you can use;

insert into new_table
select * from old_table;

if column positions have been altered or some removed you can use;

insert into new_table
select column3,column1,column5 .... from old_table;

Extending this further the select statement populating the new table can even be a more complex query, but hopefully these examples will have given you the genaral idea.
Assuming all your new tables are in new tablespaces and that you have no use for the old tablespace
you can use the command;

drop tablespace including contents;

to tidy up after you have finished (assuming you weren't using the system tablespace of course).

Hope this helps,

Ian Received on Tue Sep 30 1997 - 00:00:00 CEST

Original text of this message