Lightdb 存储过程兼容Oracle dbms_utility.format_error_backtrace函数

背景

Oracle Database 10g Release 2 中添加的 DBMS_UTILITY.format_error_backtrace 函数是记录异常时要调用的关键子程序。它返回一个字符串，将错误追溯到引发错误的行！注意：如果在堆栈向上传播时重新引发异常，您将丢失“原始”行号。回溯跟踪函数始终只追溯到最近引发的异常。错误返回最大长度为10000个字符。

测试

该功能需要在LightDB oracle兼容模式下使用；

create database test_createdb_oracle with lightdb_syntax_compatible_type oracle;
\c test_createdb_oracle

建表语句

create table test_error_stack(id number primary key);
insert into test_error_stack values (1);
create table t_errlog(operate_error_msg varchar(1000));

创建使用dbms_utility.format_error_backtrace函数的存储过程
test1:

create or replace procedure pr_error_code_test1 is
 v_backtrace    varchar(1000);
  v_error        varchar(1000);
  begin
    INSERT INTO test_error_stack values (2); 
    INSERT INTO test_error_stack values (1); 
    commit;
    exception when others then
           insert into t_errlog(operate_error_msg) select to_char(dbms_utility.format_error_backtrace); 
      rollback;
 end pr_error_code_test1;
  /

call pr_error_code_test1();
select * from t_errlog;

test2:

create or replace procedure pr_error_code_test1
is.
v_backtrace    varchar(1000);
v_error        varchar(1000);
begin
  INSERT INTO test_error_stack values (2); 
  INSERT INTO test_error_stack values (1); 
  commit;
  exception when others then
  p_write_errlog();
  rollback;
  end pr_error_code_test1;
  /
 
create or replace procedure p_write_errlog is
begin
insert into t_errlog(operate_error_msg) select
to_char(dbms_utility.format_error_backtrace);
commit;
end;
/

call pr_error_code_test1();
select * from t_errlog;

总结

好久没写文档了。哈哈。