Oracle -- How to export a table to a plane file by UTL_FILE

--author; Zhou xiangzhen

--created: 2008-10-25

--function: export table to a plane file

--you should call this procedure like this: dump2text('select * from all_users','|','EXPORTTABLEDIR','test.txt')
--

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 ||
                         ' error message: ' || sqlerrm);
end dump2text;
/

--
Thanks
Xiangzhen