暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

Oracle 如何解决冲突: 需要日志记录包括SQLCODE & SQLERRM,同时控制副作用

askTom 2017-04-04
353

问题描述

以下是关于SQLCODE,SQLERRM,PRAGMA_RESTRICT_REFERENCES及其弃用以及对日志记录框架的需求的两个紧密相关的问题。

我们在PL/SQL中开发了一个日志记录框架,它允许我们使用简单的语句,如log.info (“一些文本”); 将日志消息和异常消息 (包括上下文) 写入数据库表,类似于Log4J和其他日志记录框架。
当然,当框架必须实际编写消息时,该框架会使用自治事务,因为通常,当异常导致回滚时,日志对于分析非常有帮助。

对于异常消息,框架使用SQLCODE和SQLERRM (嗯,实际上不是SQLERRM,而是在11g上的各种助手DBMS_UTILITY.FORMAT % 或在12c上的UTL_CALLSTACK.%)。

文档说,当使用SQLCODE或SQLERRM关于PRAGMA RESTRICT_REFERENCES: 限制WNPS时: 如果子程序调用SQLCODE或SQLERRM函数 (对于RNPS也是如此),则无法指定WNPS。

Q1: 为什么这些功能违反RNPS和WNPS?不明显。

无论如何,我们通过为日志记录过程指定信任来解决此问题。
这样,数据库就可以接受日志记录过程没有副作用的谎言。
好吧,除了插入到日志表中所需的副作用之外,这些过程没有副作用-它们不会读取或写入任何其他表。

很明显,一旦我们想调用log.info(...),我们就会遇到麻烦; 从日志表上的触发器内部,但是我们当然不会这样做。

除此之外,我们可以在触发器中的任何地方使用日志记录过程,在从SQL调用的函数中使用eben,没有任何问题。
实际上,编写日志消息可能会减慢查询的速度,当然,但是框架可以为不同的例程打开或关闭日志记录,并且基于日志记录包中的PL/SQL表,决策代码很便宜。

从12c开始,文档说RESTRICT_REFERENCES已被弃用。

Q2: 如何使用SQLCODE和SQLERRM/UTL_CALL_STACK的日志记录过程,并可以从12c上的SQL内部函数调用一个代码?

专家解答

很抱歉花了这么长时间才回复。但是复活节假期让我们赶上了 :-)

如果您使用的是自主交易,则不应在任何地方使用日志记录样式的过程

SQL> @drop log_table

Y1                      Y2
----------------------- -------------------------
TABLE                   cascade constraints purge

1 row selected.


Table dropped.

SQL>
SQL> create table log_table(s number generated as identity, m varchar2(1000));

Table created.

SQL>
SQL> create or replace
  2  procedure logger(p_msg varchar2) is
  3    pragma autonomous_transaction;
  4  begin
  5    insert into log_table (m) values (p_msg);
  6    commit;
  7  end;
  8  /

Procedure created.

SQL>
SQL> create or replace
  2  function func(p_force_fail varchar2) return int is
  3  begin
  4    logger('start of function');
  5
  6    if p_force_fail = 'Y' then
  7       raise_application_error(-20000,'Oh no...I died');
  8    end if;
  9
 10    logger('end of function');
 11    return 1;
 12  exception
 13    when others then
 14      logger(sqlerrm);
 15  end;
 16  /

Function created.

SQL>
SQL>
SQL> variable n number
SQL> exec :n := func('N');

PL/SQL procedure successfully completed.

SQL> exec :n := func('Y');
BEGIN :n := func('Y'); END;

*
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "MCDONAC.FUNC", line 14
ORA-06512: at line 1


SQL> select * from log_table;

         S
----------
M
----------------------------------------------------------------------------------------------------
         1
start of function

         2
end of function

         3
start of function

         4
ORA-20000: Oh no...I died


4 rows selected.

SQL>
SQL> select func('N') from dual;

 FUNC('N')
----------
         1

1 row selected.

SQL> select func('Y') from dual;
select func('Y') from dual
       *
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "MCDONAC.FUNC", line 14


SQL> select * from log_table;

         S
----------
M
----------------------------------------------------------------------------------------------------
         1
start of function

         2
end of function

         3
start of function

         4
ORA-20000: Oh no...I died

         5
start of function

         6
end of function

         7
