Skip to Main Content
  • Questions
  • Procedures Output Parameters vs Debug Compile

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Teresa.

Asked: September 17, 2020 - 6:52 am UTC

Answered by: Connor McDonald - Last updated: September 21, 2020 - 1:54 am UTC

Category: PL/SQL - Version: 12c / 19c

Viewed 100+ times

You Asked

Hi Tom,

On your website I didn't find anything about this topic that could help me, so I decided to write.
We detect a situation in the execution of a procedure that is not expected to happen. The output parameters are returned with values, when it was not supposed to.
The situation became even stranger, when after several tests we concluded that the way the package was compiled influenced the behavior of the function's execution in what refers to the output parameters.
That is, if the package is compiled in debug mode, the results are as expected. The same does not happen if the package is not compiled in debug.

The scenario: when executing a function that results in an error, it is not expected that the output parameter will be filled in (unless the code is not well written). But the case in point is that the code does not point to the output parameters being filled.
Furthermore, if we manipulate the OPTIMIZE LEVEL defined in the database, the behavior on the output parameters is also influenced. The optimize levels 0 and 1 (corresponding to the debug), everything works fine. Level 2 always works poorly and with Level 3, depending on the complexity of the code, sometimes it works well and sometimes badly.

Simple examples were created to highlight the anomaly detected.
The attached script creates two functions and the call / execution of "FCOUTPUT_TEST_LEVEL_1"

It should be noted that this situation was detected and tested in oracle 12c and oracle 19c (PDB)

In advance I appreciate the help you can give.

with LiveSQL Test Case:

and we said...

This is covered in the docs, albeit with a general over-arching statement:

https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-subprograms.html#GUID-18E72176-145F-4E12-95F1-03F84B3D5DC6

"If the subprogram ends with an exception, then the value of the actual parameter is undefined."


If a routine ends with an error, you cannot assume *anything* about the values of the parameters that come back.

This is related to the optimization level, because we may re-arrange your code to improve its performance. So as a result, the timing and occurrences of assignments to out bound variables may have been shifted around.

But the bottom line is - you cannot "use" the value of an OUT parameter if that function encounters an error. Its value could be anything.

and you rated our response

  (1 rating)

Reviews

September 18, 2020 - 5:53 am UTC

Reviewer: A reader from Portugal

Typically, the values ​​of the outputs are only used when the function ends successfully. However, I found this behavior to be very strange, as it had never been detected before.

Thanks for your answer and explanations.
Connor McDonald

Followup  

September 21, 2020 - 1:54 am UTC

Same with me.

The reason I could answer this question easily is I encountered it a few years back for the first time as well and investigated with the PLSQL product manager at the time. It was that discussion that led to the sentence being added to the documentation :-)

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.