oracle dump_csv

1、首先提供函数dump_csv:

create or replace function dump_csv(p_query     in varchar2,
                                    p_separator in varchar2 default ',',
                                    p_dir       in varchar2,
                                    p_filename  in varchar2) return number
  AUTHID CURRENT_USER 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;
 begin
  l_output := utl_file.fopen(p_dir, p_filename, 'w');
  dbms_sql.parse(l_theCursor, p_query, dbms_sql.native);
  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
      dbms_sql.column_value(l_theCursor, i, l_columnValue);
      utl_file.put(l_output, l_separator || '"' || l_columnValue || '"');
      l_separator := p_separator;
    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;

2、创建Directory并授权：

conn sys/password as sysdba;

create directory TEMP as 'c:\temp';

grant read,write on directory TEMP to tt;

3、使用：

conn tt/password;

select dump_csv('select * from scott.emp',',','TEMP','emp.csv') from dual;