16 June 2011

Penalizing players for bugs or undocumented behavior? (2906)

The 15 June quiz tested your knowledge of the ability to pass arguments to parameterized cursors.

We scored as correct the following choice:
   l_filter   VARCHAR2 (100) := 'M%';

   CURSOR parts_cur (filter_in IN OUT VARCHAR2)
      SELECT *
        FROM plch_parts
       WHERE partname LIKE filter_in
       ORDER BY partnum;

   l_part     plch_parts%ROWTYPE;
   OPEN parts_cur (l_filter);

      FETCH parts_cur INTO l_part;
      EXIT WHEN parts_cur%NOTFOUND;
      sys.DBMS_OUTPUT.put_line (l_part.partnum);
   CLOSE parts_cur;
even though Oracle documentation specifies that a cursor parameter may only be of mode IN (not IN OUT).

As we noted in the answer: "This choice specifies IN OUT for the mode. Oracle documentation only shows the use of the IN mode, but Oracle doesn't complain about IN OUT, and it certainly doesn't change the value of the variable."

A player wrote with this complaint:

This choice was not supposed to work, both by Oracle PL/SQL Documentation below: http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/static.htm#CIHFAHFE http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/explicit_cursor.htm#i33637 ( the railroad schema explicitly shows IN parameter mode as the only legal one ). and also even more explicitly by what is stated in your book: PL/SQL Programming 5-th edition, Chapter15, Working With Explicit Cursor, Cursor Parameter Modes, page.491-492, where it is clearly stated that: "You cannot specify OUT or IN OUT modes for cursor parameters". This being said and completely logic, it is however strange that both choices [6938] and [6939] do compile without error! Regarding choice 6938, since it also returns the expected result ( !!! ). I think that this choice should be rescored for ALL players and considered as INCORRECT. The same is in fact required for choice 6939, but that one is incorrect anyway (it does not display the expected output), but in principle is as wrong as the previous one. I am pretty sure that many players will raise the same objection. This is not a feature not specified in the documentation, as we have encountered some in our quizzes, but clearly a behavior that contradicts the documented (and logical) one.

And my response: first, it sure is a drag when someone quotes your own book at you! :-) And in fact what this player has pointed out is a bug in my book. In fact, you can specify OUT or IN OUT. Oracle does not stop you from doing this; the documentation simply does not indicate that it is accepted by the compiler. So I will fix that in my book.

Second, if the only correct choice in this quiz was the IN OUT parameter (and it was a "one choice correct only" question), then you should all be upset with me; that would be a "trick question." It is, however, just one of several correct choices. So, yes, you are "penalized" if you simply checked the documentation and answered according to it, rather than actually trying it out before you submitted your answer. But now you also know this is possible and it may be something you look out for in your code - that is, it may be "lurking" in your code and should be removed.

