Oracle 写入文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27562/
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 write to file
提问by
I am running oracle and have a query which pulls some results from the database. I would like to write the results as a text file. How would I go about doing this?
我正在运行 oracle 并且有一个从数据库中提取一些结果的查询。我想将结果写成文本文件。我该怎么做呢？
My prefered way would be by using UTL_FILE. Would some one have an example of how to do this?
我更喜欢的方法是使用 UTL_FILE。有人会举例说明如何做到这一点吗？
采纳答案by stjohnroe
If you are using PL/SQL then you can use the UTL_FILE package, the difference from using sql+ spool is that the files are written to the server file system. UTL_FILE has a number of limitations so an alternative on the server side would be to use Java stored procedures.
如果您使用 PL/SQL，那么您可以使用 UTL_FILE 包，与使用 sql+ spool 的区别在于文件被写入服务器文件系统。UTL_FILE 有许多限制，因此服务器端的替代方法是使用 Java 存储过程。
回答by Alotor
If you're using Sql Plus, is as easy as:
如果您使用的是 Sql Plus，则非常简单：
SQL> spool c:\temp\out.txt SQL> SELECT * FROM USERS; SQL> spool off
This three sentences will output the result of the query "SELECT * FROM USERS" to the file c:\temp\out.txt.
这三句话将查询“SELECT * FROM USERS”的结果输出到文件c:\temp\out.txt。
You can format this query using the string manipulation functions of Oracle.
您可以使用 Oracle 的字符串操作函数来格式化此查询。
回答by Andrew Wood
Use UTL_FILE in combination with CREATE DIRECTORY for ease of mapping a directory path with a name (it does not create the actual directory just a reference to it so ensure it is created first)
将 UTL_FILE 与 CREATE DIRECTORY 结合使用，以便于映射具有名称的目录路径（它不会创建实际目录，只是对其的引用，因此请确保首先创建它）
example
例子
create directory logfile as 'd:\logfile'; -- must have priv to do this
declare
vFile utl_file.file_type;
begin
vFile := utl_file.fopen(logfile ,'syslog','w'); -- w is write. This returns file handle
utl_file.put(vFile,'Start Logfile'); -- note use of file handle vFile
utl_file.fclose(vFile); -- note use of file handle vFile
end;
回答by diciu
If you're running the query from sqlplusyou can use the spool command:
如果您从sqlplus运行查询，则可以使用 spool 命令：
spool /tmp/test.spool
After executing the spool command within a session, all output is sent to the sqlplus console as well as the /tmp/test.spool text file.
在会话中执行 spool 命令后，所有输出都会发送到 sqlplus 控制台以及 /tmp/test.spool 文本文件。
回答by stjohnroe
This seems to be a reasonable tutorial with a few simple examples UTL_FILE example
这似乎是一个合理的教程，有几个简单的例子UTL_FILE 例子
