如何将DBMS_OUTPUT.PUT_LINE的输出重定向到文件? [英] How to redirect the output of DBMS_OUTPUT.PUT_LINE to a file?

问题描述

我需要在pl/sql中调试才能确定过程的次数，我想使用:

I need to debug in pl/sql to figure times of procedures, I want to use:

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?

推荐答案

DBMS_OUTPUT并不是调试的最佳工具，因为大多数环境都不是本地使用的.但是，如果要捕获DBMS_OUTPUT的输出，则只需使用DBMS_OUTPUT.get_line过程.

DBMS_OUTPUT is not the best tool to debug, since most environments don't use it natively. If you want to capture the output of DBMS_OUTPUT however, you would simply use the DBMS_OUTPUT.get_line procedure.

这是一个小例子:

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

这篇关于如何将DBMS_OUTPUT.PUT_LINE的输出重定向到文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！