Dump a table to CSV

csv procedure dump_table_to_csv

One thing that Oracle does not do very well is CSV exports. 

Used this procedure loads. Really handy.

You need to have DUMP_DIR set up as an Oracle directory with the correct permissions.

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;


SQL> exec dump_table_to_csv('ISMA.ORGANISATION','DUMP_DIR','organisation_$NOW.csv');

 

 

Hit this issue :

 

 SQL> exec dump_table_to_csv('LEGALDBA.NWL_LEGAL_FP_MINUTES','DATA_PUMP','NWL_LEGAL_FP_MINUTES_jul26.csv');
BEGIN dump_table_to_csv('LEGALDBA.NWL_LEGAL_FP_MINUTES','DATA_PUMP','NWL_LEGAL_FP_MINUTES_jul26.csv'); END;

*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.DUMP_TABLE_TO_CSV", line 39
ORA-06512: at line 1

To fix :

 

 l_output := utl_file.fopen( p_dir, p_filename, 'w' );

 

to 

 

 l_output := utl_file.fopen( p_dir, p_filename, 'w',5000 );

but check space too as sometiumes the filesystem can fill and give the same error.