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:

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?

Questions regarding quiz on object types (7 December quiz) (1762)

A player wrote the following: Hello Steven, Just a few remarks regarding the last quiz, that I would like to hear your opinion on: 1. Regarding the creation on an object type containing STATIC subprograms only, without any attributes: While it is right that such a type is practically substitutable with a PL/SQL package, as far as I know, in Java this is allowed. Since the object oriented features in Oracle usually attempt "to mimic" the constructs and behavior of other object oriented languages, I don't see any reason for such a construct not being allowed in Oracle as well, at least technically, even if practically it is of course of a limited use. The only limitation might be that such a type should be NOT INSTANTIABLE (and therefore not storable in a database column, a.s.o.). In fact, maybe it might contain MEMBER procedures also and still being without attributes. 2. Regarding the restriction that the methods declarations should always come after the type's attributes: I think that this is also a superfluous restriction. While the order of the attributes is essential, just like the order of the columns in a table, for example for clearly determining the order of the arguments in calling the type's constructor, the order of the methods declarations seems to be not relevant and they can probably come as well in any position inside the type definition, similar to the order of subprograms in a package. This would be more similar to Oracle's general behavior of allowing independent clauses in any CREATE statement to come in any order in most cases. As a quick example, in a table definition you can put constraints before column definitions, like in the following: create table mytab (constraint mytab_pk primary key (x), x number not null); It is true that a reasonable person will probably NOT code things like this, and NONE of the examples in the different documentation sources will ever use such an awkward syntax, but this means that you have practically only 2 ways of knowing that this DOES NOT work: a. either to have tried it out by yourself (which probably nobody did, well ... at least until this quiz ... ) or b. to closely follow-up the complete railroad syntax diagram. I am not sure whether this restriction is explicitely mentioned in the documentation. I would love to read your opinion on these issues. I am quite busy at the moment and do not have time to respond, so I thought I would post this for others to consider. Regards, Steven

Misleading terminology in 8 December quiz on varrays? (1763)

The quiz tested your knowlege of the ability to adjust at runtime the maximum number of elements allowed in a varray. One player felt my use of the phrase "upper bound" was ambiguous: 'In todays question we have a problem about term upper bound, because there's a difference between upper bound and maximum size, as pointed in Oracle Documentation as "A varray has a maximum size, which you specify in its type definition. Its index has a fixed lower bound of 1 and an extensible upper bound." (PL/SQL User Guide, Understanding Varrays) So in some choices we can understand which variant of that two is meant, for example in choices … can only be changed… and … using the ALTER TYPE… almost obviously upper bound means maximum size. But in other cases meaning of "upper bound" is not so clear.' To which I reply: an interesting point, but I think that there is no ambiguity in the context of this question, which states: "The varray is one of three types of collections in PL/SQL, and it is the only kind of collection for which you specify an upper bound on the number of elements that can be placed in the collection variable declared on a varray type." In other words, I clearly define "upper bound" as referring to the number of elements. So I do not see that there is any reason to change the scoring of the quiz for this reason. A couple of other players expressed concern about my scoring as correct the statement "You can change the upper bound of a varray from within a PL/SQL block." Here is the most detailed explanation: "I think that one of the choices of today's quiz is a little bit ambiguous, namely the choice that says: "You can change the upper bound of a varray from within a PL/SQL block". The ambiguity is that, if we consider the EXECUTE IMMEDIATE statement, then ANYTHING that can be done at all out of a PL/SQL block can also be done inside a PL/SQL block, be it changing a varray's upper bound or any other DDL operation and even beyond ... The "flavor of the whole question" rather suggests that this WAS NOT the expected way of thinking, otherwise this choice "fails back" to the other one, that speaks about ALTER TYPE. Instead, I think that the intention was more probably to underline that the effective upper bound of a varray variable declared in a PL/SQL block cannot be changed beyond the defined upper bound of the SQL type by using pure PL/SQL statements, like for example l_var.EXTEND, a.s.o. I feel that some different wording of this choice would have been welcome, maybe something like "If L_ARR is a pl/sql variable of a varray type then L_ARR.limit cannot be changed in a PL/SQL block". Am I wrong ? I wonder what others will think of this." To which I reply: watch out for reading too much into the choices. If faced with a choice between trying to deduce the "flavor" of the question and accepting what the choice says "as is", you should always go with the latter approach. I really had in mind nothing more than the fact that you can use EXECUTE IMMEDIATE to also change the limit of the varray type from within a PL/SQL block. Look on the bright side: if you are comfortable enough with Oracle and PL/SQL to know that "anything" can be done in a PL/SQL block with EXECUTE IMMEDIATE, then you can quickly check all such choices as correct in future quizzes! Cheers, Steven