And that is part of the point of the PL/SQL Challenge - to extend your awareness of the day-to-day realities of the language. In conclusion, I do not believe a re-scoring is in order.


  1. What!!!???
    "So, yes, you are "penalized" if you simply checked the documentation and answered according to it, rather than actually trying it out before you submitted your answer."
    So if you know the documentation by heart (I don't) and you are not taking the time to type all the code over (and check forever for typo's that are intentionly in the quiz or not) you are penalized because the software does not follow the specifications and documentation? Sorry, that is a stupid argument.
    You don't have to rescore, but the challenge is not about typing the code and testing it? Is it? There are too many versions which could differ in behaviour (even version Oracle Database 10g Release 2 is not specific enough if you know about subversioning that could lead to problems, we had)to state that only the valid answers are the code that is run. The answers with the OUT and IN OUT are clearly in error. If you correct your book (don't) clearly say that the OUT could in certain implementations not lead to compile errors, but that it is clearly wrong, makes no sense, not supported and in future versions could lead to problems.

  2. Hello Steven, All,

    Wim just took the words out of my mind !!!

    I also strongly advise AGAINST changing the text in your book.

    What is written there is completely correct and, let's not forget, people are reading books for learning things right and learning best practices, so there is no place for such a change.

    The version differences that Wim mentioned above are indeed an issue sometimes, just recently enough we had a quiz about the 11g RESULT_CACHE in which code that compiled in R1
    does not compile in R2, which means that the compiler itself is NOT perfect, and, hopefully,
    it is improving from one version to the next.

    We all know that there exist behaviors that are undocumented (or at least not in the main Oracle Documentation, but maybe only in White Papers on very specific topics), but also not denied by any documentation part.
    About these we can argue whether they can be
    used in the challenge, at which extension, in what flavor, a.s.o.

    But the specific issue of using cursor parameters as always IN mode only is an already fully clarified one, I think already from the very first version of PL/SQL and I don't see any reason that this behavior will change in the future.
    The fact that the compiler allows it through without a compilation error is simply a compiler flaw, that I am pretty sure Oracle PL/SQL product developers are not aware of ... they are humans after all, like all of us, but I strongly believe they should be warned about such a basic issue that might cause misunderstandings ...

    So, just as Wim recommended, your book should maybe add as an introductory sentence, on the first pages, something like:

    "While practicing PL/SQL, you may and will probably encounter strange behaviors, code that will compile successfully when you expect the opposite or code that compiles correctly but runs unexpectedly or does not run at all.
    In such cases, it is worth either to stick to the documented behavior or eventually file a compiler bug or even a documentation bug
    using Oracle's Customer Service for having that issue checked and resolved."

    Thanks & Best Regards,

  3. What I need to change in my book is the use of the "cannot" in: "You cannot specify OUT or IN OUT modes for cursor parameters".

    You can, in turns out, specify OUT or IN OUT modes. It's just that you should NOT.

  4. Choice 6938 for the June 15, 2011 quiz (2906) should be considered incorrect. Both the text and syntax diagram in Oracle Database PL/SQL Language Reference 11g Release 2 (11.2) unambiguously state that cursor parameters must have a direction of IN. (The same is true of the 10gR2 documentation.) The fact that all versions of the PL/SQL compiler from 10gR2 to 11gR2 accept IN OUT and OUT direction declarations for cursor parameters does not make such use valid PL/SQL. The ability to use these declarations is an attribute of the specific compilers, not of the PL/SQL language.

    The use of IN OUT looked suspiciously like a trap, so I tried some test code to ascertain its behavior. As a result of my test I selected that choice. Experience with the Challenge has shown that behavior is given more importance than the validity of the PL/SQL code. In some cases, such as when a compiler or runtime defect prevents valid PL/SQL code from performing as expected, this bias may be justified. Quizzes that exploit a compiler’s lax adherence to language specifications in order to achieve a result despite non-compliant PL/SQL code, however, reduce the Challenge to a typing or guessing contest. The choice that prompted this blog post is based on a compiler deficiency that a competent PL/SQL programmer is unlikely to ever encounter; in addition to violating the language specification, the use of IN OUT cursor parameters serves no logical purpose. Such tricks may be interesting, but they have no place in legitimate PL/SQL programming.

    Behavior contrary to specifications may change in future versions (this is more likely to occur for cases where valid PL/SQL is rejected than it is for cases where invalid PL/SQL is accepted). Most quizzes state a minimum instead of a specific version, thus any choice deemed correct should be expected to remain valid even with future versions.

    Steven, your book correctly describes Oracle’s specified behavior (the bug is with Oracle’s implementation, not your book). The only change I would make would be to add a footnote such as “The PL/SQL compiler incorrectly accepts IN OUT and OUT cursor parameters. These direction declarations are contrary to the language specification and must not be used."

  5. A player who cannot post to the blog sent this:

    For what it's worth I wanted to comment on this contentious issue - regarding accepting as correct an answer that contradicts the documentation (in this case, acceptance of OUT and IN OUT parameters by cursor declarations).

    The PL/SQL challenge has always been about education. A developer who is aware of the nuances and edge cases (such as bugs in the compiler) is that much better equipped for his job, so in this sense a quiz that tests one's knowledge of these bugs is helpful. This point has been made several times in the past for similar types of question.

    Also, if the test for an "unfair question" is merely that a player would always have to actually type it into a SQL*Plus session and execute it, then by that standard the quiz is not unfair - because I'd be surprised if there weren't at least some players who already knew about this behaviour of the compiler and answered accordingly.

    For everyone else, their getting this quiz wrong is an opportunity for them to learn something new about PL/SQL - the prospect of which is what keeps me coming back every day.

    I intend to continue to offer quizzes and choices in quizzes that both reflect the documented behaviors of the language and its reality.

    Cheers, SF