Using UTL_FILE package to dump data to a text file

--Using UTL_FILE package to dump data to a text file
--First, you should create a dictionary and grant write privilege on this dir to the user you wanted.
--Then you can call this procedure like this: dump2text('select * from all_users','','utldir','test.txt')
--Good luck!
create or replace procedure dump2text(sp_query in varchar2,
sp_separator in varchar2 default '',
sp_dir in varchar2,
sp_filename in varchar2) authid current_user is
ol_filehandle utl_file.file_type;
cl_cursor integer default dbms_sql.open_cursor;
sl_coltext varchar2(2000);
nl_status integer;
nl_colcnt number default 0;
sl_separator varchar2(10) default '';
nl_cnt number default 0;
begin
ol_filehandle := utl_file.fopen(sp_dir, sp_filename, 'w');
dbms_sql.parse(cl_cursor, sp_query, dbms_sql.native);
for i in 1 .. 255 loop
begin
dbms_sql.define_column(cl_cursor, i, sl_coltext, 2000);
nl_colcnt := i;
exception
when others then
if (sqlcode = -1007) then
exit;
else
raise;
end if;
end;
end loop;
dbms_sql.define_column(cl_cursor, 1, sl_coltext, 2000);
nl_status := dbms_sql.execute(cl_cursor);
loop
exit when(dbms_sql.fetch_rows(cl_cursor) <= 0);
sl_separator := '';
for i in 1 .. nl_colcnt loop
dbms_sql.column_value(cl_cursor, i, sl_coltext);
utl_file.put(ol_filehandle, sl_separator sl_coltext);
if i < 2 then
sl_separator := sp_separator;
end if;
end loop;
utl_file.new_line(ol_filehandle);
nl_cnt := nl_cnt + 1;
end loop;
dbms_sql.close_cursor(cl_cursor);
utl_file.fclose(ol_filehandle);
dbms_output.put_line('total export rows: ' nl_cnt);
exception
when no_data_found then
return;
when utl_file.invalid_path then
dbms_output.put_line('invalid path');
when utl_file.invalid_filehandle then
dbms_output.put_line('invalid filehandle');
when utl_file.invalid_operation then
dbms_output.put_line('invalid operation');
when others then
dbms_output.put_line('error number: ' sqlcode
'\n error message: ' sqlerrm);
end dump2text;