Oracle simple function to create CSV.

This PL/SQL code function will quickly help to write CSV without having to code a single line.

Just change the query argument to function. One will need a writable directory as a  utl_file_dir parameter from init.ora or pfile , or a stored directory object. Make sure you have read access to operating system directory you are supplying else it does not make much sense to run a program at all.

You can create a database object as well just copy and replace function definition with create or replace function.



--alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'; DECLARE cnt number ; function dump_csv( p_query in varchar2, p_separator in varchar2 default ',' , p_dir in varchar2 default '/non_edi_data', p_filename in varchar2 default 'temp1.csv' , qualify_with_quotes in boolean default false) return number is l_output utl_file.file_type; l_theCursor integer default dbms_sql.open_cursor; l_columnValue varchar2(2000); l_status integer; l_colCnt number default 0; l_separator varchar2(10) default ''; l_cnt number default 0; l_descTbl dbms_sql.desc_tab; begin DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT','''DD-MON-YYYY HH24:MI:SS'''); l_output := utl_file.fopen( p_dir, p_filename, 'w' , 32000 ); dbms_sql.parse( l_theCursor, p_query, dbms_sql.native ); dbms_sql.describe_columns( c => l_theCursor, col_cnt => l_colCnt, desc_t => l_descTbl ); for i in 1 .. 255 loop begin dbms_sql.define_column( l_theCursor, i, l_columnValue, 2000 ); l_colCnt := i; exception when others then if ( sqlcode = -1007 ) then exit; else raise; end if; end; end loop; dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 ); l_status := dbms_sql.execute(l_theCursor); loop exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 ); l_separator := ''; for i in 1 .. l_colCnt loop if l_cnt = 0 then l_columnValue:=l_descTbl(i).col_name; utl_file.put( l_output, l_separator || l_columnValue ); l_separator := p_separator; else dbms_sql.column_value( l_theCursor, i, l_columnValue ); --l_columnValue := replace(l_columnValue , p_separator , ' ') ; -- UnComment if seperator needs to be replaced. l_columnValue := replace(l_columnValue , '"' , '''') ; -- Comment if Text is not to be qualified with "" if ( instr(l_columnValue, p_separator) > 0) OR (l_descTbl(i).col_type in (1,8,96,112) and qualify_with_quotes ) then l_columnValue:= '"' || l_columnValue || '"'; end if; -- Comment if Text is not to be qualified with "" utl_file.put( l_output, l_separator || l_columnValue ); l_separator := p_separator; end if; end loop; utl_file.new_line( l_output ); l_cnt := l_cnt+1; end loop; dbms_sql.close_cursor(l_theCursor); utl_file.fclose( l_output ); return l_cnt; end dump_csv; BEGIN cnt:=dump_csv('select 1 from dual' ); END;