22 June 2010

Questions raised about 21 June Quiz - what do you think?(409)

The question was: "I declare a nested table or varray of numbers. I then want to assign the number 10 to index value 1, as in:
my_collection (1) := 10;
"Which of the statements correctly describe a step I must take after declaring my collection and before I can successfully execute the above statement?" And we at PL/SQL Challenge claimed that the only correct answer was: "You must initialize the collection by assigning it a value returned by a call to a function provided by Oracle that has the same name as the type from which the collection was declared." Two players wrote to question this viewpoint as follows: 1. "Hi, I disagree with your answer to yesterdays quiz. You've checked the "You must initialize...." answer as correct. I think it's not a complete answer, because it lacks the the use of "extend". I can bypass using extend by using a dummy value in the constructor. But that is IMO such a bad practice that I don't consider it a correct way to do it." 2. "Do we must initialize nested table? Yes. Do we must do it by calling default constructor? No. We can call another function returning initialized collection.We can construct a collection using bulk collect. So we indeed do not must call default constructor.Although it's a typical way we have more choices.That is why i consider your answer literally incorrect." Here is our answer to these objections: 1. We never said that calling a constructor function was the only thing you had to do before you could execute the assignment. It is, certainly, "a step I must take." The player is right; a call to the EXTEND will almost always be needed as well. But that doesn't make the choice incorrect. 2. The player is right. You could call your own function to initialize the collection; you could also avoid the need to call a constructor by populating the collection with a call to BULK COLLECT. Having said that, please refer back to this assumption which appears before you take each quiz: "The session and the environment in which the quiz code executes has enabled output from DBMS_OUTPUT, and can reference only those datatypes, programs and database objects defined in the context of the quiz or are available in a default installation of the Oracle instance." This means that unless we explicitly say that we have created in the context of this question another such function, it does not exist and you cannot assume its use in the answer. As far as BULK COLLECT goes, in general, that will allow you to avoid the need to call a constructor, but in this particular case (performing a scalar assignment), that does not apply. So we believe that while these are interesting and informed questions to raise about the 21 June quiz, all your answers were scored correctly. What do you think?


  1. Hi Steven,

    I share your view on the correctness of the answer now. I am a bit surprised by the reasoning though. I thought that you would go with "type(n)" constructor. This explanation seems too "lawish" to me.

    Either way I encourage you to pay attention and try to avoid these ambiguities in the future (being a parent and an occasional teacher I know how incredibly difficult task that is :-)).

  2. I think that the answer is not correct becaus:

    1. it's not the only statement needed unless you pass at least one parameter to the constructor function

    2. as another one mentioned you can also use BULK COLLECT to initialize the collection.

    In this case you only need to execute:
    and you have a collection where the element with the index 1 exists.

    So my opinion is that you must call the constructor function plus the Extend() method or you don't need to call the constructor function and can use the SELECT statement given above.

    Regarding the "We never said that calling a constructor function was the only thing you had to do ...": that would mean that it could possibly be nessesary to execute 20 lines of code or call 50 procedures and then the statement to fulfill a quiz could be executed.

  3. I too felt like player-1 regarding the use of EXTEND and did not select any of the options as correct.

    I consider that as my misinterpretation to the question.

  4. When I saw the question, I admit that I was expecting that one of the answers would be a call to .extend and it seemed odd that it wasn't there. If there had been a .extend answer I may have just marked that one as correct and not the constructor one.

    So while I agree with your logic regarding this question/answer, I think it would have been a better challenge had it included the extend answer.

    But compared with some of the World Cup refereeing, you're doing great :)

  5. Thanks, Gary. That means a lot to me (as a former soccer referee for kids!).

    Michal, you ask us to avoid ambiguities. There was no ambiguity here. If anything, the complaint is that it is too specific and "picky."

    The verbiage I chose deliberately to use was "a step" - not "the step" or "the only step".

    I tried very hard to make sure my language did not have ambiguity and I think I succeeded - but perhaps at the expense of easy comprehension.

    Dang, this is tough! So, onward we will puzzle and quizzle and hopefully you will not feel dissatisfied in the future!

    Warm regards, SF