Oracle 10g中如何用UTL_FILE打印存储过程插入TEST_TABLE的行数？

没问题，我来一步步教你怎么实现这个需求——用存储过程插入数据后，通过UTL_FILE生成包含插入行数的监控文件。

1. 先搞定UTL_FILE的前置准备 #

UTL_FILE依赖数据库级的目录对象（不是直接用操作系统路径），还需要给执行用户分配权限：

• 创建目录对象（替换成你实际的操作系统路径，比如Linux的/opt/oracle/monitor_logs或Windows的D:\oracle\load_logs）：

CREATE OR REPLACE DIRECTORY MONITOR_LOG_DIR AS '/opt/oracle/monitor_logs';

• 给执行存储过程的用户授权读写该目录：

GRANT READ, WRITE ON DIRECTORY MONITOR_LOG_DIR TO YOUR_DB_USER;

• 确保用户有UTL_FILE的执行权限：

GRANT EXECUTE ON UTL_FILE TO YOUR_DB_USER;

2. 编写带UTL_FILE输出的存储过程 #

下面是完整的示例存储过程，核心逻辑是：执行插入后统计行数，再用UTL_FILE把监控信息写入文件：

CREATE OR REPLACE PROCEDURE INSERT_INTO_TEST_TABLE
IS
    v_insert_count NUMBER := 0;
    v_file_handle UTL_FILE.FILE_TYPE;
    -- 用时间戳命名日志文件，避免覆盖旧记录
    v_log_filename VARCHAR2(100) := 'test_table_load_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '.log';
BEGIN
    -- 第一步：执行你的插入逻辑（替换成实际业务SQL）
    INSERT INTO TEST_TABLE (COL1, COL2, COL3)
    SELECT SRC_COL1, SRC_COL2, SRC_COL3
    FROM SOURCE_DATA_TABLE
    WHERE LOAD_DATE = TRUNC(SYSDATE); -- 示例过滤条件

    -- 获取本次插入的行数（必须紧跟INSERT语句执行）
    v_insert_count := SQL%ROWCOUNT;

    -- 第二步：生成监控日志文件
    -- 打开文件，W表示覆盖写入，32767是每行最大字节数
    v_file_handle := UTL_FILE.FOPEN('MONITOR_LOG_DIR', v_log_filename, 'W', 32767);

    -- 写入监控内容
    UTL_FILE.PUT_LINE(v_file_handle, 'TEST_TABLE数据加载监控日志');
    UTL_FILE.PUT_LINE(v_file_handle, '----------------------------------------');
    UTL_FILE.PUT_LINE(v_file_handle, '加载时间: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
    UTL_FILE.PUT_LINE(v_file_handle, '目标表名: TEST_TABLE');
    UTL_FILE.PUT_LINE(v_file_handle, '本次成功插入行数: ' || v_insert_count);
    UTL_FILE.PUT_LINE(v_file_handle, '----------------------------------------');

    -- 必须关闭文件，避免句柄泄漏
    UTL_FILE.FCLOSE(v_file_handle);

    -- 提交事务（根据业务需求决定是否需要）
    COMMIT;

    DBMS_OUTPUT.PUT_LINE('存储过程执行完成，监控日志已生成: ' || v_log_filename);
EXCEPTION
    WHEN OTHERS THEN
        -- 异常处理：如果文件未关闭，先强制关闭
        IF UTL_FILE.IS_OPEN(v_file_handle) THEN
            UTL_FILE.FCLOSE(v_file_handle);
        END IF;
        -- 输出错误信息并回滚
        DBMS_OUTPUT.PUT_LINE('执行出错: ' || SQLERRM);
        ROLLBACK;
        RAISE;
END;
/

3. 执行存储过程并验证结果 #

• 执行存储过程：

EXEC INSERT_INTO_TEST_TABLE;

• 查看DBMS_OUTPUT的输出，会显示生成的日志文件名。之后去你创建的MONITOR_LOG_DIR对应的操作系统目录下，就能找到包含插入行数的监控文件了。

关键注意事项 #

• 操作系统权限：Oracle数据库的运行用户（比如Linux下的oracle用户）必须对指定的操作系统目录有读写权限，否则UTL_FILE会抛出权限错误。
• 文件名策略：示例用时间戳命名生成独立文件，如果你需要追加到同一个日志文件，可以把FOPEN的第三个参数改成A（追加模式）。
• SQL%ROWCOUNT的时机：必须紧跟在INSERT语句之后获取，否则中间如果有其他DML操作，这个值会被覆盖。
• 异常处理：一定要在异常块中检查并关闭文件，防止文件句柄泄漏导致后续写文件失败。

内容的提问来源于stack exchange，提问作者Shaan Anshu