Skip to Main Content
  • Questions
  • Error handling behavior change according to PLSQL_OPTIMIZE_LEVEL

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Philippe.

Asked: November 07, 2017 - 6:46 am UTC

Last updated: November 08, 2017 - 1:58 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

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#;

and Connor said...

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


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Philippe Malera, November 08, 2017 - 6:40 am UTC


More to Explore

APEX

Keep your APEX skills fresh by attending their regular Office Hours sessions.