Re: Exporting data to ASCII files - HOW?

Hi

Hope this is what you are looking for. (I found it in SQL*Plus User's Guide & Ref version 3.1 Page 4-29 as I need to do something similar).

	set echo off
	spool output_file
	set newpage 0
	set space 0
	set linesize 140 <---- set this to a large number (sum of cols plus a bit)
	set pagesize 0
	set echo off
	set feedback off
	set heading off
	select..............
	from ..........
	spool off

This creates a flat file from the table.

Problem.
If your field is defined as 80chars and it actually only contains 5chars of data, then the remaining 75 chars WILL be padded with spaces (RTRIM etc. doesn't work as selecting whole table at once).

As this was no good for my application, I quickly wrote a bit of PL/SQL to get around this (it reads each record and builds a variable containing the record with each of the fields concatenated, with no trailing spaces). Unfortunately there is no PRINT in PL/SQL, so the only way to see this variable is to either store it in a table and use the above script in SQL*Plus, or use a Pro*C/COBOL/etc. program (may as well scrap the PL/SQL and write the whole thing in Pro*C from scratch if opting for the latter).

One alternative I haven't had a chance to investigate yet is using SQL*ReportWriter - someone mentioned I might be able to produce variable length records using this. As I haven't really used it, I will probably write a Pro*C program as it will be quicker for me. Has anyone used ReportWriter to do this?

I keep getting the feeling that as this seems to be so straight-forward, I must be missing something really obvious! Am I?

Hope this helps

Paul

/******************************************************************************/
Paul Narth pnarth_at_axion.bt.co.uk / cs90pkn_at_brunel.ac.uk Ipswich Engineering Centre
B81 G61
BT Labs
Martlesham Heath
Ipswich
Suffolk
IP5 7RE 0473 640746 / (+44) 473 640746

"The path of excess leads to the tower of wisdom"

• W Blake
  /******************************************************************************/