Re: Spooling variable length output

In article <487pjm$n4m_at_ixnews3.ix.netcom.com>, chuckh_at_ix.netcom.com (Chuck Hamilton) writes:
|> Greg Hayes <greg_at_hayford.demon.co.uk> wrote:
|>
|> >If I want to spool the output from a select statment to a file
|> >then I SET LINESIZE 338 let us say. However if the data in the
|> >file is comma delimited (as in "abcd","defg","","1","sfgdtg"),
|> >thenthe record is always 338 bytes long instead of 29 as in
|> >the example above. Does anyone have a soution to this.
|>
|> What I've done in the past is to append a TAB character (chr(9)) to
|> the end of the line and use the the UNIX 'cut' utility to lop off
|> everything from the TAB on.
|>

I prefer to simply use the regular expressions that UNIX has. My changes are simply
|> In sqlplus...
|>
|> SET PAGESIZE 0
|> SET LINESIZE 338
|> SPOOL myfile
|> SELECT '"'||col1||'","'||col2||'","'||col3||'"'||chr(9)
SELECT '"'||col1||'","'||col2||'","'||col3||'"'

   Don't bother whith the tab/chr(9) stuff
|> FROM mytable;
|> SPOOL OFF
|>
|> In UNIX...
|>
|> cut -f1 myfile.lst > temp
sed 's/ *$//g' myfile.lst > myfile.temp

        Note that the pattern is 2 spaces before the *
|> mv temp myfile.lst

Actually, I'd also set up the spool to a named pipe, and then create the myfile.lst as part of a parallel pipeline with the sqlplus preceeding the sed. Why waste system resources on such a trivial task as stripping trailing blanks.

-- 
Names:	Bill Beaton			beatonb_at_cadvision.com
					Bill_Beaton_at_digitech.ab.ca
Phone:	(403) 295-3254 (Home)		(403) 266-8622 (work)