24 July 2011

Objections to PL/SQL warnings quiz in Q2 2011 playoff (4483)

The Q2 2011 playoff tested players' knowledge of Oracle's compile-time warnings, in particular some key warnings added in Oracle Database 11g Release 2. In this quiz, we showed a program unit and asked how many warnings would be displayed by Oracle.

Players had the following objections:

"I hope I never see another question like this again! I can't imagine how anyone could have gotten this one correct without actually typing in and running it. I would have preferred if the question was in the form "which of the following warnings would be shown", and the answers would include these and perhaps other unrelated warning messages."

"I think that this quiz was somehat unfair towards players that are using 11gR1. As by my test performed with 11gR1 (, there are only 4 warnings generated, namely: PLW-7203, PLW-5004, PLW-6009 and PLW-5005. The additional two ones, specific for 11gR2 are even not mentioned in the PL/SQL Documentation of 11gR2, nor in other sources, like your book PL/SQL Programming 5-th Ed, so those who are NOT using this version were unable to know about it. By the way, there is no consistent source that offers a complete list of all these warnings, for any version. So, I think that for the sake of fairness, this quiz should be rescored and considered as correct any answer that fits the previous releases, 11gR1 and 10gR2. I did not check 10gR2, it may be the same as 11gR1."

I can see why this quiz would have been a source of frustration and perhaps irritation, but I don't see why I should rescore.

The Oracle documentation of every version offers an Error Messages book, which contains (so far as I know) a comprehensive list of warnings. This quiz specified 11.2 as the minimum version.