start of function

         8
ORA-20000: Oh no...I died


8 rows selected.

SQL>
SQL> col m format a50
SQL> @drop log_table

Y1                      Y2
----------------------- -------------------------
TABLE                   cascade constraints purge

1 row selected.


Table dropped.

SQL>
SQL> create table log_table(s number generated as identity, m varchar2(1000));

Table created.

SQL>
SQL> create or replace
  2  procedure logger(p_msg varchar2) is
  3    pragma autonomous_transaction;
  4  begin
  5    insert into log_table (m) values (p_msg);
  6    commit;
  7  end;
  8  /

Procedure created.

SQL>
SQL> create or replace
  2  function func(p_force_fail varchar2) return int is
  3  begin
  4    logger('start of function');
  5
  6    if p_force_fail = 'Y' then
  7       raise_application_error(-20000,'Oh no...I died');
  8    end if;
  9
 10    logger('end of function');
 11    return 1;
 12  exception
 13    when others then
 14      logger(sqlerrm);
 15  end;
 16  /

Function created.

SQL>
SQL>
SQL> variable n number
SQL> exec :n := func('N');

PL/SQL procedure successfully completed.

SQL> exec :n := func('Y');
BEGIN :n := func('Y'); END;

*
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "MCDONAC.FUNC", line 14
ORA-06512: at line 1


SQL> select * from log_table;

         S M
---------- --------------------------------------------------
         1 start of function
         2 end of function
         3 start of function
         4 ORA-20000: Oh no...I died

4 rows selected.

SQL>
SQL> select func('N') from dual;

 FUNC('N')
----------
         1

1 row selected.

SQL> select func('Y') from dual;
select func('Y') from dual
       *
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "MCDONAC.FUNC", line 14


SQL> select * from log_table;

         S M
---------- --------------------------------------------------
         1 start of function
         2 end of function
         3 start of function
         4 ORA-20000: Oh no...I died
         5 start of function
         6 end of function
         7 start of function
         8 ORA-20000: Oh no...I died

8 rows selected.

SQL>
SQL> @drop log_table

Y1                      Y2
----------------------- -------------------------
TABLE                   cascade constraints purge

1 row selected.


Table dropped.

SQL>
SQL> create table log_table(s number generated as identity, m varchar2(1000));

Table created.

SQL>
SQL> create or replace
  2  procedure logger(p_msg varchar2) is
  3    pragma autonomous_transaction;
  4  begin
  5    insert into log_table (m) values (p_msg);
  6    commit;
  7  end;
  8  /

Procedure created.

SQL>
SQL> create or replace
  2  function func(p_force_fail varchar2) return int is
  3  begin
  4    logger('start of function');
  5
  6    if p_force_fail = 'Y' then
  7       raise_application_error(-20000,'Oh no...I died');
  8    end if;
  9
 10    logger('end of function');
 11    return 1;
 12  exception
 13    when others then
 14      logger(sqlerrm);
 15      raise;
 16  end;
 17  /

Function created.

SQL>
SQL>
SQL> variable n number
SQL> exec :n := func('N');

PL/SQL procedure successfully completed.

SQL> exec :n := func('Y');
BEGIN :n := func('Y'); END;

*
ERROR at line 1:
ORA-20000: Oh no...I died
ORA-06512: at "MCDONAC.FUNC", line 14
ORA-06512: at "MCDONAC.FUNC", line 6
ORA-06512: at line 1


SQL> select * from log_table;

         S M
---------- --------------------------------------------------
         1 start of function
         2 end of function
         3 start of function
         4 ORA-20000: Oh no...I died

4 rows selected.

SQL>
SQL> select func('N') from dual;

 FUNC('N')
----------
         1

1 row selected.

SQL> select func('Y') from dual;
select func('Y') from dual
       *
ERROR at line 1:
ORA-20000: Oh no...I died
ORA-06512: at "MCDONAC.FUNC", line 14
ORA-06512: at "MCDONAC.FUNC", line 6


SQL> select * from log_table;

         S M
---------- --------------------------------------------------
         1 start of function
         2 end of function
         3 start of function
         4 ORA-20000: Oh no...I died
         5 start of function
         6 end of function
         7 start of function
         8 ORA-20000: Oh no...I died

8 rows selected.

SQL>
SQL>
复制


「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论