oracle 如何将 DBMS_OUTPUT.PUT_LINE 的输出重定向到文件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1453538/
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
How to redirect the output of DBMS_OUTPUT.PUT_LINE to a file?
提问by
I need to debug in pl/sql to figure times of procedures, I want to use:
我需要在 pl/sql 中调试以计算程序的时间，我想使用：
SELECT systimestamp FROM dual INTO time_db;
DBMS_OUTPUT.PUT_LINE('time before procedure ' || time_db);
but I don't understand where the output goes to and how can I redirect it to a log file that will contain all the data I want to collect?
但我不明白输出的去向以及如何将其重定向到包含我想要收集的所有数据的日志文件？
回答by Vincent Malgrat
DBMS_OUTPUTis not the best tool to debug, since most environments don't use it natively. If you want to capture the output of DBMS_OUTPUThowever, you would simply use the DBMS_OUTPUT.get_lineprocedure.
DBMS_OUTPUT不是最好的调试工具，因为大多数环境本身并不使用它。DBMS_OUTPUT但是，如果要捕获输出，则只需使用该DBMS_OUTPUT.get_line过程即可。
Here is a small example:
这是一个小例子：
SQL> create directory tmp as '/tmp/';
Directory created
SQL> CREATE OR REPLACE PROCEDURE write_log AS
2 l_line VARCHAR2(255);
3 l_done NUMBER;
4 l_file utl_file.file_type;
5 BEGIN
6 l_file := utl_file.fopen('TMP', 'foo.log', 'A');
7 LOOP
8 EXIT WHEN l_done = 1;
9 dbms_output.get_line(l_line, l_done);
10 utl_file.put_line(l_file, l_line);
11 END LOOP;
12 utl_file.fflush(l_file);
13 utl_file.fclose(l_file);
14 END write_log;
15 /
Procedure created
SQL> BEGIN
2 dbms_output.enable(100000);
3 -- write something to DBMS_OUTPUT
4 dbms_output.put_line('this is a test');
5 -- write the content of the buffer to a file
6 write_log;
7 END;
8 /
PL/SQL procedure successfully completed
SQL> host cat /tmp/foo.log
this is a test
回答by Tony Andrews
As an alternative to writing to a file, how about writing to a table? Instead of calling DBMS_OUTPUT.PUT_LINE you could call your own DEBUG.OUTPUT procedure something like:
作为写入文件的替代方法，写入表怎么样？您可以调用自己的 DEBUG.OUTPUT 过程，而不是调用 DBMS_OUTPUT.PUT_LINE，例如：
procedure output (p_text varchar2) is
pragma autonomous_transaction;
begin
if g_debugging then
insert into debug_messages (username, datetime, text)
values (user, sysdate, p_text);
commit;
end if;
end;
The use of an autonomous transaction allows you to retain debug messages produced from transactions that get rolled back (e.g. after an exception is raised), as would happen if you were using a file.
自治事务的使用允许您保留从回滚的事务（例如，在引发异常之后）产生的调试消息，就像使用文件时会发生的那样。
The g_debugging boolean variable is a package variable that can be defaulted to false and set to true when debug output is required.
g_debugging 布尔变量是一个包变量，当需要调试输出时，它可以默认为 false 并设置为 true。
Of course, you need to manage that table so that it doesn't grow forever! One way would be a job that runs nightly/weekly and deletes any debug messages that are "old".
当然，您需要管理该表，以免它永远增长！一种方法是每晚/每周运行并删除任何“旧”调试消息的作业。
回答by Tony Andrews
If you are just testing your PL/SQL in SQL Plus you can direct it to a file like this:
如果您只是在 SQL Plus 中测试您的 PL/SQL，您可以将其定向到这样的文件：
spool output.txt
set serveroutput on
begin
SELECT systimestamp FROM dual INTO time_db;
DBMS_OUTPUT.PUT_LINE('time before procedure ' || time_db);
end;
/
spool off
IDEs like Toad and SQL Developer can capture the output in other ways, but I'm not familiar with how.
Toad 和 SQL Developer 等 IDE 可以通过其他方式捕获输出，但我不熟悉如何捕获。
回答by Mahmoud Hanafy
use set serveroutput on;
使用 set serveroutput on;
for example:
例如：
set serveroutput on;
DECLARE
x NUMBER;
BEGIN
x := 72600;
dbms_output.put_line('The variable X = '); dbms_output.put_line(x);
END;
回答by Ian Carpenter
回答by Juan Calero
As a side note, remember that all this output is generated in the server side.
作为旁注，请记住所有这些输出都是在服务器端生成的。
Using DBMS_OUTPUT, the text is generated in the server while it executes your query and stored in a buffer. It is then redirected to your client app when the server finishes the query data retrieval. That is, you only get this info when the query ends.
使用 DBMS_OUTPUT，文本在服务器执行查询时生成并存储在缓冲区中。当服务器完成查询数据检索时，它会被重定向到您的客户端应用程序。也就是说，您只有在查询结束时才能获得此信息。
With UTL_FILE all the information logged will be stored in a file in the server. When the execution finishes you will have to navigate to this file to get the information.
使用 UTL_FILE 记录的所有信息都将存储在服务器的文件中。执行完成后，您必须导航到此文件以获取信息。
Hope this helps.
希望这可以帮助。
回答by Rafa de Castro
Using UTL_FILEinstead of DBMS_OUTPUTwill redirect output to a file:
使用UTL_FILE而不是DBMS_OUTPUT将输出重定向到一个文件：
回答by J. Chomel
Its possible write a file directly to the DB server that hosts your database, and that will change all along with the execution of your PL/SQL program.
它可能将文件直接写入托管您的数据库的 DB 服务器，这将随着您的 PL/SQL 程序的执行而改变。
This uses the Oracle directoryTMP_DIR; you have to declare it, and create the below procedure:
这将使用Oracle 目录TMP_DIR；您必须声明它，并创建以下过程：
CREATE OR REPLACE PROCEDURE write_log(p_log varchar2)
-- file mode; thisrequires
--- 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', 'my_output.log', 'A');
utl_file.put_line(l_file, p_log);
utl_file.fflush(l_file);
utl_file.fclose(l_file);
END write_log;
/
Here is how to use it:
以下是如何使用它：
1) Launch this from your SQL*PLUS client:
1) 从您的 SQL*PLUS 客户端启动：
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 Felypp Oliveira
An old thread, but there is another alternative.
一个旧线程，但还有另一种选择。
Since 9i you can use pipelined table function.
从 9i 开始，您可以使用流水线表函数。
First, create a type as a table of varchar:
首先，创建一个类型作为 varchar 表：
CREATE TYPE t_string_max IS TABLE OF VARCHAR2(32767);
Second, wrap your code in a pipelined function declaration:
其次，将您的代码包装在流水线函数声明中：
CREATE FUNCTION fn_foo (bar VARCHAR2) -- your params
RETURN t_string_max PIPELINED IS
-- your vars
BEGIN
-- your code
END;
/
Replace all DBMS_OUTPUT.PUT_LINEfor PIPE ROW.
全部替换DBMS_OUTPUT.PUT_LINE为PIPE ROW.
Finally, call it like this:
最后，这样称呼它：
SELECT * FROM TABLE(fn_foo('param'));
Hope it helps.
希望能帮助到你。
回答by Mark
Try This:
尝试这个：
SELECT systimestamp INTO time_db FROM dual ;
DBMS_OUTPUT.PUT_LINE('time before procedure ' || time_db);