So to answer the question correctly, you needed to (a) read the code and identify fairly obvious problems with it; (b) be generally conversant with the way compile-time warnings work; and (c) either be aware of the new warnings added in 11.1 and 11.2 or scan the documentation for verification of your analysis.


  1. Hello Steven,

    I am pretty sure that the main source of information that developers are using for these warnings is (still) the PL/SQL documentation set
    + at most the Advanced Development guide,
    which,even if you HAVE READ the 11gR2 guide,
    PRIOR to having ever touched it "with hands on",
    you COULD NOT find any word exactly about those warnings that were specifically added to this version ...

    I can hardly believe that somebody "is learning by heart" the complete warning list from the Errors reference book ...

    Each PL/SQL documentation only offers a description of the feature and some examples of the more or less wider used warnings,
    including your PL/SQL Programming book,
    and NOT an exhaustive list of ALL the warnings.

    To be completely sincere, I can even find no good enough reason to add a warning like the AUTHID one ...
    Regardless of the PLSQL_WARNINGS feature,
    if one was supposed just "to observe the code and object about what looks to him as incorrect",
    I don't think that any PL/SQL programmer in the world could have been entitled to object on the fact that AUTHID was not specified, just out of
    a "best practices" point of view ...
    So, without DEFINITELY KNOWING that this warning was added in 11gR2, no PL/SQL knowledge or "common sense" would have "directed you" towards it ...

    In fact, if the number of those warnings will be continuously increasing, there will be less and less chances that people will start using them effectively ...
    well, but that's another issue ...

    You are right in that the assumption of the quiz was 11gR2, but, exactly because
    1. most of the players are probably NOT using yet that version
    2. the knowledge of the feature itself is not prejudiced in any way by (only) being familiar with it in one of the previous versions

    I think that the quiz could have been asked "on the same level of difficulty" for 11gR1 or even 10gR2, to give everybody at least a theoretically equal chance to solve it correctly.

    This is why I think it is fair to consider as
    correct ANY answer that completely matches
    ANY of the versions, of course, if that was the single one chosen by a player.

    Thanks a lot & Best Regards,
    Iudith Mentzel

  2. I would expect that PL/SQL developers interested in developing their skills would have looked at 11gR2. Especially contractors/consultants or those who don't know where they will be working next.

    For those playing with 11gR2, the warnings that crop up on 95% of objects do get burned into your head quickly.

    Particular warnings can be enabled/disabled on an individual basis (at session or package level).


  3. Hi

    First I would like to say that in the playoff "we" the players will almost for certain not read that section and that the version (if distinct from the "default one" should, in the future, be in the question text.

    As for the subject at hand: normally a insterested developer would read the "what's new in PL/SQL" section on the new versions documentations and in that section there is not a mention to the fact of more warning messages (maybe I'm mistaken).

    If a somewhat similar question was already asked in the "daily quiz" I'm sure that "we" would be alert to that fact, even if the warning messages (are at least for me) not not usefull than the warnings I get in SQL Navigator (the same as Toad). As Iudith said some warnings are "strange" to the normal user and can be "too much info".

    That said: congratulations to all participants and, if you (the reader) are not playing "PL/SQL Challenge" give it a try and I bet that you will learn something new each month.

  4. Gary: "Particular warnings can be enabled/disabled on an individual basis (at session or package level)."

    We know that (thanks for the link), but maybe the default warning list should be a clean one and the developer would turn on the more "advanced" ones if needed (like the PLW-05018 and PLW-06008).

    BTW, I think some others should be compiler errors and not just warnings like PLW-05008, PLW-05009, PLW-05010, PLW-05013,PLW-05014, specially because they are due to bad direct compilation instructions!

  5. PLW-05018: ...
    > This is one of those "nuisance" warnings

    It certainly is and one of the reasons, why many warnings are disabled in my environment.

    PLW-06009: ... OTHERS handler does not end in RAISE ...
    If you follow best practices and encapsulate logging and errorhandling (including a raise) in a custom package you get this for every OTHERS.

    PLW-05005: subprogram ... returns without value
    For each function with an Exception handling where you don't write a RETURN statement, simply because you want to raise an error. Or is it a best practice to write a RETURN after a RAISE just to suppress the warning ;-)

    PLW-07203: parameter ... may benefit from use of the NOCOPY compiler hint

    All of them together will hide any useful warning because only the first n of them are displayed and the really interesting ones are not visible.

    The warnings are a good idea, but they still can be improved.

    As Filipes said, everyone should read the "What's new" section, but who will read the error book, to find out whether there is a new message? I for my share don't have the time to compare each document to the corresponding document of the previous version just to find the differences.

    And concerning the challenge question:
    (a) read the code and identify fairly obvious problems with it;
    I don't think a missing AUTHID is fairly obvious
    (b) be generally conversant with the way compile-time warnings work;
    generally yes
    and (c) ... scan the documentation for verification of your analysis.
    Can you show me someone who is able to do so in the limited time the challenge allows?

    I worked some times as a teacher for math and physics, but this is a kind of question I would never use, simply because it does not account for subsequent errors(?), meaning if you have one error the whole answer is wrong, even if in this case you knew 5 out of 6 possibilities. And you might even get the correct number though you choose the wrong warnings.

    Maybe the qustion could have different combinations of warnings and you had to find out the correct ones.

  6. Hello All,
    If we already arrived to the documentation,
    then please note that even the Error reference
    for 11gR2 does have many errors ...

    For example, the description of PLW-05000 is the same as PLW-05003, and PLW-06006 is the same as PLW-06007 ...

    There are also some descriptions that look simply incorrect, like PLW-05009, whose description says that "... pragma INLINE cannot appear immediately prior to a declaration", which is definitely not the case.
    And, even if yes, syntax errors should have been treated as errors and cause the compilation to fail, and NOT as warnings.

    I think that Oracle went this time a little bit too far ... and the best example is the AUTHID warning.
    As long as AUTHID is the default and it is strongly recommended to be used in almost all cases, except for very special ones, and by almost all PL/SQL books, I really don't see why one would suppose that a warning is necessary if NOT having specified it explicitely ...

    Anyway, I think that the error reference IS NOT THE PLACE from where new features are supposed to be learned ... rather the opposite is true ...
    you learn things from the main documentation and from other books, where these warnings that made the difference between 11gR1 and 11gR2 DO NOT APPEAR mentioned at all, not to speak about examples ...

    All what Gary said is knowledge that I dare say ALL the playoff players do posses ...
    We all know that we can turn each warning individually into an error, and maybe this is the best usage that one can do with these warnings at all ...

    A good PL/SQL developer will probably never make
    sich mistakes in his code that a warning would be able to detect.

    The error reference guide itself recommends turning some of the warnings off if they become too annoying ... at least a strange way
    "to advertise a product" in the very same place
    where it is supposed to be first heared about ...

    If it already comes to knowing a version ahead of the time when you will maybe use it,
    then I think that 11gR2 has many more new features and much more essential ones to offer than knowing by heart all the warnings, especially those that Oracle itself will hardly find a good explanation for having introduced them at all ...

    So, I don't think that such a narrow issue of a few warnings more or less in one version or another should become such a central point in scoring a playoff quiz, in deciding between "white" and "black", between the "good" and the "bad" guys ...

    Thanks & Best Regards,

  7. Marcus, I agree that the lack of AUTHID is not an "obvious problem".

    So it is clear to me that a number of players are unhappy with this type of question, but I still see no reason to change the scoring and ranking.

  8. Hello All,

    Marcus put it very correctly, as only a teacher is able to, CHAPEAU !

    Not only that AUTHID is NOT an obvious problem,
    it is NOT A PROBLEM AT ALL and should NEVER
    have been "afforded the honor" of a warning ...
    As Marcus said, it is just misleading and annoying, driving serious developers away from this feature, and ... as we see, causing playoff problems also ...

    If warnings KNOWLEDGE was supposed by this quiz,
    then please all remember that the PLW-06018
    "overall" one was also only introduced in 11gR2,
    so, if one goes by the obviously wrong division by zero, it comes out that this was not so obvious prior to 11gR2 ... so did things become "obvious" only in this last version ?

    Maybe it is ok to ask quizzes which suppose 11gR2, but if the feature involved IS NOT SPECIFIC to that version and might change upside-down the answer of THE SAME QUIZ in a previous version, then objections are completely justified, in my opinion.

    In this case, maybe several choices like:
    n1 for 10gR2
    n2 for 11gR1
    n3 for 11gR2
    ( that is, each choice for a different version )
    could have been better, giving everyone
    "a chance to be there" .

    One way or another, this quiz will specifically leave a very bad taste to many players, even if not all of them will recognize it publicly ...

    Thanks & Best Regards,
    Iudith Mentzel

  9. Well, I am sorry about it leaving a bad taste, but I still don't see anything wrong with the quiz per se.

    It might irritate you, but you could answer it by looking at the code and looking at the list of warnings of figuring out which would be flagged by the compiler.

    It's not like you had to install 11.2; but you do need to be "current" on the warnings Oracle has implemented.

  10. "(...) but you could answer it by looking at the code and looking at the list of warnings of figuring out which would be flagged by the compiler."
    If it was a daily quiz I think that we could agree on that (we could use 10,20 minutes there with that question) but being in playoff with a time constraint I think that it was not very fair.
    But I accept the "judge" opinion.

  11. Hello All,

    Of course we will all accept the judge opinion,
    but the purpose of this blog was however to discuss the issue, in the hope to lower similar occurrences in the future ...

    Yes, looking at the code and applying your knowledge about warnings and, as Felipe said, maybe less using the documentation in the limited time frame of a playoff and for a different version that one is currently using, might have been a good enough scenario if there were SEPARATE CHOICES OFFERED FOR EACH WARNING, and not an overall count choice only, which is either good or bad, without having much importance whether you were wrong by 1, by 2 or at all.
    With an overall summary choice, it does not matter too much whether a wrong choice (in terms of 11gR2 only !) was chosen by a simple guess or by knowledge of the feature.

    Thanks a lot & Best Regards,
    Iudith Mentzel