Re: Exporting data to ASCII files - HOW?

From: <bamon_at_ocvaxc.cc.oberlin.edu>
Date: 24 Jun 93 11:41:41 EDT
Message-ID: <1993Jun24.114141.1_at_ocvaxc.cc.oberlin.edu>


In article <1993Jun24.062259.22494_at_alf.uib.no>, Gunnar.Sylthe_at_nsd.uib.no writes:
>
> We need to export data from Oracle tables to ASCII files. Is there an easy
> way to do this?
>
> --Gunnar

You can do it with a pro*C or pro*something_else program.

I wrote a program called dumptable, that takes 3-7 parameters, and writes the contents of the table to an ascii file. The parameters look like this:

  1. table name
  2. table owner
  3. column delimeter in the output file, valid values are COMMA and TAB
  4. column(s) for an ORDER BY clause
  5. flag indicating whether column headers with the column names should be included at the top of the output file
  6. columns to be omitted, e.g. ('colname1','colname2',...,'colnameN')
  7. columns to be included, e.g. ('colname1','colname2',...,'colnameN')

The user running the program must have access to the specified table.

The program uses dynamic SQL to get the names of the columns in the specified table WHERE COLUMN_NAME NOT IN param6 AND COLUMN_NAME IN param7, and to build a cursor for the subsequent fetches from the table.

If param4 is not null, then an ORDER BY clause is added to the cursor text.

If param5 is not null, then a line is written to the ascii file, with the column names, delimited with the same character as the column values.

The column values are written to the ascii file with quotes around each value, and with the specified delimeter.

We use this to extract data for use in print-merge operations from word processors. It works well.

Good luck!


Jennifer R. Amon            PHONE: (216) 775-6987   
Houck Computing Center        FAX: (216) 775-8573    
Oberlin College          
Oberlin, OH 44074        INTERNET: bamon_at_ocvaxc.cc.oberlin.edu
_____________________________________________________________________
Received on Thu Jun 24 1993 - 17:41:41 CEST

Original text of this message