Re: Export table to comma delimited file?

From: Peter Evans <pevans_at_olmsys.demon.co.uk>
Date: 1995/09/10
Message-ID: <810739716.24584_at_olmsys.demon.co.uk>#1/1


mikephil_at_metropolis.nl (Mike Philippens) wrote:

>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

snip

>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.

example code deleted

>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 |
>+------------------------------------------------------------+

I wrote a couple of .SQL scripts to do just this a couple of years ago when we needed to do a version 7 to version 6 data transfer. I haven't used them for a while but they produced what Mike Philippens was describing. Basically, they accept a table name and username/pw as input and produce a complete SQL*Loader file (header and data) as output. Limitations include the inability to handle Long & Long Raw data (and possibly other datatypes that we don't use) and the fact that I haven't used them for a long while. They use SQL & PL/SQL and are run from either a DOS batch file or a Unix shell script in order to be able trim trailing spaces. If anybody wants a copy of the code (no guarantees or documentation sadly) I can E-Mail a zip file of the necessary scripts.

Pete Received on Sun Sep 10 1995 - 00:00:00 CEST

Original text of this message