将数据从 Oracle 表传输到文本文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9850666/
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
Transfer data from Oracle table to text file
提问by user1285928
I'm interested is it possible with PL/SQL block to transfer the content of a Oracle table into text file on the Hard Drive. I need a PL/SQL block which can download the content of a table witch will be used to store log data into text file.
我感兴趣的是是否可以使用 PL/SQL 块将 Oracle 表的内容传输到硬盘驱动器上的文本文件中。我需要一个可以下载表内容的 PL/SQL 块，它将用于将日志数据存储到文本文件中。
Regards
问候
回答by pratik garg
you can use UTL_file package for this..
您可以为此使用 UTL_file 包。
you can try below type of block --
您可以尝试以下类型的块-
declare
p_file util_file.file_type;
l_table <your_table_name>.ROWTYPE;
l_delimited varchar2(1) := '|';
begin
p_file:= utl_file.fopen('<file_path>','<file_name>','W');
for l_table in (select * from <your_table_name>) loop
utl_file.putline(p_file,l_table.col1||l_delimited||l_table.col2||l_delimited||l_table.col3||l_delimited||l_table.col4||l_delimited <continue with column list .........> ||chr(10));
end loop;
utl_file.fclose_all();
end;
回答by A.B.Cade
pratik garg's answer is a good one.
But, you might want to consider also the use of an EXTERNAL TABLE.
Basically, it's a table which is mapped to a file. So every row inserted to the table is automatically written to a file.
you can see an example here
pratik Garg 的回答很好。
但是，您可能还想考虑使用EXTERNAL TABLE。
基本上，它是一个映射到文件的表。所以插入到表中的每一行都会自动写入一个文件。
你可以在这里看到一个例子
