在Oracle的PL/SQL中，有没有一种方法可以清除输出？

Question

我有一个SQL脚本，它在shell脚本中被调用，并且执行时间很长。目前，在不同的位置上脚本中包含了dbms_output.put_line语句。这些打印语句的输出会出现在日志文件中，但是只有在脚本完成后才会出现。

有没有办法确保输出在脚本运行时就出现在日志文件中呢？

- baxter

Answer 1

并非如此。DBMS_OUTPUT 的工作原理是这样的：您的 PL/SQL 块在数据库服务器上执行，与客户端没有任何交互。因此，当您调用 PUT_LINE 时，它只是将文本放入服务器上的内存缓冲区中。当您的 PL/SQL 块完成后，控制权返回给客户端（我假设这里使用的是 SQLPlus）；此时客户端通过调用 GET_LINE 从缓冲区获取文本，并将其显示出来。

因此，让输出更频繁地出现在日志文件中的唯一方法是将一个大的 PL/SQL 块分解成多个较小的块，以便更经常地将控制权返回给客户端。这可能取决于您的代码在做什么而不太实用。

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

- Dave Costa

Answer 2

如果可以的话，您应该将对 dbms_output.put_line 的调用替换为自己的函数。这是该函数的代码 WRITE_LOG -- 如果您想要在两种日志记录方案之间进行选择：

将日志写入一个表中的自主事务

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;
/

或直接将数据写入托管数据库的DB服务器

这将使用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过程，它可以在两种模式之间切换，并可以停用以避免影响性能（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;
/

以下是如何测试上述内容的方法：

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）在数据库服务器上，打开一个shell并执行以下命令：

tail -f -n500 /directory/where/oracle/can/write/on/DB_server/my_output.log

- J. Chomel

Answer 3

两种选择：

1. 您可以使用自主事务将日志详细信息插入日志表中。您可以在另一个SQLPLUS / Toad / sql developer等会话中查询此日志表。您必须使用自主事务才能使提交日志成为可能，而不会干扰主要SQL脚本中的事务处理。

2. 另一种选择是使用返回日志信息的管道函数。这里有一个示例：http://berxblog.blogspot.com/2009/01/pipelined-function-vs-dbmsoutput.html 当您使用管道函数时，您不必使用另一个SQLPLUS / Toad / sql developer等会话。

- tuinstoel

Answer 4

DBMS_OUTPUT的缓冲区在调用过程DBMS_OUTPUT.get_line时被读取。如果您的客户端应用程序是SQL*Plus，则这意味着它只会在过程完成后刷新。

您可以使用此SO描述的方法将DBMS_OUTPUT缓冲区写入文件。

- Vincent Malgrat

Answer 5

使用dbms_application_info()设置会话元数据的模块和/或操作。
例如，可以使用OEM进行监视：

Module: ArchiveData
Action: xxx of xxxx

- user2196257

---

您也可以直接监控ASH（v$active_session_history）。 - BobC

Answer 6

如果您可以从 PL/SQL 环境访问系统 shell，则可以调用 netcat：

 BEGIN RUN_SHELL('echo "'||p_msg||'" | nc '||p_host||' '||p_port||' -w 5'); END;

p_msg - 是一个日志消息。 v_host 是运行 Python 脚本的主机，该脚本在端口 v_port 上读取数据。

我在编写 aplogr 用于实时 shell 和 pl/sql 日志监控时使用了这个设计。

- olekb

---

请不要在多个问题中添加相同的答案。回答最好的一个，并将其余标记为重复。请参见是否可以将重复的答案添加到多个问题中？ - Mogsdad