Oracle PL/SQL - 即时输出/控制台打印提示
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1729739/
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 PL/SQL - tips for immediate output / console printing
提问by FrustratedWithFormsDesigner
I have a number of pl/sql procedures that can take several minutes to run. While developing them, I've added a few print statements to help debug and also provide some feedback and progress indicators. Initially, I ran these on small test sets and output was almost instantaneous. Now that I'm testing with larger test sets that take several minutes to run, I find that printing to the console is no longer suitable, because nothing gets printed until the procedure ends. I'm used to working in environments that do not buffer their output and print it immediately and adding simple print-statements for simple debugging and diagnostic is common.
我有许多可能需要几分钟才能运行的 pl/sql 过程。在开发它们时，我添加了一些打印语句来帮助调试并提供一些反馈和进度指示器。最初，我在小型测试集上运行这些，输出几乎是即时的。现在我正在测试需要几分钟才能运行的更大的测试集，我发现打印到控制台不再合适，因为在过程结束之前不会打印任何内容。我习惯于在不缓冲输出并立即打印的环境中工作，并且为简单的调试和诊断添加简单的打印语句是很常见的。
Is it possible in pl/sql to print output immediately (not buffered)? If not, what alternatives do people recommend to get a similar result?
是否可以在 pl/sql 中立即打印输出（未缓冲）？如果不是，人们推荐哪些替代方案来获得类似的结果？
采纳答案by Tony Andrews
You can have a procedure that writes messages to a table using an autonomous transaction something like:
您可以有一个使用自治事务将消息写入表的过程，例如：
procedure log (p_message)
is
pragma autonomous_transaction;
begin
insert into message_log (user, datetime, message)
values (user, sysdate, p_message);
commit;
end;
Then monitor the table from another Oracle session.
然后从另一个 Oracle 会话监视该表。
回答by Henry Gao
we have a small trick for this.
我们有一个小技巧。
you can use DBMS_APPLICATION_INFO.set_client_info(" some information here"); creating some variables and replace the string inside " ".
你可以使用 DBMS_APPLICATION_INFO.set_client_info("这里有一些信息"); 创建一些变量并替换“”中的字符串。
and use select client_info from v$session to monitor the progress.
并使用 select client_info from v$session 来监控进度。
回答by Rene
I've been using dbms_pipe for this purpose. Send messages to a named pipe and read them from another session. This method may not work in a RAC environment when the writing and reading processes may connect to a different node.
为此，我一直在使用 dbms_pipe。将消息发送到命名管道并从另一个会话中读取它们。当写入和读取过程可能连接到不同的节点时，此方法在 RAC 环境中可能不起作用。
Alternatively you can insert messages into a table using a procedure that runs in its own session using "pragma autonomous_transaction". You can the query these messages from another session
或者，您可以使用在其自己的会话中运行的过程将消息插入表中，使用“pragma allowed_transaction”。您可以从另一个会话中查询这些消息
Edit: I see that my second option has already been mentioned.
编辑：我看到已经提到了我的第二个选项。
回答by Robert Giesecke
You can use DBMS Pipe and the Pipe Viewer in PL/SQL Developer to asynchronously catch all infos as they are put into the pipe.
您可以使用 DBMS 管道和 PL/SQL Developer 中的管道查看器异步捕获所有放入管道的信息。
Be careful to only put things into a pipe when there's someone to read it. Otherwise, your call will fail when the pipe is full.
小心只有当有人阅读时才将东西放入管道中。否则，当管道已满时，您的调用将失败。
There's also the possibility of using events, PL/SQL Developer has an event monitor as well. And the docs should provide an example of how to do it.
还有使用事件的可能性，PL/SQL Developer 也有一个事件监视器。并且文档应该提供一个如何做的例子。
回答by dpbradley
There are generally two options:
一般有两种选择：
- Send the output to an Oracle table (or temporary table)
- Write to the (database host) filesystem with UTL_FILE
- 将输出发送到 Oracle 表（或临时表）
- 使用 UTL_FILE 写入（数据库主机）文件系统
If you don't have OS access to the database host, you can still write to the dbhost filesystem and bind an Oracle externally-defined table to the file so it can be queried with a SELECT.
如果您没有对数据库主机的操作系统访问权限，您仍然可以写入 dbhost 文件系统并将 Oracle 外部定义的表绑定到该文件，以便可以使用 SELECT 进行查询。
回答by Dan
It may depend on your client tool. I haven't used SQL*Plus in a while, but when I'm debugging procedures in PL/SQL Developer, I open a command window and issue a SET SERVEROUTPUT ONcommand. Then when I execute the procedure, anything printed by DBMS_OUTPUT.PUT_LINEshows up right away.
这可能取决于您的客户端工具。我有一段时间没有使用 SQL*Plus，但是当我在 PL/SQL Developer 中调试过程时，我打开一个命令窗口并发出一个SET SERVEROUTPUT ON命令。然后当我执行该过程时，任何打印的东西都会立即DBMS_OUTPUT.PUT_LINE显示出来。
Edit: you're right, I guess I was only seeing that with larger amounts of output or something. Anyhow I did some searching online and came across this log4plsql- may be useful.
编辑：你是对的，我想我只是看到大量输出或其他东西。无论如何，我在网上进行了一些搜索并遇到了这个log4plsql- 可能有用。
回答by tuinstoel
An alternative is to use a pipelined function that returns your logging information. See here for an example: http://berxblog.blogspot.com/2009/01/pipelined-function-vs-dbmsoutput.htmlWhen you use a pipelined function you don't have to use another SQLPLUS/Toad/sql developer etc... session.
另一种方法是使用返回日志信息的流水线函数。请参见此处的示例：http: //berxblog.blogspot.com/2009/01/pipelined-function-vs-dbmsoutput.html当您使用流水线函数时，您不必使用其他 SQLPLUS/Toad/sql 开发人员等... 会议。
回答by Leigh Riffel
Another option is to have your PL/SQL call a procedure to send an email with the log message in it. This requires that your database has email sending capability, which can be added using UTL_SMTP.
另一种选择是让您的 PL/SQL 调用一个过程来发送包含日志消息的电子邮件。这要求您的数据库具有电子邮件发送功能，可以使用 UTL_SMTP 添加。
