BEGIN
IF 1 = 2
THEN
EXECUTE IMMEDIATE 'call salespkg.calc_total(''A'')';
END IF;
END;
Then this block will not raise an error, even though it "calls" the program.
2. "I do not agree with the answer "Any block of code that includes a call to salespkg.calc_total will result in Oracle raising an error when executed", especially the words "any" and "when executed". Of course, an anonymous block will give a runtime error. But when I include this call in a procedure or package (a "block of code" as well), I will never be able to execute this, because it won't even compile (PLS-00307)! So, in this case, the block of code will NEVER result in an Oracle (runtime) error, because it will never be executed. Because of that, I scored this answer as incorrect"
Here is my response:
1. Well, look at that! A player found a "hole" in my statement. I said "includes a call" but I never said that the block had to actually execute the subprogram. Further, he hides the call to the ambiguously overloaded subprogram inside a dynamic PL/SQL block so that the static block with compile. Very ingenious - and irritating. :-) It is so ingenious, in fact, that I am entirely loath to rescore everyone's answers as correct on this point. I will grant that I had a "hole" in my statement and fix that in the text. I will give this player credit for a correct answer on this statement - and I will do so for anyone else who chose "incorrect" for this option because of this - you will need to submit a request through Feedback on the website. Finally, this player (_Nikotin) will receive an O'Reilly Media ebook as a reward for finding a way to maneuver around the language of this statement.
2. I do not agree with this objection. The bottom line is that you cannot execute a block that contains a call to (and that executes) any of these subprograms. Either you cannot run that block because it fails to compile due to an ambiguous overloading or because it is calling a subprogram that is invalid (due to an ambiguous overloading). Either way, you cannot execute that block.
Your thoughts?
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.
10 December 2010
Every block will fail with an error? Not so, say players. (1764)
In the 9 December quiz, I test your knowledge of subprogram overloading and the problem of ambiguous overloading.
One choice, marked as correct, stated:
"Any block of code that includes a call to salespkg.calc_total will result in Oracle raising an error when executed."
I received two objections to this statement:
1. What if, a player asked, my block looked like this:
Subscribe to:
Post Comments (Atom)
My opinion? I don't agree with the fact that there is a call in that program. There is a call to EXECUTE IMMEDIATE that has a string. That string evaluates to a call on runtime but the program does not know that when the code compiles! That is by our interpretation that we know that it is a call. And our interpretation does not count, I assume.
ReplyDeleteI know this is a tricky discussion. It is all about semantics on run time, compile time and in the readers mind.
Conclusion, don't rescore (even if I have it wrong?)
The first objection definitely does NOT deserve any reward because the executable code does NOT contain a call to the procedure salespkg.calc_total.
ReplyDeleteWim de Lange has a valid point, the "CALL" to salespkg.calc_total is not in the PL/SQL block, it is in a string passed to an EXECUTE IMMEDIATE. Because the player made use of the SQL CALL statement, this string is not a PL/SQL block. Even if the string is replaced with the functionally equivalent 'BEGIN salespkg.calc_total(''A''); END;' anonymous block, the call would only exist if the EXECUTE IMMEDIATE statement were executed at runtime and the PL/SQL engine could successfully compile the code in the string (which it could not since the overloading is ambiguous).
Even if we were to accept the contents of constant strings passed to EXECUTE IMMEDIATE as being part of the PL/SQL block (which I do not), the executable code still does not contain a call to salespkg.calc_total. Because the expression "1 = 2" always evaluates to FALSE, the code within the IF is unreachable and thus the call to the offending procedure will never be attempted. Although the original phrasing did not explicitly state that the call had to be executed, the requirement should have been understood by anyone as implicit. Giving any player credit for a correct choice based on the argument that an unreachable "call" to the procedure appears in the source code is a disservice to the other players.
Note to players: I stated in my original post that I would rescore for _Nikotin and anyone else who lays claim to this analysis (one person has already done so). I should have held off on doing so until other players had a chance to provide feedback.
ReplyDeleteI find the Wim's and JHall62's arguments persuasive (well, or at least giving me enough backbone to say: "No, you should have understood that I meant you are calling the subprogram.").
So...I am definitely considering NOT applying a rescore. Keep the comments coming!
Thanks, SF
Hi!
ReplyDeleteSteve, but the ebook will be received by player _Nikotin, will not it? At least, he made you to think about the quiz. ;)
Alex
Hello All,
ReplyDeleteWhat I rather find interesting about this quiz is Oracle's behavior itself,
rather than the specific choice in question.
The fact that the package spec and both can be compiled without errors looks to be more in
contradiction with the Oracle PL/SQL Language reference, that says the following:
"PL/SQL lets you overload nested subprograms, package subprograms, and type
methods. You can use the same name for several different subprograms if their formal
parameters differ in name, number, order, or data type family. (A data type family is a
data type and its subtypes ...).
It is not very clear what Oracle does mean by "lets you overload", does it mean
"lets you compile" or "lets you execute" ?
VARCHAR2 and CHAR belong both to the "CHAR datatype family" (as shown the same documentation),
so I would rather expect the compilation of such a package to fail.
Instead of this, it fails at runtime only, but due to a compilation error, PLS-00307,
which is rather unusual for Oracle.
This reminds me of a previous quiz, in which there were also 2 overloads of the same program,
one having a single parameter and the second having two parameters, with a default value
for the second parameter.
In such a case, maybe the compilation can be accepted as successful because there can be
at least one successful call, namely a call that specifies both parameters.
But in our case, no valid call is possible, therefore I still wonder how is it
that Oracle still allows the compilation to be successful.
I am very curious what do you think of this aspect.
Regarding the specific choice in discussion, maybe a less ambiguous wording would have said
"Any block of code that EXECUTES a call to salespkg.calc_total ..." instead of
"Any block of code that INCLUDES a call to salespkg.calc_total ...".
Thanks & Best Regards,
Iudith
Ok, I admit that my comment looks pedantic, but this is because I was surprised by 08 Dec quiz with solution that uses native dynamic sql to execute ddl, and so I expected that you might have had in mind something like that. In the light of that, I would suggest that you take my point and give me the rescore :)
ReplyDeleteMy interpretation of "Any block of code that includes a call to salespkg.calc_total will result in Oracle raising an error when executed." was that the "when executed" and "raising an error" referred to the call rather than the block of code.
ReplyDeleteNikotin's variation does allow the exception to be caught and ignored such that the block itself could be run without the block raising an exception.
Iudith, I agree that it is strange that Oracle permits such a package to be compiled when there is no possible way to call any of the overloaded procedures. It seems to me that trapping ambiguous overloading could be caught easily (for most cases) by comparing a procedure’s type signature (reduced to the type family instead of using the specific subtype) and it’s parameter names with those of any previously declared procedures with the same name. At the very least a warning could be issued. My guess is that Oracle only checks when attempting to compile a calling program in order to be consistent and to handle less obvious instances such as the following:
ReplyDeleteCREATE PACKAGE ambiguous_overloads IS
PROCEDURE overload (p1 NUMBER, p2 VARCHAR2);
PROCEDURE overload (p1 VARCHAR2, p2 NUMBER);
END ambiguous_overloads;
/
CREATE PACKAGE BODY ambiguous_overloads IS
PROCEDURE overload (p1 NUMBER, p2 VARCHAR2) IS
BEGIN
NULL;
END overload;
PROCEDURE overload (p1 VARCHAR2, p2 NUMBER)
BEGIN
NULL;
END overload;
END ambiguous_overloads;
/
The overload procedures have different signatures, although the parameter names match. One can successfully call overload(1,'1') or overload('1',1); however, if one attempts to call overload(1,1) or overload('1','1') , then Oracle will raise the PLS-00307 exception. It is reported as a compilation error because that is what it is. When attempting to execute an anonymous block outside of a stored procedure (e.g., from command line SQL*Plus or an EXECUTE IMMEDIATE statement), the failure is not occurring at runtime but when the code is being compiled/interpreted in preparation for execution.
_Nikotin’s reference to the use of native dynamic SQL in the explanation of the December 8 quiz ignores a significant characteristic of the EXECUTE IMMEDIATE statement in the December 8 quiz: it will be executed. The string in _Nikotin’s example could be replaced by any string without altering the behavior of the block since it can never be referenced by any executing code.
Gary, _Nikotin’s code doesn’t catch the exception, in fact, it doesn’t do anything except skip to the end. Even if it did catch the exception, that is not the same as Oracle not raising an exception.
Some comments on above.
ReplyDeleteSteven Feuerstein said:
> I said "includes a call" but I never said that the block had to actually execute the subprogram.
>well, or at least giving me enough backbone to say:
>"No, you should have understood that I meant you are calling the subprogram."
Yes, it's obvious that we can't actually execute the subprogram without pls-307 error in the error stack,
so I suppose that it doesn't happen in the PLSQL block.
jhall62 said:
>the executable code still does not contain a call to salespkg.calc_total.
>Because the expression "1 = 2" always evaluates to FALSE,
>the code within the IF is unreachable and thus the call to the offending
>procedure will never be attempted.
Ok let's consider not so simple condition for IF. We can use reference to
other code like package or dbms_random. So it can depend on stars placement
if we will get exception or not. The choice states "when executed"; however,
I would rather argue that it happens in every execution.
jhall62 said:
>Although the original phrasing did not explicitly state that the call had to be executed,
>the requirement should have been understood by anyone as implicit.
If we suppose that the call has to be executed we will get pls-307.
One more interpretation for choice solution :)
ReplyDeleteSQL> begin
2 execute immediate 'call salespkg.calc_total(''A'')';
3 exception
4 when others then
5 dbms_output.put_line('Procedure salespkg.calc_total was called, but no error raised from block');
6 end;
7 /
Procedure salespkg.calc_total was called, but no error raised from block
PL/SQL procedure successfully completed
I'm surprised that, if "if 1=2" should be considered a "hole", no one suggested:
ReplyDeletebegin
begin
EXECUTE IMMEDIATE 'call salespkg.calc_total(''A'')';
exception
when others then null ;
end ;
end;
/
would "not raise an exception" because it does not allow the exception to escape the block.
Hopefully, if I were to seriously raise such an issue, I would NOT get a re-score...
A player who was unable to post asked me to offer the following thoughts:
ReplyDeleteAt the end of the day, the scoring/rescoring is not important, but this discussion is certainly leading to some interesting comments.
-- From 12/08/2010 quiz, marked as a correct statement.
"You can increase the maximum size of a varray type from within a PL/SQL block."
-- Verification code included with question answers.
/* Change the limit inside a PL/SQL block*/
BEGIN
EXECUTE IMMEDIATE 'ALTER TYPE names_vat MODIFY LIMIT 200 INVALIDATE';
END;
/
-- From 12/09/2010 quiz, marked as a correct statement, but up for debate on this blog entry
"Any block of code that includes a call to salespkg.calc_total will result in Oracle raising an error when executed."
-- code example from this 12/09/2010 question blog entry
BEGIN
IF 1 = 2
THEN
EXECUTE IMMEDIATE 'call salespkg.calc_total(''A'')';
END IF;
END;
Based on the current scoring of these two answers, I would argue that the following contradictions exist.
Based on the answer from the 12/08/2010 quiz, operations included within a Execute Immediate statement are considered to be within a PL/SQL block. Hence, it is possible to modify the limit of a type within the PL/SQL block and this answer is marked correct. Following from this, in the 12/09/2010 question, the call to salespkg.calc_total within the Execute Immediate statement would be considered to be within the PL/SQL block. (I would suggest that this does not depend on whether the statement is executed at runtime) It is possible to run this block successfully without encountering an error, meaning that error will not always be encountered.
Alternatively, suggesting that the call to salespkg.cacl_total is not a part of the block would suggest two options. option 1: Since the call was not executed due to the 'if 1 = 2', inclusion in a block is dependent on whether the line is actually executed. I would hope that we can agree that this is not the case, as any code with if..then...else would indicate that the portion not executed is not included in the block. or option 2: Statements executed within Execute Immediate are not part of a block. This contradicts the 12/08/2010 quiz answer although I don't think anyone would argue the 12/08/2010 answer was incorrect. Either way, both result in contradictory results, showing inconsistent definitions/scoring.
Thus, the code example presented in the blog entry should provide the example to mark this answer incorrect, resulting in rescoring.
The only other reasoning I can come up with is that "...includes a call..." means something along the line of "is executed as a part of the block" rather than the code just being present in the block. That would lead to a wording interpretation, a discussion I won't enter into and would accept the scoring as is.
And totally without execute immediate:
ReplyDeletecreate database link selflink
connect to *** identified by ***
using ***;
CREATE OR REPLACE PACKAGE salespkg
IS
/*PROCEDURE calc_total (reg_in IN CHAR);*/
PROCEDURE calc_total (reg_in IN VARCHAR2);
END salespkg;
/
CREATE OR REPLACE PACKAGE BODY salespkg
IS
/*PROCEDURE calc_total (reg_in IN CHAR) IS
BEGIN DBMS_OUTPUT.put_line ('Region :' || reg_in); END;*/
PROCEDURE calc_total (reg_in IN VARCHAR2) IS
BEGIN DBMS_OUTPUT.put_line ('Region :' || reg_in); END;
END salespkg;
/
create or replace procedure caller_for_calc_total is
begin
salespkg.calc_total@selflink('A');
exception
when others then
dbms_output.put_line('Hello from handler! Not rised '||SQLCODE);
end;
/
CREATE OR REPLACE PACKAGE salespkg
IS
PROCEDURE calc_total (reg_in IN CHAR);
PROCEDURE calc_total (reg_in IN VARCHAR2);
END salespkg;
/
CREATE OR REPLACE PACKAGE BODY salespkg
IS
PROCEDURE calc_total (reg_in IN CHAR) IS
BEGIN DBMS_OUTPUT.put_line ('Region :' || reg_in); END;
PROCEDURE calc_total (reg_in IN VARCHAR2) IS
BEGIN DBMS_OUTPUT.put_line ('Region :' || reg_in); END;
END salespkg;
/
begin
caller_for_calc_total;
end;
/
After that we can see
Hello from handler! Not rised -4062
PL/SQL procedure successfully completed
Just a quick reminder to keep this thread focused: the choice stated "Any block of code that includes a call to salespkg.calc_total will result in Oracle raising an error when executed."
ReplyDeleteThe issue is not if an exception would escape unhandled from the block, only that Oracle will raise an error.
So demonstrating that you can trap an exception raised in calling the program is irrelevant to the quiz.
The only problem I see with this choice is in the phrase "that includes a call". As Iudith suggests, I should have more clearly said "that executes".
It has been demonstrated that you can "include" a call in the block, not execute it, and then Oracle doesn't complain.
Several players have argued that code executed as a dynamic PL/SQL block from another, static block is not executed in that static block.
I do not plan to draw that distinction (as one player pointed out, that would be a contradiction with a recent quiz). Yes, that dynamic block executes at the schema level, in terms of resolving references to objects within the dynamic block. But generally I will consider any code written and executed in the block to be a part of that block.
Given the ambiguity of "includes a call", I will rescore for two players. I don't think this will be a disservice to other players, though I think we can all agree that it is a finely-drawn distinction.
Well, there's nothing new with that in the PL/SQL Challenge. We've been doing a lot of very fine distincting.
Have a fine weekend, everyone!
SF