We had faced a case in our application where error message disappear according to PLSQL_OPTIMIZE_LEVEL.
I had isolated the problem in a simple script.
Run this script, you will see that at first execution of the procedure "test_error_proc#" error is displayed normally, but at second execution (plsql_optimize_level = 2), error message disappear.
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#;
Thanks a nice self-contained test case.
I'm not sure what you mean by "disappear" - do you mean this ?
--
-- 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
If so, then this appears to be resolved in 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