sending sql output to a csv file

So I would previously have been doing something like this

spool myfile.csv;
set heading off
set pages off
select column1_name||','||column2_name||','||column3_name||','||column4_name
from mytable order by 1;
spool off

which is fine but I had to build up the statement each time.


Step in Tom, I found this procedure tucked away in an asktom page from 2000

sqlplus / as sysdba
grant execute on utl_file to system;
create or replace directory DPUMP_TMP as '/tmp';
grant read,write on directory DPUMP_TMP to system;


CREATE OR REPLACE procedure SYSTEM.dump_table_to_csv( p_tname in varchar2,
                                               p_dir   in varchar2,
                                                p_filename in varchar2 )
   is
     l_output        utl_file.file_type;
     l_theCursor     integer default dbms_sql.open_cursor;
      l_columnValue   varchar2(4000);
    l_status        integer;
    l_query         varchar2(1000)
                      default 'select * from ' || p_tname;
    l_colCnt        number := 0;
     l_separator     varchar2(1);
    l_descTbl       dbms_sql.desc_tab;
 begin
      l_output := utl_file.fopen( p_dir, p_filename, 'w' );
     execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

     dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
      dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

    for i in 1 .. l_colCnt loop
          utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name || '"' );
         dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
       l_separator := ',';
      end loop;
     utl_file.new_line( l_output );

     l_status := dbms_sql.execute(l_theCursor);

      while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
        l_separator := '';
          for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, l_columnValue );
              utl_file.put( l_output, l_separator || l_columnValue );
              l_separator := ',';
         end loop;
          utl_file.new_line( l_output );
     end loop;
     dbms_sql.close_cursor(l_theCursor);
       utl_file.fclose( l_output );

     execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
   exception
     when others then
         execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
          raise;
 end;
/

exec SYSTEM.DUMP_TABLE_TO_CSV ('schema.tablename','DPUMP_TMP','myfilename.csv');

If you want to read more about Tom talking about unloading data to a flat file go here http://tkyte.blogspot.co.uk/2009/10/httpasktomoraclecomtkyteflat.html