PL/SQL and edition-based redefinition:
the perfect marriage.

  • September 28, 2015

A Surprising Program

A correspondent recently sent the PL/SQL team a small code example and reported surprise at the program's behavior. Once the example was stripped of superfluities, the behavior was easily explained because of a simple rule every PL/SQL programmer should know.The rule is

When a call terminates with an unhandled exception, the value of an actual argument associated with an out formal parameter becomes undefined.

This note shows the example code and provides a detailed explanation. I hope you will enjoy reading it.

Join the discussion

Comments ( 1 )
  • Bryn Tuesday, September 29, 2015

    It turns out that our correspondent's surprise either was, or was not, warranted depending upon which section of the documentation he read. (This is the proper pronoun. I happen to know him rather well.) Referring to the PDF copy of the latest version of the PL/SQL Language Reference (part E50727-04, July 2014), we see on p. 8-15 the text that Charles referred to: "If the subprogram ends with an exception, then the value of the actual parameter is undefined." But on p. 11-25 we find this: "If a subprogram exits with an unhandled exception, then actual parameters for OUT and IN OUT formal parameters passed by value (the default) retain the values that they had before the subprogram invocation (see Example 8.15). And on p. 12-7, we find this: "By default, PL/SQL passes OUT and IN OUT subprogram parameters by value. Before running the subprogram, PL/SQL copies each OUT and IN OUT parameter to a temporary variable, which holds the value of the parameter during subprogram execution. If the subprogram is exited normally, then PL/SQL copies the value of the temporary variable to the corresponding actual parameter. If the subprogram is exited with an unhandled exception, then PL/SQL does not change the value of the actual parameter." Make no mistake. The statements of our rule on p. 11-25 and on p. 12-7 are downright wrong. I filed doc bug 21913555 to this effect. Those of you with access to My Oracle Support can check it out.

Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.