DECLARE
l_best_friend VARCHAR2 (100);
BEGIN
EXECUTE IMMEDIATE 'BEGIN special_friend ( :n, :v1, :v2 ); END;'
USING 2010, OUT l_best_friend, OUT l_best_friend;
DBMS_OUTPUT.put_line (l_best_friend);
END; 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
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:
Subscribe to:
Post Comments (Atom)
It’s my quote. I wrote this before publishing results.
ReplyDeleteAnd 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#sthref1836
Using 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.
So this choice should be rescored.
Hello Steven,
ReplyDeleteI 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".
Oleksandr
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.
ReplyDeleteRegardless 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.
create or replace procedure tmp_from_first_to_last(x out varchar2, y out varchar2)
ReplyDeleteas
begin
x := 'x';
y := 'y';
end tmp_from_first_to_last;
/
create or replace procedure tmp_from_last_to_first(x out varchar2, y out varchar2)
as
begin
y := 'y';
x := 'x';
end tmp_from_last_to_first;
/
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);
-- Aliasing in procedure call
tmp_from_first_to_last(x, x);
dbms_output.put_line(x);
tmp_from_last_to_first(x, x);
dbms_output.put_line(x);
-- Twice aliasing in procedure call and execute immediate
execute immediate 'begin tmp_from_first_to_last(:1, :2); end;' using out x, out x;
dbms_output.put_line(x);
execute immediate 'begin tmp_from_first_to_last(:2, :1); end;' using out x, out x;
dbms_output.put_line(x);
execute immediate 'begin tmp_from_last_to_first(:1, :2); end;' using out x, out x;
dbms_output.put_line(x);
execute immediate 'begin tmp_from_last_to_first(:2, :1); end;' using out x, out x;
dbms_output.put_line(x);
end;
/
drop procedure tmp_from_first_to_last;
drop procedure tmp_from_last_to_first;
----------------------------------------
2
1
2
1
2
1
y
y
y
y
y
y
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.
Hello Steven,
ReplyDeleteYou 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."
See this block:
DECLARE
l_best_friend VARCHAR2 (100);
l_different_var VARCHAR2 (100);
BEGIN
EXECUTE IMMEDIATE 'BEGIN special_friend ( :n, :v1, :v2 ); END;'
USING 2010, OUT l_different_var, OUT l_best_friend;
DBMS_OUTPUT.put_line (l_best_friend);
END;
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:
CREATE OR REPLACE PROCEDURE special_friend (year_in IN NUMBER
, company_out OUT VARCHAR2
, lobbyist_out OUT VARCHAR2)
IS
BEGIN
lobbyist_out := 'Revolving, Door and Associates';
company_out := 'Too Big To Fail, Inc.';
END;
/
Best regards,
Hamid Talebian
Perhaps we should take a step back from what the documentation says, what empirical testing shows, and accept that it would simply be poor programming practice to do something like this?
ReplyDeleteIf the documentation doesn't clarify some esoteric example (I'm sure there's many of those), and you not sure how your particular version will behave - it shouldn't be done. Is that enough?
Excellent point, Scott, and thanks Vitaliy for your analysis.
ReplyDeleteIt seems to me that there is no reason to rescore. The choice is not correct. But I will change the explanation to point out the unreliability (and undocumented nature) of doing something like this.
SF
Indeed, there is no need to rescore. My point is that this choice would be incorrect even if by chance Oracle would show the company name.
ReplyDeleteHi Steven,
ReplyDeleteJust a remark on the method of questioning:
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.
Kind regards,
Jeroen R
Jeroen,
ReplyDeleteWould you please explain what you mean by "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."
Thanks, SF
Hi Steven,
ReplyDeleteWhat 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.
Kind regards,
Jeroen R
Vitaliy,
ReplyDeleteWhy 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 have taken the liberty of translating your code into something equivalent:
declare
x varchar2(22);
begin
-- Aliasing in execute immediate
-- execute immediate 'begin :1:=1; :2:=2; end;' using out x, out x;
execute immediate 'declare x varchar2(22); begin x:=1; x:=2; dbms_output.put_line(x); end;';
-- execute immediate 'begin :2:=2; :1:=1; end;' using out x, out x;
execute immediate 'declare x varchar2(22); begin x:=2; x:=1; dbms_output.put_line(x); end;';
-- 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;
execute immediate 'declare x varchar2(22); begin x:=1; x:=2; dbms_output.put_line(x); end;';
-- 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;
execute immediate 'declare x varchar2(22); begin x:=2; x:=1; dbms_output.put_line(x); end;';
-- 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;
execute immediate 'declare x varchar2(22); begin x:=1; x:=2; dbms_output.put_line(x); end;';
-- 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;
execute immediate 'declare x varchar2(22); begin x:=2; x:=1; dbms_output.put_line(x); end;';
-- Aliasing in procedure call
tmp_from_first_to_last(x, x);
dbms_output.put_line(x);
tmp_from_last_to_first(x, x);
dbms_output.put_line(x);
-- Twice aliasing in procedure call and execute immediate
-- execute immediate 'begin tmp_from_first_to_last(:1, :2); end;' using out x, out x;
execute immediate 'declare x varchar2(22); begin tmp_from_first_to_last(x, x); dbms_output.put_line(x); end;';
-- execute immediate 'begin tmp_from_first_to_last(:2, :1); end;' using out x, out x;
execute immediate 'declare x varchar2(22); begin tmp_from_first_to_last(x, x); dbms_output.put_line(x); end;';
-- execute immediate 'begin tmp_from_last_to_first(:1, :2); end;' using out x, out x;
execute immediate 'declare x varchar2(22); begin tmp_from_last_to_first(x, x); dbms_output.put_line(x); end;';
-- execute immediate 'begin tmp_from_last_to_first(:2, :1); end;' using out x, out x;
execute immediate 'declare x varchar2(22); begin tmp_from_last_to_first(x, x); dbms_output.put_line(x); end;';
end;
/
Will you agree that you made another "proof" of the un-documented belief, that arguments are applied and loaded in the order presented?
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.
So, argument aliasing is un-documented.
And, argument aliasing appears to be very consistent.
Mike
>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.
ReplyDeleteMike, 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.
> My point is that this choice would be incorrect even if by chance Oracle would show the company name.
ReplyDeleteal0, that is only reason why I marked this choice incorrect.
But it is definitely not what Steven still means.
>Mike, many people believed that group-by without order-by do sort.
ReplyDeleteVitaly, 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.
Oleksandr
Why I am insisting on rescore? It is principal for me.
ReplyDeleteI 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.