Re: Export to CSV

rkennedy wrote
>I have a need to export the results of a SQL*Plus query to a
>CSV file. Is there a tool similar to SQL*Loader to do this?

As far as I know Oracle does not provide it. Of course, third party tools are available.

>Or are there options in
>SQL*Plus to do this using the SPOOL command?

If you don't have to do this for a different query each day...

When you do not bother about too much spaces when a column value does not use the whole column width, then you could set the column seperator to a coma. You would get the coma on a fixed position in your file. If the first column you select has been defined as a varchar2(30), then the first coma will always be on position 31, even if the actual columnwidth is less then 30. Something like:

    set pagesize 0 -- surpress header and pagebreaks     set linesize 200
    set wrap on -- just in case linesize is too small     set trimspool on -- surpress trailing blanks     set feedback off -- surpress 'n rows selected'     set echo off -- do not copy SQL statements     set verify off -- do not echo parameter substitutions     set space 1 -- space will be replaced by colsep     set colsep ',' -- column seperator

    set termout off -- do not show output on screen     spool my_spool.csv -- spool to file
    select rownum, owner, table_name
    from all_tables;
    spool off
    set termout on

You might choose to set pagesize to something really big, to get only one header. Then also surpress the line on the second row using:

    set pagesize 100000
    set underline off

If the spaces bother you, then you need to select one single column. That is: concatenate all columns into one string yourself. This way, the coma will not be on a fixed position in the file. Not too nice:

• repeat all settings given in the first example,
• except for set colsep set termout on spool my_spool.csv prompt "number,owner,tablename" select rownum || ',' || owner || ',' || table_name from all_tables; spool off set termout off

Things get even more complicated if you want string values to be surrounded by qoutes in the output...

Arjan. Received on Sat Mar 06 1999 - 09:11:38 CET