有没有办法在 Oracle 中刷新 PL/SQL 的输出？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1472587/
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
Is there any way to flush output from PL/SQL in Oracle?
提问by baxter
I have an SQL script that is called from within a shell script and takes a long time to run. It currently contains dbms_output.put_linestatements at various points. The output from these print statements appear in the log files, but only once the script has completed.
我有一个从 shell 脚本中调用的 SQL 脚本，需要很长时间才能运行。它目前包含dbms_output.put_line不同点的声明。这些打印语句的输出出现在日志文件中，但仅在脚本完成后才会出现。
Is there any way to ensure that the output appears in the log file as the script is running?
有什么方法可以确保在脚本运行时输出出现在日志文件中？
回答by Dave Costa
Not really. The way DBMS_OUTPUT works is this: Your PL/SQL block executes on the database server with no interaction with the client. So when you call PUT_LINE, it is just putting that text into a buffer in memory on the server. When your PL/SQL block completes, control is returned to the client (I'm assuming SQLPlus in this case); at that point the client gets the text out of the buffer by calling GET_LINE, and displays it.
并不真地。DBMS_OUTPUT 的工作方式是：您的 PL/SQL 块在数据库服务器上执行，不与客户端交互。因此，当您调用 PUT_LINE 时，它只是将该文本放入服务器内存中的缓冲区中。当您的 PL/SQL 块完成时，控制权返回给客户端（在这种情况下我假设是 SQLPlus）；此时，客户端通过调用 GET_LINE 从缓冲区中获取文本，并显示它。
So the only way you can make the output appear in the log file more frequently is to break up a large PL/SQL block into multiple smaller blocks, so control is returned to the client more often. This may not be practical depending on what your code is doing.
因此，使输出更频繁地出现在日志文件中的唯一方法是将一个大的 PL/SQL 块分解为多个较小的块，以便更频繁地将控制权返回给客户端。这可能不切实际，具体取决于您的代码在做什么。
Other alternatives are to use UTL_FILE to write to a text file, which can be flushed whenever you like, or use an autonomous-transaction procedure to insert debug statements into a database table and commit after each one.
其他替代方法是使用 UTL_FILE 写入文本文件，您可以随时刷新该文件，或者使用自主事务过程将调试语句插入数据库表并在每个语句之后提交。
回答by J. Chomel
If it is possible to you, you should replace the calls to dbms_output.put_line by your own function.
如果可能，您应该用您自己的函数替换对 dbms_output.put_line 的调用。
Here is the code for this function WRITE_LOG-- if you want to have the ability to choose between 2 logging solutions:
这是此功能的代码WRITE_LOG——如果您想在两种日志记录解决方案之间进行选择：
write logs to a table in an autonomous transaction
将日志写入自治事务中的表
CREATE OR REPLACE PROCEDURE to_dbg_table(p_log varchar2)
-- table mode:
-- requires
-- CREATE TABLE dbg (u varchar2(200) --- username
-- , d timestamp --- date
-- , l varchar2(4000) --- log
-- );
AS
pragma autonomous_transaction;
BEGIN
insert into dbg(u, d, l) values (user, sysdate, p_log);
commit;
END to_dbg_table;
/
or write directly to the DB server that hosts your database
或直接写入托管数据库的数据库服务器
This uses the Oracle directoryTMP_DIR
这将使用Oracle 目录TMP_DIR
CREATE OR REPLACE PROCEDURE to_dbg_file(p_fname varchar2, p_log varchar2)
-- file mode:
-- requires
--- CREATE OR REPLACE DIRECTORY TMP_DIR as '/directory/where/oracle/can/write/on/DB_server/';
AS
l_file utl_file.file_type;
BEGIN
l_file := utl_file.fopen('TMP_DIR', p_fname, 'A');
utl_file.put_line(l_file, p_log);
utl_file.fflush(l_file);
utl_file.fclose(l_file);
END to_dbg_file;
/
WRITE_LOG
WRITE_LOG
Then the WRITE_LOGprocedure which can switch between the 2 uses, or be deactivated to avoid performances loss (g_DEBUG:=FALSE).
然后WRITE_LOG可以在 2 之间切换的程序使用，或停用以避免性能损失（g_DEBUG:=FALSE）。
CREATE OR REPLACE PROCEDURE write_log(p_log varchar2) AS
-- g_DEBUG can be set as a package variable defaulted to FALSE
-- then change it when debugging is required
g_DEBUG boolean := true;
-- the log file name can be set with several methods...
g_logfname varchar2(32767) := 'my_output.log';
-- choose between 2 logging solutions:
-- file mode:
g_TYPE varchar2(7):= 'file';
-- table mode:
--g_TYPE varchar2(7):= 'table';
-----------------------------------------------------------------
BEGIN
if g_DEBUG then
if g_TYPE='file' then
to_dbg_file(g_logfname, p_log);
elsif g_TYPE='table' then
to_dbg_table(p_log);
end if;
end if;
END write_log;
/
And here is how to test the above:
以下是如何测试上述内容：
1) Launch this (file mode) from your SQLPLUS:
1）从你的 SQLPLUS启动这个（文件模式）：
BEGIN
write_log('this is a test');
for i in 1..100 loop
DBMS_LOCK.sleep(1);
write_log('iter=' || i);
end loop;
write_log('test complete');
END;
/
2) on the database server, open a shell and
2）在数据库服务器上，打开一个shell并
tail -f -n500 /directory/where/oracle/can/write/on/DB_server/my_output.log
回答by tuinstoel
Two alternatives:
两种选择：
You can insert your logging details in a logging table by using an autonomous transaction. You can query this logging table in another SQLPLUS/Toad/sql developer etc... session. You have to use an autonomous transaction to make it possible to commit your logging without interfering the transaction handling in your main sql script.
Another 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.
您可以使用自主事务将日志详细信息插入到日志表中。您可以在另一个 SQLPLUS/Toad/sql 开发人员等...会话中查询此日志记录表。您必须使用自治事务才能提交日志记录，而不会干扰主 sql 脚本中的事务处理。
另一种选择是使用返回日志信息的流水线函数。请参见此处的示例：http: //berxblog.blogspot.com/2009/01/pipelined-function-vs-dbmsoutput.html当您使用流水线函数时，您不必使用其他 SQLPLUS/Toad/sql 开发人员等... 会议。
回答by Vincent Malgrat
the buffer of DBMS_OUTPUTis read when the procedure DBMS_OUTPUT.get_lineis called. If your client application is SQL*Plus, it means it will only get flushed once the procedure finishes.
调用DBMS_OUTPUT该过程时读取的缓冲区DBMS_OUTPUT.get_line。如果您的客户端应用程序是 SQL*Plus，这意味着它只会在过程完成后刷新。
You can apply the method described in this SOto write the DBMS_OUTPUTbuffer to a file.
您可以应用此 SO 中描述的方法将DBMS_OUTPUT缓冲区写入文件。
回答by user2196257
Set session metadata MODULE and/or ACTION using dbms_application_info().
Monitor with OEM, for example:
使用 设置会话元数据 MODULE 和/或 ACTION dbms_application_info()。
使用 OEM 进行监控，例如：
Module: ArchiveData
Action: xxx of xxxx
回答by olekb
If you have access to system shell from PL/SQL environment you can call netcat:
如果您可以从 PL/SQL 环境访问系统 shell，则可以调用 netcat：
BEGIN RUN_SHELL('echo "'||p_msg||'" | nc '||p_host||' '||p_port||' -w 5'); END;
p_msg- is a log message
v_hostis a host running python script that reads data from socket on port v_port.
p_msg- 是一条日志消息，
v_host是一个运行 python 脚本的主机，它从端口上的套接字读取数据v_port。
I used this design when I wrote aplogrfor real-time shell and pl/sql logs monitoring.
我在写aplogr进行实时shell 和pl/sql 日志监控时使用了这种设计。
