Export Oracle Data to CSV using UTL_FILE

$ mkdir -p /home/oracle/export_data

SQL> create directory export_file as '/home/oracle/export_data';
SQL> grant read,write on directory export_file to public; (Optional)

SQL>

create or replace procedure 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 utl_file.invalid_mode then
         raise_application_error(-20101,'Invalid Mode');
      when utl_file.invalid_operation then
         raise_application_error(-20102,'Invalid Operation');
      when utl_file.invalid_filehandle then
         raise_application_error(-20103,'Invalid FileHandle');
      when utl_file.write_error then
         raise_application_error(-20104,'Write Error');
      when utl_file.read_error then
         raise_application_error(-20105,'Read Error');
      when utl_file.internal_error then
         raise_application_error(-20106,'Internal Error');
      when others then
           utl_file.fclose( l_output );
           execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
           raise;
end;
/

SQL> exec dump_table_to_csv( 'ALL_OBJECTS', 'EXPORT_FILE','ALL.csv');
PL/SQL procedure successfully completed.


[oracle@ncdung ~]$ head -10 /home/oracle/export_data/ALL.csv
"OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJECT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDARY","NAMESPACE","EDITION_NAME"
SYS,ICOL$,,20,2,TABLE,15-aug-2009 00:16:51,15-aug-2009 00:29:27,2009-08-15:00:16:51,VALID,N,N,N,1,
SYS,I_USER1,,46,46,INDEX,15-aug-2009 00:16:51,15-aug-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,
SYS,CON$,,28,28,TABLE,15-aug-2009 00:16:51,15-aug-2009 00:36:04,2009-08-15:00:16:51,VALID,N,N,N,1,
SYS,UNDO$,,15,15,TABLE,15-aug-2009 00:16:51,15-aug-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,1,
SYS,C_COBJ#,,29,29,CLUSTER,15-aug-2009 00:16:51,15-aug-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,5,
SYS,I_OBJ#,,3,3,INDEX,15-aug-2009 00:16:51,15-aug-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,
SYS,PROXY_ROLE_DATA$,,25,25,TABLE,15-aug-2009 00:16:51,15-aug-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,1,
SYS,I_IND1,,41,41,INDEX,15-aug-2009 00:16:51,15-aug-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,
SYS,I_CDEF2,,54,54,INDEX,15-aug-2009 00:16:51,15-aug-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,

Note: Table, Directory must be Up-Case