有什么方法可以从Oracle中的PL/SQL刷新输出? [英] Is there any way to flush output from PL/SQL in Oracle?

问题描述

我有一个从外壳程序脚本中调用的SQL脚本，并且运行时间很长.当前，它在各个点上包含dbms_output.put_line语句.这些打印语句的输出将显示在日志文件中，但仅在脚本完成后才会显示.

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_line statements at various points. The output from these print statements appear in the log files, but only once the script has completed.

有什么方法可以确保在脚本运行时输出显示在日志文件中吗?

Is there any way to ensure that the output appears in the log file as the script is running?

推荐答案

不是. DBMS_OUTPUT的工作方式是这样的:您的PL/SQL块在数据库服务器上执行，而没有与客户端的交互.因此，当您调用PUT_LINE时，它只是将文本放入服务器内存中的缓冲区中.当您的PL/SQL块完成时，控制权将返回给客户端(在这种情况下，我假设使用SQLPlus)；此时，客户端通过调用GET_LINE将文本从缓冲区中取出并显示.

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.

因此，使输出更频繁地出现在日志文件中的唯一方法是将一个较大的PL/SQL块分成多个较小的块，从而使控制更频繁地返回给客户端.这可能不切实际，这取决于您的代码在做什么.

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.

其他选择是使用UTL_FILE写入文本文件，该文件可以随时刷新，也可以使用自主事务过程将调试语句插入数据库表并在每个表之后提交.

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.

这篇关于有什么方法可以从Oracle中的PL/SQL刷新输出?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！