oracle导出大批量数据为csv或txt文件

昨天开发人员要求根据其提供的sql语句，将查询到的3百多万条数据导出为excel文件，以提供给相关人员进行线下统计分析。

我首先想到的是通过plsql工具的“导出查询结果”–>"csv文件"这种方法。使用这种方式导出几千上万条数据还行，对于导出几百万条数据就比较吃力了，如果网络稳定还好，如果网络不稳定导致连接中断，所有努力就白费了。

因此我采用了oracle自带的UTL_FILE包来实现将大批量数据导出为csv。

一、准备工作

在操作UTL_FILE工具包之前，需要先创建或使用一个已经存在的目录(directory)。如果是以普通用户来操作，还需要给普通用户赋予相应的权限。

--如果是普通用户，则需要赋予权限：
grant execute on utl_file to cqiwen;

--查询和创建目录
select * from dba_directories
create directory exp_dir as '/recover';
grant read,write on directory exp_dir to cqiwen;

二、导出大批量数据为txt文件

--通过UTL_FILE包导出数据至txt文件中
set serveroutput on
declare
  VSFILE UTL_FILE.FILE_TYPE; --定义用于接收文件句柄的类型
  V_CNT NUMBER; --统计每个文件加载行数或作为序号使用
begin
    VSFILE:=UTL_FILE.FOPEN('EXP_DIR','test3.txt','w');
    V_CNT := 1;
    -- UTL_FILE.PUT_LINE(VSFILE,'序号, 流水id, 学校名称, 付款方式,...');  --可以为要导出的文件指定标题栏,最好与后面导出的列相对应
    for x in (select * from cqiwen.tmp_20211220_1)
      loop
        UTL_FILE.PUT_LINE(VSFILE,V_CNT||','||x.payorderid||','||x.cname||','||x.pname||','||x.paytype||','||x.tcost||','||x.odsrc||','||x.devcode);
        --以上是将要导出的字段列出来，并写到指定好的文件中
        V_CNT := V_CNT + 1;
      end loop;
      DBMS_OUTPUT.PUT_LINE('Finished! ALL LOAD ROWS：' || V_CNT);
      UTL_FILE.FFLUSH(VSFILE);
      UTL_FILE.FCLOSE(VSFILE);
      EXCEPTION WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,2000));
end;
/

三、对导出的文件进行拆分

--对导出的文件进行拆分，以方便后续保存为csv时能够不超过最大行数上限
--参数最后一位指定为null时表示截止最后一行
BEGIN
utl_file.fcopy('EXP_DIR', 'test3.txt', 'EXP_DIR', 'sp_test1.txt',1,1024000);
END;
/

BEGIN
utl_file.fcopy('EXP_DIR', 'test3.txt', 'EXP_DIR', 'sp_test2.txt',1024001,null);
END;
/

四、转储为csv文件，然后即可通过excel工具打开

此时得到的txt文件并不能通过直接修改为csv后缀而转换为excel表格，因为utl_file包生成的文件是unix-dos格式的，在excel中并不能正确识别中文。

因此，先在UltraEdit中打开txt文件，然后UE中复制全文并粘贴到一个新文件中，将新文件另存为XX.csv即可。

五、当导出的列中有clob大字段时进行切割

DECLARE
  clob_data   clob;
  content_txt varchar2(4000) := '';
  data_len    number := 0;
  offset_1    number := 1;
  dest_dir    VARCHAR2(256) := 'EXP_DIR';   --需先在oracle中创建此目录并授予用户读写权限
  dest_file   VARCHAR2(256) := 'GET_PRO_DDL.txt';
  dest_handle UTL_FILE.file_type;
  cursor cur1 is
    select rownum rnm, a.*
      from (select owner,
                   object_name,
                   object_type,
                   dbms_metadata.get_ddl(object_type => OBJECT_TYPE,
                                         name        => object_name,
                                         schema      => owner) ddl
              from dba_objects
             where owner not in ('SYS',
                                 'SYSTEM',
                                 'PUBLIC',
                                 )
               AND OBJECT_TYPE in ('PROCEDURE', 'PACKAGE', 'FUNCTION')
               AND OWNER NOT IN
                   (SELECT USERNAME
                      FROM DBA_USERS
                     WHERE ACCOUNT_STATUS <> 'OPEN')
             ORDER BY owner, object_type, object_name) a;
  v_c cur1%rowtype;
BEGIN
  dbms_output.enable(1000000);
  dest_file := 'GET_PRO_DDL_' || to_char(sysdate, 'yyyymmdd') || '.txt';
  open cur1;
  loop
    fetch cur1
      into v_c;
    exit when cur1%notfound;
    clob_data := v_c.rnm || ',' || v_c.owner || ',' || v_c.object_name || ',' ||v_c.ddl || CHR(13);
    select length(clob_data) into data_len from dual;
    offset_1 := 1;
    loop
      exit when data_len <= 0;
      content_txt := substr(clob_data, offset_1, 3000);  --按3000个字符进行切割
      dbms_output.put_line(v_c.rnm || ',' || v_c.object_name || ',' ||data_len);  --输出切割列表
      dest_handle := UTL_FILE.fopen(dest_dir, dest_file, 'a', 32767);
      UTL_FILE.put_line(dest_handle, content_txt, true);
      utl_file.fflush(dest_handle);
      UTL_FILE.fclose(dest_handle);
      --dbms_output.put_line(content_txt);
      data_len := data_len - 3000;
      offset_1 := offset_1 + 3000;
    end loop;
  end loop;
  close cur1;
  IF UTL_FILE.is_open(dest_handle) THEN
    UTL_FILE.fclose(dest_handle);
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    RAISE;
END;

六、补充

在本文发出后，热心的网友反馈还可以使用第三方工具sqluldr2来实现大量数据导出为csv文件。为了使本文更全面，因此我也补充一下关于sqluldr2的介绍。

1）这个软件声明中写的是对非商业用途免费，商业用途收费，类似于oracle软件。
License: Free for non-commercial useage, else 100 USD per server.
2）针对含中文的大量数据导出为csv文件的常用命令：

./sqluldr2 user=cqiwen/cqiwen sql=./query.sql head=yes charset=ZHS16GBK batch=yes file=./test_%B.csv 

3. 这个工具已经很早就没有人维护和更新了，由于代码不开源，其中的bug也就无法继续修复了。目前已知的问题有：

• 3.1.当表中有除英文和中文外的其它国家文字时，导出必定会有乱码；
• 3.2.当表中有clob列时，如果该列储存的文本超过4000字符，则导出时会报错。且无法在导出时进行切割，除非在原表中先切割为多列再导出；
• 3.3.当导出的列中有date类型或number类型时，可能导出为csv文件后会出现问题，包括但不限于数字精度变化等。