create or replace procedure createCSVFile(p_filename varchar2 , p_sql varchar2 , p_headerline varchar2 , p_printTable_header boolean default false , p_printHeader_line boolean default true , p_print_utf8 boolean default true) is v_cur SYS_REFCURSOR; v_cur_pointer pls_integer; v_column_count pls_integer; v_column_desc SYS.DBMS_SQL.desc_tab; v_column_value varchar2(4000); v_file utl_file.file_type; v_rec_count pls_integer:=0; v_line varchar2(4000); v_fetch pls_integer:=0; v_data_format varchar2(18):='dd.mm.yyyy hh24:mi'; begin -- open file if p_print_utf8 then v_file := UTL_FILE.FOPEN_NCHAR ('EXPORT_DIR',p_filename,'w',4000); else v_file := UTL_FILE.FOPEN ('EXPORT_DIR',p_filename,'w',4000); end if; --write header line if p_printHeader_line then if p_print_utf8 then utl_file.PUT_line_NCHAR(v_file,p_headerline); else utl_file.PUT_line(v_file,p_headerline); end if; end if; -- set the required data format EXECUTE IMMEDIATE 'alter session set nls_date_format='''||v_data_format||''''; -- bild the sql dbms_output.put_line ('-- Info:: try to execute '||p_sql); -- not in 10g -- open the cursor -- open v_cur for v_sql; -- get pointer to the cursor -- v_cur_pointer := sys.dbms_sql.to_cursor_number (v_cur); v_cur_pointer := DBMS_SQL.OPEN_CURSOR; sys.dbms_sql.parse(v_cur_pointer, p_sql, sys.dbms_sql.native); --get the columnns of this cursor sys.dbms_sql.describe_columns (c => v_cur_pointer, col_cnt => v_column_count, desc_t => v_column_desc); for i in 1 .. v_column_count loop v_line := v_line || v_column_desc (i).col_name; if i < v_column_count then v_line := v_line || '|'; end if; dbms_sql.define_column (v_cur_pointer, i, v_column_value,4000); end loop; if p_printTable_header then dbms_output.put_line ('-- Info:: Header Line '||v_line); if p_print_utf8 then utl_file.PUT_line_NCHAR(v_file,v_line); else utl_file.PUT_line(v_file,v_line); end if; end if; v_fetch:=DBMS_SQL.EXECUTE(v_cur_pointer); while (sys.dbms_sql.fetch_rows (v_cur_pointer) > 0) loop v_rec_count := v_rec_count + 1; v_line := ''; for i in 1 .. v_column_count loop dbms_sql.column_value (v_cur_pointer, i, v_column_value); v_line := v_line || v_column_value; if i < v_column_count then v_line := v_line || '|'; end if; end loop; --dbms_output.put_line ('--info::'||v_line); if p_print_utf8 then utl_file.put_line_nchar(v_file,v_line); else utl_file.put_line(v_file,v_line); end if; end loop; if v_rec_count = 0 then dbms_output.put_line ('no data found.'); else dbms_output.put_line (v_rec_count || ' rows returned.'); end if; -- close the cursor if sys.dbms_sql.is_open(v_cur_pointer) then sys.dbms_sql.close_cursor (v_cur_pointer); end if; --close the file if UTL_FILE.IS_OPEN (v_file) then utl_file.fflush(v_file); utl_file.fclose(v_file); end if; exception when others then --close the file if UTL_FILE.IS_OPEN (v_file) then utl_file.fflush(v_file); utl_file.fclose(v_file); end if; -- close the cursor if sys.dbms_sql.is_open(v_cur_pointer) then sys.dbms_sql.close_cursor (v_cur_pointer); end if; -- Error dbms_output.put_line ('-- Error ::'||SQLERRM); end createcsvfile;