Oracle 错误处理行为根据PLSQL_OPTIMIZE_LEVEL改变

根据PLSQL_OPTIMIZE_LEVEL，我们在应用程序中遇到了错误消息消失的情况。
我在一个简单的脚本中隔离了这个问题。
运行此脚本，您将看到在第一次执行过程 “test_error_proc #” 时正常显示错误，但是在第二次执行时 (plsql_optimize_level = 2)，错误消息消失。

ALTER SESSION SET PLSQL_CODE_TYPE = INTERPRETED;
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 2;
ALTER SESSION SET PLSQL_WARNINGS = 'DISABLE:ALL';
ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:NONE';

CREATE OR REPLACE PACKAGE test_error# AUTHID DEFINER IS

  TYPE record_rt IS RECORD(
    name user_source.name%TYPE,
    TYPE user_source.type%TYPE,
    line user_source.line%TYPE,
    text user_source.text%TYPE);

  TYPE table_ntt IS TABLE OF record_rt;

  FUNCTION get_source RETURN table_ntt
    PIPELINED;

END test_error#;
/

CREATE OR REPLACE PACKAGE BODY test_error# IS

  FUNCTION get_source RETURN table_ntt
    PIPELINED IS
    r_row record_rt;
  BEGIN
  
    FOR r_loop IN (SELECT name, TYPE, line, text FROM user_source WHERE name = $$PLSQL_UNIT ORDER BY name, TYPE, line)
    LOOP
      r_row.name := r_loop.name;
      r_row.type := r_loop.type;
      r_row.line := r_loop.line;
      r_row.text := r_loop.text;
      PIPE ROW(r_row);
    END LOOP;
    raise_application_error(num => -20000, msg => 'This is an error !');

  END get_source;

END test_error#;
/

CREATE OR REPLACE PROCEDURE test_error_proc# AS
BEGIN
  FOR idx IN (SELECT ROWNUM FROM TABLE(test_error#.get_source))
  LOOP
    NULL;
  END LOOP;
END test_error_proc#;
/

ALTER PROCEDURE test_error_proc# COMPILE plsql_optimize_level = 1;
EXEC test_error_proc#;

ALTER PROCEDURE test_error_proc# COMPILE plsql_optimize_level = 2;
EXEC test_error_proc#;

DROP PROCEDURE test_error_proc#;
DROP PACKAGE test_error#;
复制

感谢一个不错的独立测试用例。

我不确定您所说的 “消失” 是什么意思-您是这个意思吗？

--
-- 11.2.0.4 / 12.1.0.2
--
SQL> ALTER PROCEDURE test_error_proc# COMPILE plsql_optimize_level = 1;

Procedure altered.

SQL> EXEC test_error_proc#;
BEGIN test_error_proc#; END;

*
ERROR at line 1:
ORA-20000: This is an error !
ORA-06512: at "MCDONAC.TEST_ERROR#", line 16
ORA-06512: at "MCDONAC.TEST_ERROR_PROC#", line 3
ORA-06512: at line 1


SQL>
SQL> ALTER PROCEDURE test_error_proc# COMPILE plsql_optimize_level = 2;

Procedure altered.

SQL> EXEC test_error_proc#;
BEGIN test_error_proc#; END;

*
ERROR at line 1:
ORA-20000:
ORA-06512: at "MCDONAC.TEST_ERROR_PROC#", line 3
ORA-06512: at line 1
复制

如果是这样，那么这似乎在12.2中得到解决

SQL> ALTER PROCEDURE test_error_proc# COMPILE plsql_optimize_level = 1;

Procedure altered.

SQL> EXEC test_error_proc#;
BEGIN test_error_proc#; END;

*
ERROR at line 1:
ORA-20000: This is an error !
ORA-06512: at "MCDONAC.TEST_ERROR#", line 16
ORA-06512: at "MCDONAC.TEST_ERROR_PROC#", line 3
ORA-06512: at "MCDONAC.TEST_ERROR_PROC#", line 3
ORA-06512: at line 1


SQL> ALTER PROCEDURE test_error_proc# COMPILE plsql_optimize_level = 2;

Procedure altered.

SQL> EXEC test_error_proc#;
BEGIN test_error_proc#; END;

*
ERROR at line 1:
ORA-20000: This is an error !
ORA-06512: at "MCDONAC.TEST_ERROR#", line 16
ORA-06512: at "MCDONAC.TEST_ERROR_PROC#", line 3
ORA-06512: at "MCDONAC.TEST_ERROR_PROC#", line 3
ORA-06512: at line 1
复制