Oracle 如何将查询导出到文本/csv 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/857744/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle how to export query to a text/csv file
提问by Rich
I was wondering how to go about exporting a query from PL/SQL to an text file or csv file. The query I have in mind exports a huge amount of data (about 1 gig). So I'd also like the data split across multiple files;
我想知道如何将查询从 PL/SQL 导出到文本文件或 csv 文件。我想到的查询导出了大量数据（大约 1 gig）。所以我也希望将数据拆分到多个文件中；
out1.csv out2.csv out3.csv
out1.csv out2.csv out3.csv
I'd like to be able to decide how many files to split it across.
我希望能够决定将它拆分多少个文件。
Anyone have any idea how to do this?
任何人都知道如何做到这一点？
回答by Matthew Watson
Use UTL_FILE.
使用UTL_FILE。
A well known ( probably the most complete discussion on this topic ) discussion on this can be found at Ask Tom, Here, note that many of the examples there date back to oracle 8, so there may be better ways to do it in your version of Oracle.
在一个众所周知的（可能是关于这一主题的最完整的讨论）的讨论，可以发现问汤姆，在这里，需要注意的是很多的例子，可以追溯到甲骨文8，所以有可能是更好的方法来做到这一点在你的版本甲骨文。
回答by wai
Try this
尝试这个
For creating MYDIR
用于创建 MYDIR
create or replace directory MYDIR as 'F:/DATA/';
Grant all permission to MYDIRvia SYSuser execute this procedure
通过SYS用户向MYDIR授予所有权限执行此过程
CREATE OR REPLACE PROCEDURE export_to_csv(refcur out sys_refcursor) IS
v_file UTL_FILE.file_type;
v_string VARCHAR2(4000);
CURSOR c_emp IS
SELECT ROLE_ID, ROLE_DESC FROM role_mst;
BEGIN
open refcur for
SELECT ROLE_ID, ROLE_DESC FROM role_mst;
v_file := UTL_FILE.fopen('MYDIR', 'empdata.csv', 'w', 1000);
-- if you do not want heading then remove below two lines
v_string := 'Emp Code, Emp Name';
UTL_FILE.put_line(v_file, v_string);
FOR cur IN c_emp LOOP
v_string := cur.ROLE_ID || ',' || cur.ROLE_DESC;
UTL_FILE.put_line(v_file, v_string);
END LOOP;
UTL_FILE.fclose(v_file);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
IF UTL_FILE.is_open(v_file) THEN
UTL_FILE.fclose(v_file);
END IF;
END;
