Oracle/SQL PLUS：如何假脱机日志并在整个脚本中间歇性地写入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34400319/
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/SQL PLUS: How to spool a log and write intermittently throughout script
提问by justin cress
Figuring out how to spool to a file has been easy enough. I am hoping there is an option to write to the text file after each command is written. I am not sure how to communicate the status of a long script to other people on my team. The solution we were going for was to write a log file to a network drive, as the script executes they would be able to follow along.
弄清楚如何假脱机到文件已经很容易了。我希望在每个命令写入后都有一个写入文本文件的选项。我不确定如何将长脚本的状态传达给我团队中的其他人。我们想要的解决方案是将日志文件写入网络驱动器，因为脚本执行时他们将能够跟进。
However, this seems to only write output to the file after the spool off; command at the end of the file.
但是，这似乎只是在假脱机关闭后将输出写入文件；文件末尾的命令。
Is there any way to achieve what we're trying to do, either with spooling a log file or another method?
有什么方法可以实现我们正在尝试做的事情，无论是通过后台处理日志文件还是其他方法？
Here is the code I have so far.
这是我到目前为止的代码。
set timing on;
set echo on;
column date_column new_value today_var
select to_char(current_timestamp, 'yyyymmdd_HH24_MI') as date_column
from dual
/
select current_timestamp from dual;
SPOOL 'Z:\log\KPI\secondary_reporting_&today_var..log'
... lots of stuff...
spool off;
采纳答案by fork2execve
Two methods come to mind, depending on what your 'stuff' is.
根据您的“东西”是什么，我想到了两种方法。
1) If your code has lots of SQL statements and PL/SQL blocks then you can repeatedly spool for a little while. Use the spool <filename> appendstatement for this.
1) 如果你的代码有很多 SQL 语句和 PL/SQL 块，那么你可以反复假脱机一段时间。spool <filename> append为此使用该语句。
SQL> help spool
SPOOL
-----
Stores query results in a file, or optionally sends the file to a printer.
In iSQL*Plus, use the Preferences screen to direct output to a file.
SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]
Not available in iSQL*Plus
2) If you have long running PL/SQL procedures use the UTL_FILEpackage. See https://docs.oracle.com/html/B14258_02/u_file.htmfor more information. This does require some setup and administrative privileges in the database to set up a directory where writing is allowed.
2) 如果您有长时间运行的 PL/SQL 过程，请使用该UTL_FILE包。有关更多信息，请参阅https://docs.oracle.com/html/B14258_02/u_file.htm。这确实需要数据库中的一些设置和管理权限来设置允许写入的目录。
回答by Mureinik
As far as I know there's no way to control when spooled output is written to a file. One way around this, though, could be to abandon spooling altogether and just redirect the output:
据我所知，无法控制何时将假脱机输出写入文件。但是，解决此问题的一种方法可能是完全放弃假脱机并仅重定向输出：
$ sqlplus @/path/to/script.sql >& /path/to/script.log
