Oracle存储过程循环调用时DBMS_OUTPUT消息实时输出问题咨询

问题原因 #

Oracle的DBMS_OUTPUT包默认采用缓存机制输出内容，所有打印的消息都会先暂存在会话级的缓存中，只有当前PL/SQL执行块完全运行结束后，客户端才会一次性读取缓存中的所有内容展示，所以会出现执行完成后才统一显示消息的情况。

DBMS_OUTPUT实时输出实现方法 #

方法1：启用即时刷新（Oracle 12cR2及以上版本支持） #

12cR2之后Oracle新增了DBMS_OUTPUT.ENABLE的flush_immediate参数，开启后每次调用PUT_LINE都会自动刷新缓存：

-- 存储过程开头先执行配置
DBMS_OUTPUT.ENABLE(flush_immediate => TRUE);

-- 之后正常调用PUT_LINE即可实时输出
DBMS_OUTPUT.PUT_LINE('LOOP 1 STARTED');

如果是用SQL*Plus或者SQL Developer执行，还需要提前开启客户端的输出配置：

SET SERVEROUTPUT ON;

方法2：手动调用刷新方法 #

如果是更低版本的Oracle，无法使用自动刷新参数，可以在每次PUT_LINE之后手动调用DBMS_OUTPUT.FLUSH强制刷出缓存：

DBMS_OUTPUT.PUT_LINE('PROCEDURE 1 STARTED');
DBMS_OUTPUT.FLUSH; -- 手动刷新缓存
PROCEDURE_1(x, y);
DBMS_OUTPUT.PUT_LINE('PROCEDURE 1 COMPLETED');
DBMS_OUTPUT.FLUSH;

注意：如果是在第三方客户端工具中使用，还需要确认工具是否支持实时读取DBMS_OUTPUT缓存，部分旧版本的PL/SQL Developer、Navicat等工具本身不支持中途读取缓存，会导致即便调用了FLUSH也无法实时显示，需要升级客户端或者更换工具。

其他替代实现方案 #

如果DBMS_OUTPUT的实时输出受客户端限制无法满足需求，可以采用以下更稳定的方案：

方案1：使用自治事务写进度日志表 #

创建一张专门的进度日志表，用自治事务的存储过程写入日志，因为自治事务不会受主事务的影响，写入后可以立即被其他会话查询到：

1. 先创建日志表：

CREATE TABLE PROCESS_LOG (
    LOG_TIME TIMESTAMP DEFAULT SYSTIMESTAMP,
    LOG_MESSAGE VARCHAR2(4000)
);

2. 编写自治事务的日志写入存储过程：

CREATE OR REPLACE PROCEDURE WRITE_PROCESS_LOG(p_msg VARCHAR2) 
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO PROCESS_LOG(LOG_MESSAGE) VALUES(p_msg);
    COMMIT;
END;
/

3. 主存储过程中替换PUT_LINE为调用日志写入过程即可：

WRITE_PROCESS_LOG('LOOP 1 STARTED');
WRITE_PROCESS_LOG('PROCEDURE 1 STARTED'); 
PROCEDURE_1(x, y);
WRITE_PROCESS_LOG('PROCEDURE 1 COMPLETED');

执行过程中可以在另一个会话中直接查询PROCESS_LOG表即可实时查看进度。

方案2：使用UTL_FILE包写入日志文件 #

将进度消息直接写入操作系统层面的日志文件，写入操作是实时落盘的，可以直接打开日志文件查看进度：

-- 先创建目录对象（需要DBA权限）
CREATE OR REPLACE DIRECTORY PROC_LOG_DIR AS '/opt/oracle/logs';
-- 存储过程中打开文件写入
DECLARE
    log_file UTL_FILE.FILE_TYPE;
BEGIN
    log_file := UTL_FILE.FOPEN('PROC_LOG_DIR', 'process.log', 'W');
    UTL_FILE.PUT_LINE(log_file, 'LOOP 1 STARTED');
    UTL_FILE.FFLUSH(log_file); -- 强制刷入文件
    -- 执行存储过程
    UTL_FILE.PUT_LINE(log_file, 'PROCEDURE 1 COMPLETED');
    UTL_FILE.FFLUSH(log_file);
    -- 全部执行完关闭文件
    UTL_FILE.FCLOSE(log_file);
END;
/

内容的提问来源于stack exchange，提问作者Nick Johnson