Export Data in CSV format

Export Data in CSV format

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 others then

execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';

raise;

end;

/
Procedure created.
exec dump_table_to_csv( 'emp', '/tmp', 'tkyte.emp' );
PL/SQL procedure successfully completed.

ops$tkyte@8i> host cat /tmp/tkyte.emp

"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"

7369,SMITH,CLERK,7902,17-dec-1980 00:00:00,800,,20


7566,JONES,MANAGER,7839,02-apr-1981 00:00:00,2975,,20


7698,BLAKE,MANAGER,7839,01-may-1981 00:00:00,2850,,30



See the Supplied Packages guide for info on utl_file. You must set some init.ora

parameters to enable it to work correctly....
This can be done via sqlplus as well -- just not as nicely in my opinion.