Re: Select statement question

scthomp_at_ibm.net (Simon Thompson) wrote:

>In message <3vi0mn$fhg_at_auntie.bbcnc.org.uk> - i-jacob_at_nimr.mrc.ac.uk (Ian Jacob
>) writes:
>:>
>:>In article <3vgpgp$pbr_at_warez.psyber.com>, rcox@jasmine.psyber.com says...
>:>>
>:>>I need to pull some data out of an Oracle database with SQL. It would
 greatly
>:>>simplify my life if, somehow, through the select statement I could
 insert
>:>>commas and quotes in such a manner that my spool file would be a comma
>:>>delimeted format. If this is possible please let me know.
>:>
>:>You can achieve the same with sqlplus. Try running the example below:-
>:>
>:>select ''''||dummy||''''||','||''''||dummy||'''' from dual
 

>The problem is that you have to make the record size that of the maximum row.
>Consequently, Oracle adds spaces to the end. This negates one of the
>advantages of ASCII delimited - variable row lengths. It makes the file
>larger than it should be, in some cases much larger.
 

>I use Browser or Paradox to export to ASCII delimited files.

Ryan didn't say what operating system he was running on, but in Unix what we do is concatenate a tab to the end of the line and run the cut utility to extract everything in front of it. If we're really worried about having to do it on two passes (spool the file 1st, cut it 2nd) we'll just spool the output to a named pipe that has a "cut" process running on the other end of it.

mkfifo intermediate.lst
cut -f 1 < intermediate.lst > final.lst &

sqlplus user/passwd
SET HEADING OFF
SET FEEDBACK OFF
SET ECHO OFF
SET TERMOUT OFF
SPOOL intermediate
SELECT col1 || ',' || col2 || chr(9)
FROM mytable;
SPOOL OFF
EXIT Received on Tue Aug 01 1995 - 00:00:00 CEST