有没有办法在 Oracle 中刷新 PL/SQL 的输出? [英] Is there any way to flush output from PL/SQL in Oracle?

问题描述

我有一个从 shell 脚本中调用的 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屋！