如何记录Oracle存储过程内所有执行操作到自定义日志表

1. 首先创建操作日志表 #

create table proc_exec_log (
    txt varchar2(1000), -- 操作/错误内容
    dt date default sysdate, -- 执行时间默认取当前时间
    proc_name varchar2(100) -- 所属存储过程名称
);

2. 创建独立事务的日志写入存储过程 #

这里用自治事务保证日志记录不会随主存储过程的回滚丢失：

create or replace procedure write_log(p_txt varchar2, p_proc_name varchar2) is
    pragma autonomous_transaction; -- 声明自治事务，和主事务隔离
begin
    insert into proc_exec_log(txt, proc_name) values(p_txt, p_proc_name);
    commit; -- 独立提交日志写入操作
end;
/

3. 改造原有tst存储过程 #

每一步操作完成后写入日志，同时加全局异常捕获记录错误信息：

create or replace procedure tst is
begin
    -- 记录存储过程启动日志
    write_log('procedure started', 'tst');
    
    execute immediate 'create table tb1 select 1 col from dual';
    write_log('table tb1 created', 'tst');
    
    execute immediate 'create table tb2 select 1 col from dual';
    write_log('table tb2 created', 'tst');
    
    insert into tb3 select 1 from dual;
    write_log('insert into tb3', 'tst');
    
    execute immediate 'truncate table tb3';
    write_log('truncate table tb3', 'tst');
    
    -- 出错的语句
    execute immediate 'create table tb4 select 1 col from ';
    write_log('table tb4 created', 'tst');
    
    execute immediate 'truncate table tb4';
    write_log('truncate table tb4', 'tst');
exception
    when others then
        -- 捕获所有错误，写入错误日志
        write_log('error '||sqlerrm, 'tst');
        -- 如果需要向外抛出原始错误，可开启下面的语句
        -- raise;
end;
/

效果验证 #

执行存储过程后，查询proc_exec_log表即可得到你预期的记录结果，错误信息会自动取Oracle返回的报错内容。如果需要出错后继续执行后续语句，可以把每一步操作单独包裹在begin exception end块中，分别捕获单步错误即可。

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