Re: Export table to comma delimited file?

From: Mike Philippens <mikephil_at_metropolis.nl>
Date: 1995/09/09
Message-ID: <DEMstC.7BE_at_news.metropolis.nl>#1/1

dwest_at_ad3100.ada.epa.gov (Dan West) wrote:

>Is there a simple way to export an Oracle 7 table to a comma delimited
>ASCII file? I need to transport data from Oracle on a UNIX system to
>FoxPro on a PC. SQL*Loader allows me to import data in this format but
>I can't seem to find a way to export in this format short of having to
>specify every field and use concat with a select statement or writing
>an embedded SQL C program.

So if I'm reading this correctly you know how to do it, but you don't want to type all the columns in a SQL statement. You could let SQL*Plus build it's own export file without difficult C routines. And this is a portable solution:

Make a SQL command file which 'creates' another SQL command file containing the required SQL statement. The SQL statement in your 'create' file should read the contents of the datadictionary for the table that you want (COLUMN_NAME in USER_TAB_COLUMNS). The only tricky thing is to identify the first/last field in the table to prevent a concatenation sign too much in the resulting file. You could try something with decode and rownum.

[all the obvious SET commands]
-- start with spool command
spool prep_export.sql
select 'spool export.asc'
from dual;
-- select the start of the statement
select 'select '||column_name
from user_tab_columns
where table_name = 'table'
and rownum < 2;
-- select the middle of the statement
select '||','||' decode(rownum,1,null,

                        column_name) <- you have to figure
                                        the quote business
                                        out yourself. I'm
                                        writing this out of
                                        memory.

from user_tab_columns
where table_name = 'table';
-- knit a nice ending to it
select ' from table_name;'
from dual;
select 'spool off'
from dual;
spool off

the resulting file should be something like:

spool export.asc
select COLUMN1
||','||COLUMN2
||','||COLUMN3
from table_name;
spool off

This is the general idea. This method is used quite often to do this kind of thing when you wouldn't want to write a C program. You can also re-create create table statements with this method. Hope this helps.

+------------------------------------------------------------+
|   Mike Philippens - Gorinchem - The Netherlands, Europe    |
|                 Vijfhart Automatisering bv                 |
|       Oracle Specialists Training and Consultancy          |
|                 Utrecht    The Netherlands                 |
+------------------------------------------------------------+

Received on Sat Sep 09 1995 - 00:00:00 CEST