Re: Exporting data to ASCII files - HOW?

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

Assume that you have a table containing fields NAME, ACCT_NO and LAST_SALE_DATE where LAST_SALE_DATE might be a null value.

To extract to an ASCII (flat) file use the following:

	SET PAGESIZE 0   -- turns off all formatting, column heads, etc
	SPOOL THE_FILE   -- turn on the spooler for output
	SET LINESIZE 132 -- needed if the output is over 80 columns

	SELECT NAME, ACCT_NO, LAST_SALE_DATE, '999'
	FROM   THE_TABLE
	ORDER BY NAME;

 	SPOOL OFF        -- turn off the listing and we are done

The field '999' in the select statement is a dummy variable to define the boundries of the optional field LAST_SALE_DATE. Makes the output easier to read.

As shown, the listing will include embedded blanks between each column. If you want to supress them, add the statement SET SPACE 0 to the set statements at the top of the listing. I prefer to include blanks because the listing is easier to read.

If you are seeking a delimited output file -- for example, where each column is marked by a comma for subsequent use as a variable list in a Microsoft WORD merge routine -- then change the select statement to read:

	SELECT NAME           || ',' ||
               ACCT_NO        || ',' ||
               LAST_SALE_DATE || ',' ||
               '999'
   	FROM   THE_TABLE
	ORDER BY NAME;

The output will have a single column containing each variable separated by a comma with all trailing spaces removed.

Hope this helps. Received on Fri Jun 25 1993 - 13:34:55 CEST