The PL/SQL Challenge (www.plsqlchallenge.com) offers a daily quiz on the PL/SQL language, through which thousands of Oracle technologists demonstrate and deepen their knowledge of PL/SQL. This blog contains posts by the PL/SQL Challenge founder, Steven Feuerstein, as well as comments from players.

27 November 2010

Indeterminate behavior with same variable bound to multiple placeholders? (26 November quiz) (1710)

Several players did not agree with the explanation provided for one of the choices of the 26 November quiz. This quiz tested your knowledge of the need to specify an OUT or IN OUT mode for bind variables when retrieving values of PL/SQL expressions from a dynamic PL/SQL block.
We showed this code as a choice:

We scored the choice as incorrect, explaining:
"This block will run without error, but it will also display the name of the lobbying firm, rather than the company name. That's because I used the same variable for the two OUT bind variables."
Three players argued that while the choice is truly not correct, the explanation is wrong, because as one person put it "This is an example of parameter aliasing. The result is INDETERMINATE. Any correctness of this choice is questionable, because it can not be proven by documentation."
Rather than comment further, I offer this post as a starting point for discussion, especially from those who communicated their concerns to me directly.
So please post your comments and code samples for all to learn from!
SF

16 comments:

It’s my quote. I wrote this before publishing results.And oh! I’m lucky! I guessed the current PL/SQL implementation.Maybe it is not implementation issue? But rather it is issue of some circumstances?How can we be certain? Nohow.It’s very similar to Subprogram Parameter Aliasing http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/subprograms.htm#sthref1836Using same var twice in out position is not described there. But it’s obvious that we have two names of the same variable. And order in which values of formal parameter will be copied to formal parameters is defined nowhere. Even if we see some order it can’t prove anything.

I have disagreed not with the explanation, but with the choice itself (have sent my comment before the explanation even appeared). Anyhow, an outcome of this choice is indeterminable - might depend on a DB version/patchlevel, an optimization level and anything that is able to influence the PL/SQL compiler behavior.

So, the explanation should be like "never, ever use parameter aliasing".

Empirical evidence supports the provided explanation, but there does not appear to be any official Oracle documentation that defines the order in which parameters are processed when calling or returning from a procedure. I conducted some experiments with Oracle 11gR2 and have ascertained that Oracle processes parameters in the order in which they appear in the formal parameter list (this occurs even when the actual parameters occur in a different order in the call when assigned by name instead of position). Despite these observations, this behavior should not be relied upon and should be considered indeterminate until it is described in official Oracle documentation.

Regardless of any deficiencies in the explanation, the choice is properly identified as incorrect. The question asks which of the choices will, not might, display the specific message (will is implicit in the original phrasing). If the behavior of the choice is indeterminate, then it cannot be guaranteed to act in the desired manner. Rescoring is only necessary if someone can demonstrate that their Oracle 10gR2 installation behaves in a manner contrary to the explanation and thus caused them to select this choice.

declare x varchar2(22);begin -- Aliasing in execute immediate execute immediate 'begin :1:=1; :2:=2; end;' using out x, out x; dbms_output.put_line(x); execute immediate 'begin :2:=2; :1:=1; end;' using out x, out x; dbms_output.put_line(x); execute immediate 'begin for i in 1..2 loop if i=1 then :1:=1; else :2:=2; end if; end loop; end;' using out x, out x; dbms_output.put_line(x); execute immediate 'begin for i in reverse 1..2 loop if i=1 then :1:=1; else :2:=2; end if; end loop; end;' using out x, out x; dbms_output.put_line(x); execute immediate 'begin for i in 1..2 loop if i=2 then :2:=2; else :1:=1; end if; end loop; end;' using out x, out x; dbms_output.put_line(x); execute immediate 'begin for i in reverse 1..2 loop if i=2 then :2:=2; else :1:=1; end if; end loop; end;' using out x, out x; dbms_output.put_line(x);

There are series of experiments. Basing on its results we can SUPPOSE some pattern:1) Aliased procedure parameters are assigned from left to right regardless of procedure contents.2) Aliased bind variables of execute immediate are assigned in order of dynamic anonymous block flow.It's hypothesis. Does the series sufficient to claim it to be a rule? I don't know.And documentation for black box of PL/SQL does not have an answer.

So the exploration has no practical meaning because its conclusions unreliable.As developer we should avoid aliasing whenever we need predictable result. And we MUST don't care about the order in which aliased parameters/variables are assigned.

You say: "This block will run without error, but it will also display the name of the lobbying firm, rather than the company name. That's because I used the SAME variable for the two OUT bind variables."

If you use DIFFERENT variables it will still display the name of the lobbying firm, rather than the company name. That's because the use of 'l_best_friend' in the wrong place.The choice will be correct if you change the procedure as follows:

When I was taking the quiz I doubted wether it was even possible to bind the same variable twice. I tested this with a simple procedure and block and was surprised to see that you were allowed to. I overlooked the fact that the wrong value was displayed and marked the option, and that was incorrect.

But the (false) assumption the block won't even compile, like two of the other answers, would in this case have earned a correct score.

What I meant to say was that someone might look (glance) at the code, and would judge that it would not compile, and would therefore not mark the choice. One could easily come (jump) to this conclusion since two of the four blocks presented also would not compile.

So by the way you formulate this option, you can not be sure that it was not marked because of the assumption the block won't compile, or whether someone understood all the consequences of binding the same value twice.

You would have to reformulate the question to avoid this, so it was just a remark.

Why are you doing this?I believe you are a really smart guy, but all you do here is putting up a smokescreen!

Xmas is coming up, and I bought a pair of socks for use as a present. Now I am wondering what wrapping to use; Red, Green, Blue, Silver, Gold. You know what? It doesn't matter which wrapping I choose. Inside will still be the pair of socks.

Likewise with your sample code. All different wrappings, all containing the same "pair of socks".

>I am a firm believer in compiled bits doing the same thing over and over again. I also consider it unlikely that Oracle should have built in features to, occasionally, break that rule.

Mike, many people believed that group-by without order-by do sort. They even proved it by consistency of results' pattern. They just didn't know (or didn't want to know) how to do proper tests. And new implementation of sorting easely proved inconsistency of their belief.But it taked time before they undesttood that eyes can lie.

Vitaly, and it (as well as distinct) indeed did sort in quite old Oracle version. Moreover, it was "semi-documented" - in a sense that Oracle has recommended (e.g. in Oracle magazine articles) to apply distinct to the inner queries in a need of sorting in a times when the ORDER BY clause was not supported in inner queries (e.g. for top-N). But semi-documented does not mean documented.

Mike - do not forget that PL/SQL compiler has several optimization levels, and I would not be at all surprised if a code using undocumented behavior would behave differently if compiled with different optimization. Not so uncommon in other languages, e.g. infamous expression C>C++ which may evaluate to either 0 and 1, depending on optimization chosen by the compiler.

Why I am insisting on rescore? It is principal for me.I cannot admit the fact itself of existence of choices that assume a developer have to know current implementation of undocumented PL/SQL behavior. Instead he must avoid any INDETERMINATION.I don’t like guess what I couldn’t know.

It’s a pity but INDETERMINATION of implementation happens again. In playoff. When using variable and function modifying that variable via out parameter in one expression. A result depends on what compiler decide to do first: use variable value in expression or call the function. A slight modification of code will show the difference of implementation between versions of PL/SQL compiler.