09 December 2010

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

13 comments:

  1. I took the PL/SQL option to be about the use of varray types within PL/SQL, not about the ability to change DB type definitions from within PL/SQL. To me it seemed a perfectly valid assumption - however incorrect is has turned out to be.

    I did find the using "varray type" in three places and "varray" in the other two to be confusing. I think if the PL/SQL option had used "varray type" there would have been less opportunity for confusion or ambiguity.

    ReplyDelete
  2. > then you can quickly check all such choices as correct in future quizzes!

    Steven, are you sure that in the PAST there are no conflicts with this new "rule"?

    ReplyDelete
  3. Many times when you have non-code question for quiz, I find it very difficult to understand the language, and alway wander if you have any word to trick us. But then you have mentioned in the past, that you do noet intend to trick us with answer. I knew about 'EXECUTE IMMEDIATE' feature but decided to not choose that option, because I thought question was not about dynamic SQL. If you consider EXECUTE IMMEDIATE as valid option, then I wander what can NOT be done from PL/SQL block.

    ReplyDelete
  4. Hi!
    I have understood your point of view.
    Thank you for answer :)

    Best regards, macabre.

    ReplyDelete
  5. Steven,

    "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)

    A varray is an instance of a varray type declared as a variable. This means you can not change the maximum size of a 'varray' but a 'varray TYPE'.

    Therefore the choice "You can change the upper bound of a varray from within a PL/SQL block" should be marked as incorrect.

    Best regards,
    Hamid Talebian

    ReplyDelete
  6. I don't know Steven, I my mind your style leans more toward "deducing flavor" than "accepting as is". That could just be my problem of perspective however.

    I also have a problem with "know that "anything" can be done in a PL/SQL block with EXECUTE IMMEDIATE". Am I wrong in assuming that an EXECUTE IMMEDIATE causes a context-switch and therefore you would not technically be in a PL/SQL block any longer. Yes you are within on but not IN one. If not technically then I would say the "flavor" would no longer be PL/SQL :-)

    ReplyDelete
  7. Hamid,

    Yes, I was a bit careless with terminology there. It should have said "a varray type". I will fix that in the text of the choices.

    But by changing the maximize size of a varray type, I am also changing the maximum size of any varray based on that type, right?

    For example:

    DROP TYPE names_vat FORCE
    /

    CREATE OR REPLACE TYPE names_vat AS VARRAY (10) OF VARCHAR2 (80);
    /

    BEGIN
    EXECUTE IMMEDIATE 'ALTER TYPE names_vat MODIFY LIMIT 100 INVALIDATE';

    EXECUTE IMMEDIATE
    'DECLARE
    l_list names_vat := names_vat ();
    BEGIN
    DBMS_OUTPUT.put_line (
    ''Limit of names_vat after expansion = '' || l_list.LIMIT);
    END;';
    END;
    /

    ReplyDelete
  8. Hi Steven,

    No, Steven, I must disagree. I agree with Hamid.

    "But by changing the maximize size of a varray type, I am also changing the maximum size of any varray based on that type, right"

    The size of l_limit is set when the VARRAY is defined in the declaration part of the second block. The upper limit is not changed after it has been defined.

    In a single PL/SQL block you can not define a VARRAY and then change the upper limit afterwards, even if you (try to) change the upper limit of the underlying VARRAY TYPE.

    ReplyDelete
  9. And I am curious, Silverfox and Hamid: you marked that choice as incorrect?

    ReplyDelete
  10. I think a better wording for answers 3 and 4 would have been "You can increase the upper bound of a varray..." rather than "You can change the upper bound of a varray...". An attempt to _change_ the upper bound to be lower will result in an error.

    CREATE OR REPLACE TYPE names_vat AS VARRAY (10) OF VARCHAR2 (80);
    /

    Type created

    ALTER TYPE names_vat MODIFY LIMIT 5 INVALIDATE;
    /

    ORA-22324: altered type has compilation errors
    ORA-22328: object "ROLL"."NAMES_VAT" has errors.
    PLS-00728: the limit of a VARRAY can only be increased and to a maximum 2147483647

    ReplyDelete
  11. Please disregard that last comment. It doesn't matter how anyone answered (I hadn't yet had breakfast and was getting a bit grouchy).

    I was sloppy in my question text and should have included the word "type." I will make that correction in the text and give everyone who marked "incorrect" for that choice credit.

    ReplyDelete
  12. Hello All,
    I think that SilverFox is right and raised
    a good point.
    If you have a type defined as:

    CREATE TYPE myvarr AS VARRAY(10) OF NUMBER
    /

    and then execute the following block:

    DECLARE
    l_arr myvarr;
    BEGIN
    l_arr := MYVARR(1,2,3);

    EXECUTE IMMEDIATE 'ALTER TYPE myvarr MODIFY LIMIT 25';

    DBMS_OUTPUT.PUT_LINE('Now limit is '||l_arr.LIMIT);
    END;
    /

    The output will be:

    "Now limit is 10".


    The reason is completely clear:

    The block was compiled in its entirety
    when the type was still defined as VARRAY(10),
    and, even if AT RUN TIME we altered the varray type to increase its size, the varray variable
    still kept its original declared limit fixed during compilation.

    It is even strange that Oracle does not complain in any way at runtime that we are performing DDL on an object (the VARRAY TYPE object itself)
    that is currently in use by a session.

    For example, if we recompile a package while
    it is being used, then at least the package state is lost and some runtime error will happen
    to the session that is using it ...

    This is maybe the finest thing about Oracle,
    that at the very moment when you consider
    something to be trivial and completely understood, you suddenly have some good opportunity to think over it again :) :) :).

    By the way, maybe a nice additional choice to this quiz would have been exactly a block like the above one and asking what would be the output of it at runtime ...
    (well, it discloses the answer to the ALTER TYPE choice, but that's another thing ...).

    Steven, as you see, we are ready to volunteer in
    creating questions, not just in answering them !

    Be optimistic, THE PL/SQL CHALLENGE HAS A BRIGHT FUTURE AHEAD !!!

    Best Regards,
    Iudith

    ReplyDelete
  13. Good idea, Judith, about the variation on this quiz. In fact, almost every time I come up with one quiz, another 5 or 6 immediately make themselves visible.

    Then I just have to find time to write them - BUT in the next release of the site (which should be available for beta testing next week), you will be able to submit full-blown quizzes for us to review and add to the repository.

    I hope to get lots of really good quizzes this way.

    Cheers, SF

    ReplyDelete