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

Last updated: September 21, 2020 - 1:54 am UTC

Version: 12c / 19c

Viewed 1000+ 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:
https://livesql.oracle.com/apex/livesql/s/koa1jsurcx63tfkf53rquvwrk

and Connor 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.

Rating

  (1 rating)

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

Comments

A reader, September 18, 2020 - 5:53 am UTC

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

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

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library