05 March 2013

Feedback on "Unnecessary Code" Quiz Rules, Please

On 1 March, we offered a new type of daily PL/SQL quiz: Write No Unnecessary Code.

As often happens with new types of quizzes, players had lots of excellent feedback (including corrections) on how the quiz was presented.

Since we plan to offer at least another dozen quizzes of this type over the next year or so, we want to make sure that all future "unnecessary" quizzes are unambiguous and easy to understand.

Here is our current idea for how to define the quiz:

Each choice contains a combination of DDL statements and PL/SQL blocks. A choice is correct if it does not contain unnecessary code. A piece of code is unnecessary if you can remove it from the choice without changing the result of running the remaining code in that choice. 

Rules for this quiz:
  • You cannot add anything to the choice. You can only remove text - and there are limitations to what can be removed:
  • PL/SQL is composed of delimiters, identifiers and literals. Removal of part of a delimiter or literal is not allowed. You can, however, remove an  entire word (text separated by a delimiter or whitespace) from an identifier. Examples: you cannot remove "PLS_" from "PLS_INTEGER"; you cannot remove single quotes from around a literal string; you can remove "ZONE" from TIMESTAMP WITH TIME ZONE"(the result may be invalid code, but it would be a valid removal).
  • You cannot remove whitespace or comments. 
  • If it starts as a PL/SQL block, it must end that way. You cannot, in other words, remove BEGIN and END; and leave in place some part of the statements in the block as individual SQL statements.
  • A change in the resources needed to execute the choice (CPU, memory, etc.) does not, for this quiz, constitute a change in the choice. In other words, if the removal results in a choice that is slower or consumes more memory, but otherwise accomplishes the same work (inserts a row, displays text, etc.), then the choice does contain unnecessary code.
The following block, for example, contains unnecessary code and should be marked incorrect:
After removing the NULL; statement, the block will do exactly the same thing it did before. But the  following choice should be marked correct, since if you remove the "NULL" or ";", the block will no longer be valid.

I believe these rules clarify all issues raised in the Commentary for the 1 March quiz. What do you think? Are there other scenarios you have in mind that would not be addressed by these rules?

Thanks! Steven


  1. I think that looks good. Addresses everything I brought up while still allowing the removal of "execute immediate" which you want to include as unnecessary. At least in cases as shown in the March 1 quiz.

  2. Hi Steven,
    I think it would be good if you clarified BEGIN/END removal rule. For example, in this code:
    The inner pair of BEGIN/END is obviously unnecessary, but would we be allowed to remove it in such quizzes?
    In the rule you mentioned the start and the end of a block. How about nested blocks?

    I would simplify the rule to "Removal of BEGIN and END is not allowed".


  3. Suppose the code has two inner blocks each of them trapping the errors.
    Should we consider that to be unnecessary code (we could have written only one block instead of two)?
    I think some rules have to be added about that as the possibilities are just too many.

  4. 2 remarks:
    1. I do not quite understand the next rule:
    A change in the resources needed to execute the choice (CPU, memory, etc.) does not, for this quiz, constitute a change in the choice. In other words, if the removal results in a choice that is slower or consumes more memory, but otherwise accomplishes the same work (inserts a row, displays text, etc.), then the choice does contain unnecessary code.
    Can you give some examples to it to clarify your means?
    2. Removal does not only mean removal of a complete statement, but can also be renmoval of a part of a statement.
    Kind regards, Marga.

  5. I think these rules are so clear. I absolutely like "Unnecessary Code" Quiz. This type of quizzes should be necessary. Because when we write code, we might write redundant codes. This is the best way to see people's common mistakes.

    By the way, I am also looking forward to see daily DBA quizzes.

    Warm Regards

  6. What is about the example from one of the choices:

    EXECUTE IMMEDIATE 'insert into ...';

    Are the EXECUTE IMMEDIATE and the two apostrophes unnecessary code or not?

    Kind regards, Niels

  7. What is stated here is well and good, as far as I can see.

    I would like a clarification added as to whether switching from dynamic SQL to static is allowed where possible (as was contentious in the first quiz). The other way around is clearly not allowed as it means adding characters, but I still think going to static SQL is as much a *change* to the code.

    We will surely see more discussion when the next few of these quizzes are played.

  8. This seems very clear to me. Good clarification.

  9. Dalibor, regarding your recommendation that 'I would simplify the rule to "Removal of BEGIN and END is not allowed"', I do not agree.

    If my block contains a nested block, I should be able to remove the entire nested block if it does nothing:


    My proposed rules states: "If it starts as a PL/SQL block, it must end that way."

    Removing a nested block leaves the rest of the anonymous block as a PL/SQL block. Removing the outer BEGIN-ENDs do not.

  10. Cristi, regarding this:

    "Suppose the code has two inner blocks each of them trapping the errors. Should we consider that to be unnecessary code (we could have written only one block instead of two)? I think some rules have to be added about that as the possibilities are just too many."

    My answer would be: it depends: if a nested block always raises an exception, traps it and does nothing, yes, you could remove it. If you can remove code so that two nested blocks become one and there is no change in behavior, then why not? What is your concern about that? It has to conform to the rest of the rules: (a) behavior must be same, (b) change in resources used is OK.

  11. Niels, with these rules then definitely you could remove EXECUTE IMMEDIATE and single quotes from a statement that is not dynamic (as in the 1 March quiz)...that would be "unnecessary" code. Do you have a concern about this? Do you feel the rules do not CLEARLY make this possible?

  12. Brigt, going from dynamic to static is certainly a change to the code, just not a change according to the rules of this quiz. Do you feel that it is not clear or that this is just TOO WRONG and should not be allowed? :-)

  13. Marga,

    Your #1: Suppose I could change a FORALL statement into a FOR loop. Each inserts 5 rows, there are no errors, so the result is the same. That would be an INCORRECT CHOICE, then, because the FORALL is unnecessary. This, by the way, would be impossible, since you cannot change a FORALL to a FOR by removing "ALL". That would be a violation of the rules.

    To be quite honest, I'd rather not offer examples, because then they are "ruined" for quizzes.

    Your #2: yes, that's right. You can remove part of a statement and it is probably worth saying that. So I will change this:

    Delimiters, identifiers and constants may be removed in their entirety. Removal of whitespace and comments is not allowed; removal of a part of an delimiter, identifer or constant is not allowed. For example, you may not remove "PLS_" from "PLS_INTEGER" and be left with "INTEGER".


    Delimiters, identifiers and constants may be removed in their entirety. Removal of whitespace and comments is not allowed; removal of a part of an delimiter, identifer or constant is not allowed. For example, you may not remove "PLS_" from "PLS_INTEGER" and be left with "INTEGER". Removal of just part of a statement is, however, allowed.

  14. I suggest the following: the piece of code must be a statement, i.e. it must be completed with a semicolon.
    Anton Sibiryakov.

  15. a side effect of the bullet list rules is you can simply drop a bullet to change the rules.

    For instance: drop the pl/sql block retention rule.

    insert into mytable (x) values(100);

    That's simply an insert statement with overhead of pl/sql context switch.
    By the "full" rules it would be correct, remove one bullet and it becomes incorrect.
    I think that's a nice feature; particularly for the last bullet if you want to explore performance related "should" code vs "could" code.

  16. Anton - do you mean that the only thing that can be removed would be an entire statement or that the result must be a statement? If the former, that will be too restrictive (hard to build the quizzes) and less interesting. If the latter, I think that is already taken care of by "must behave the same" - if the result of removal is no longer a statement, it certainly will not execute.

    Sean - that is an excellent insight. Yes, it will give me flexibility. And I will also add to the quiz text the date it was last changed (rules), so that if you'd played before, you will know that you do NOT have to read it again.

  17. Hello All,

    It looks to me that the rules are looking for ways to still allow the kind of "removals" that we saw in Choice 4 ...

    I would rather prefer to avoid these ones, because, once this controversy forgotten, the next one will start
    at the same point ...

    Let's say we can allow removing part of a statement, as long as it remains the SAME statement,
    for example, removing unnecessary branches of a CASE statement or unnecessary conditions
    from an IF statement, but NOT changing an EXECUTE IMMEDIATE into another statement,
    be it a static SQL (like in Choice4) or a nested PL/SQL block ...

    Regardless the PLS_INTEGER versus INTEGER issue ... yes, this is removal of part of an identifier,
    but in fact it is a data type change .

    for a specific case in which such a change does not impact the outcome ?

    This looks like the removal of a word, but it is more a data type change than "removal of unnecessary code" ...

    I am afraid that we will find ourselves again looking after things like a missing semicolon or similar
    and then sitting for days and debating who was right ...

    Do you remember the older

    END LOOP ( without a semicolon )

    COMMIT ;

    quiz ?

    These are things that I cannot consider to be "serious PL/SQL" teaching ...
    and these exactly are the things for which the GUI tools were invented for helping people
    even not think about them ... have them colored, indented, a.s.o., for helping you "to better see"
    what is effectively written.

    But, to punish a player for not having remarked the missing semicolon ?!?

    No by no by no, in my opinion.

    So, I think that care and common sense should be exercised for each and every quiz.

    Precise assumptions are required for each case, and I won't go for demanding from the players
    "to remember assumptions from a previous quiz based on last date, a.s.o.",
    better to have them in front of you each time for that specific quiz, and not having your attention distracted
    by remembering assumptions change dates, a.s.o.

    While most people seem to like this type of quizzes, let's remember however that most of the life
    of a PL/SQL developer is spent writing code and not removing code ... so, it is important to keep
    a "reasonable measure" in everything, just like in daily life, what is "too much" is in general not healthy.

    Thanks a lot & Best Regards,

  18. Steven,

    A couple items.

    For bullet two, should it read that "...removal of a part of a delimiter, identifier, data type or constant is not allowed."? "PLS_INTEGER" is a data type and the PL/SQL Language Reference does not reference data types in the same manner as delimiter, identifier, or constant, which seems to indicate they are distinct entities. If true, as written, the example describes modifying the data type and does not match the description of the rule. The exact wording/meaning of this rule needs to be clear, as I can already envision several scenarios that depend on what exactly can or cannot be changed in a declaration. Not sure if I can even ask any further clarification questions on this one without giving away possible quiz scenarios.

    Although it is clear from this blog thread and the quiz discussion that the EXECUTE IMMEDIATE option will be in play, I'm not sure that it would be clear from the rules above to anyone that hadn't seen these explanations. This was demonstrated by the majority missing it on the quiz under the original scoring, where a string is normally thought of as an atomic entity. One would think of keeping all or part of the string or removing the entire string, not changing the string to a different entity by removing the quote marks. In fact, I would say that it "almost" explicitly contradicts the first rule of not changing code. What was originally a string value "changes" into a dml statement. The change of PLS_INTEGER to INTEGER, which is prohibited, is less of a change than what happens here. Unless explicity stated in the rules, I think the majority of individuals will miss any answers of this type the first time they encounter it.


  19. Iudith,

    I still do not understand why you want to restrict the ability to remove code that will result in a "different" statement. The one example you offer to me emphasizes precisely why we SHOULD use my more "flexible" rule.

    I continue to believe that the EXECUTE IMMEDIATE choice (remove E-I and single quotes, leaving only the insert) is an excellent test of your ability as a PL/SQL developer to examine a piece of code and say "Whoa! That's not needed. There's nothing dynamic about that. Why over-complicate this program? And we can make it faster, too!"

    I don't think that the knowledge tested here is anything like the "missing" semi-colon quiz you mention.

    Remove LOCAL? Sure, that seems like a legitimate removal to me. Why not? This quiz is agnostic about the result of the change after the removal. It may change a datatype, it might change from dynamic to static, etc. The only question that is relevant for this quiz is: is the behavior of the code affected?

    Finally, don't worry - the rules will be in every one of these quizzes. I will just ADD a date at the top so you can see at a glance that nothing has changed since last time.

  20. Chad, thanks for catching this. First, it should state "delimiters, identifiers and literals may be removed in their entirety." I will change that in the initial blog above, for the sake of other readers.

    A datatype is an identifier and in fact the PL/SQL User Guide states "The lexical units of PL/SQL are its smallest individual components—delimiters, identifiers, literals, and comments." (http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/fundamentals.htm#CIHJCJAD)

    In addition, I realize that I should not talk about "changing" code. I should only exclude inserting new code/characters. You can take away, but you cannot add.

    Of course the code has changed. A removal will do that.

  21. I have made a number of changes to the rules, to improve clarity and fix some mistakes.

  22. Hello Steven,

    Removal of "PLS_" from PLS_INTEGER is by no means different from removing "LOCAL"

    Both are a data type change. So, why would the rules prohibit the first and allow the second ?
    Only because one is a word separated by spaces from the others, while the other is joined by an underscore ?
    Does not make much sense to me ...

    Removing EXECUTE IMMEDIATE from the so widely discussed example, anyway anybody will look at it,
    is NOT removing unnecessary code, but replacing less efficient with more efficient code.

    It looks to me that Chad in his post and most other players (except for Sean) tend to agree with it.

    I have a feeling that you only accepted it as a result of being amazed and its first sight
    and having probably said to yourself "Wow, how could it happen that I didn't see this possibility by myself ?".

    But, on the second thought, you will have probably realized that it was however an improper way of considering code as "unnecessary", one that maybe reminds about those puzzles that used figures and arithmetical operations made from match arrangements and asked that by only moving around a single piece (a match) to obtain a correct operation ...

    Chad raised a good point about it, it was indeed turning a CHARACTER STRING into a STATEMENT
    that was NOT originally present (as statement, not as words and letters !) in the code :) :)

    We should not fall into starting to play "word puzzles", that only take us further and further from PL/SQL
    and closer to playing "who is EDITING faster, the one who just removes characters or the one who removes
    and entire row and puts back just a part of it.

    I am sure that in the future cases you and the other authors will be very careful to not encounter again
    exactly this EXECUTE IMMEDIATE trap ... but I am afraid that it's NOT the only possible one that might
    raise similar controversies.

    In fact, regardless of the common sense that dictates not to write unnecessary code in the first place,
    Oracle is probably trying to continuously improve the compiler (some papers even call it "the optimizing compiler") so that it will become more and more capable to even ignore alone code that is not
    being used ... so the entire issue is not that "weighty" as to justify useless and endless controversies.

    Code improvement ?

    Yes, that is indeed a welcome topic, though, probably much more difficult "to pour" into quizzes .

    Thanks a lot & Best Regards,

  23. The statement "A choice is correct if it does not contain unnecessary code." is a double negative. I would prefer: "A choice is correct if it does only contain necessary code."

  24. John, good point. How about:

    "A choice is correct if all of its code is necessary."

  25. Good day.
    CRLF(CHR(10) || CHR(13)) - is it a whitespace? :)
    Can I remove precision in NUMBER(1)?
    'EXECUTE IMMEDIATE...' - single quotes are the part of the literal? Or not?

    Maybe make the rules flexible (only "You cannot remove whitespace or comments")... (replacements PLS_INTEGER->INTEGER, 'EXECUTE IMMEDIATE...' are allowable). And the quiz author will pay particular attention at possible ways to edit code.
    Anton Sibiryakov.

  26. I fully agree with Anton and Iudith for the EXECUTE IMMEDIATE example. The statement that should be executed by it is a literal constant and can only removed in total.
    And then we would add code to re-create the statement that was formerly a string.
    Take a look at the Oracle SQL Reference (Basic Elements of Oracle SQL | Literals | Text Literals) where the syntax diagram shows that the quotes are part of the string/text literal.

    Kind regard, Niels

  27. Excellent, thanks, Niel. That helps a lot. All I want are a set of rules I can apply consistently. The rules already state that literals must be removed in their entirety, and now we can see and agree that you cannot remove single quotes from a literal. I will add that as an example in the rules above. So, yes, from now on, removing single quotes around a literal will not be an acceptable removal. Thanks for all of your patience in working through this.

    Anton - CHR(13) is not whitespace. It produces whitespace when the code is run.

  28. When I see it correctly you are allowed to remove identifiers in total and as a conclusion also single arguments. So the block
    iPos := InStr( TEST_VALUE, 'b');
    DBMS_Output.Put_Line( 'Result: ' || SubStr( TEST_VALUE, iPos, 2));
    could be minimized to
    TEST_VALUE VARCHAR2(3) := 'abc';
    DBMS_Output.Put_Line( 'Result: ' || SubStr( TEST_VALUE, 2));
    Is that correct?

    Kind regards, Niels

  29. Hello Steven, Niels, All,

    @Niels, your previous remark regarding the quotes being part of the literal was excellent :)

    But I would rather not agree with this last example ...
    It is again just "playing around with entities", by taking care only to preserve the result ...

    Yes, it respects all the rules, but it just shows once again that those rules cannot probably cover
    all cases ...

    By the way:

    Does anybody, and especially Steven, think that removing the CONSTANT keyword from a constant declaration
    means indeed to remove UNNECESSARY CODE ?!?

    All the best practices, and Steven even more than others, do recommend to always use CONSTANTS where possible, rather than variables.

    It looks to me that, as I already said numerous times during this discussion,
    we are focusing too unilaterally on "shortening" code by any means, even if making it worse ...

    Thanks a lot & Best Regards,

  30. Iudith,

    I think it is important to remember the context for this discussion. I need to come up with a consistent set of rules so that I can offer "unnecessary code" quizzes that will be clear: you can read them, understand them, apply them consistently.

    This definition of "unnecessary" has NOTHING to do with quality of code - it is agnostic. It simply defines a process by which the code can be changed. It's not good, bad or otherwise.

    With my rules in place, I can then offer other quizzes. And in the writing of the quiz, we then get to the "judgement" side of things. I can use very poor judgement and come up with scenarios that have unnecessary code but DO NOT TEACH ANYTHING ABOUT PL/SQL. And players would find that irritating and they would stop liking me. I don't want that to happen.

    So I am not going to do that. I will use the quizzes to test and deepen knowledge.

    Niels' code is a good example of what I do NOT plan to do. It is a coincidence that the arguments passed to SUBSTR can be changed and the result is the same. It teaches us nothing about SUBSTR (Niels, please do not think my criticism is directed at you!).

    Of course, I am sure that you, Iudith, and others as well will withhold judgement about these rules and this quiz until the next one!

    Cheers, SF

  31. I think as long as the rules are clear about what can and can not be removed, then it's irrelevant what the players think "should" be considered removable.

    Just like every quiz that has a "correct" answer that is clearly a bad way to implement something, the point isn't to recommend a particular method, but to see if you can understand what is going on in a particular piece of code and either confirm or deny the results.

    There have been quizzes where none of the correct answers were good. I don't like those; but that doesn't mean the answers are wrong.

    Same with this. Some may say I used "tricky" thinking; but all I did was take a literal and (to me anyway) obvious approach to reading the quiz. With different rules stated I would have read it differently. With execute immediate, the change I suggested was simply "removing" some text. Was there a semantic difference? Sure, but the question didn't ask that, it wanted to know if removing the text changed the output.

    But...that's the old question. Going forward...as long as the rules of a quiz are unambiguous, then it's sort of moot whether they are "good" or "bad."

    If it seems that the quiz isn't teaching anything, just remember odd code you've come across in your own experience. I keep a little list of FANTASTIC bad code I've had to support.

    For example (yes, this is real code I've seen in a vendor's product)

    Iterate row-by-row over thousand of records, each record representing one day's worth of data. For each of those records insert a row of 25 nulls (25 to account for extra hour of daylight saving time.) Then, for each of those newly inserted rows, loop 25 times issuing one update on each hourly column.

    That's right, 26 sql statements to create one row of data!

    Every time I see what looks like a stupid answer that I don't want to mark as "correct" I remind myself of this code that somebody wrote and sold to someone else.
    Did it work? Yes! Was it good? NOOOOOOOOO!

    I've got multiple soft-copies and hard-copies of Steven's books and I've read tons of his articles and code examples; but as useful as those are...sometimes it's helpful to see what non-experts are generating. Unless you're fortunate enough to have Steven writing your code for you; chances are you're eventually going to come across some junk that makes you scratch your head. I've seen lots of anti-patterns and just plain dumb mistakes and I have to admit, I've written my share of them too.

    But, because I've read, written and debugged those oddities I spot and correct them faster now. I'm pretty sure if I had only ever seen the code in Oracle PL/SQL Programming I would be befuddled by the arcane constructions that exist in many real-world applications.

    So, while I did petition for more quizzes of what "should" be done. Bring on the collections of what "should not" be done because somebody, somewhere will try it.

  32. How about brackets (I think you call them parentheses in America)?

    Technically 1+(2*3) is the same as 1+2*3, so they could be considered "unnecessary" though they add clarity.

    Likewise, the IN for IN parameters is completely unneeded (and irritating to me) - but some people consider it good practise.

    Or the keyword CONSTANT, or AS for table aliases.

  33. Excellent point, Sean, about the value of seeing bad code. In fact, I wonder if you might be willing to share with me your fantastic list. I have been thinking about also starting up a quiz along the lines of "How bad is my code?" and you answer by identifying what's really wrong with the code.

    DanCJ, yes with these rules, you could certainly remove the ( or ). And yes IN could be removed. I definitely don't think you should do that, but it is the default, and that would test your knowledge of this default.

  34. Hello Steven, All,

    I am glad that you confirmed that there are also sample cases that you will NOT be using,
    in spite of their obeying all the rules.

    In fact, if we go back to the March 1 quiz that started this discussion,
    there was a "common point" for all the choices regarding what cam or cannot be removed,
    and this made the entire quiz into a very reasonable one.

    Namely, it checked to see that:

    1. You don't need a COMMIT after a DDL, because it is already done by that statement.
    2. You do need a COMMIT after a DML, which DOES NOT commit automatically.
    3. You don't need to check if a cursor is open after a cursor FOR loop, because for sure it is not.

    That is, unnecessary code was, even of not explicitly declared, code that could be removed
    because either it does nothing (like the NULL; statement in the sample code in the quiz introduction)
    or because what it does was in fact already done by the code that preceded it in the block.

    That could be the case for omitting things done by default, like initializing a variable to NULL
    or omitting keywords that are there by default, like IN parameter mode, as mentioned by Dan above.

    But this is NOT the case for transforming an EXECUTE IMMEDIATE into a static DML,
    for removing a CONSTANT from a declaration, for changing the data type of a variable, a.s.o.

    These are already NOT cases of removing unnecessary code, but for writing different code that
    produces the same result, be it better of worse code if judged by other criteria than
    the one of the pure outcome only.
    They are of course topmost legitimate as quiz topics, but not as part of the "removing unnecessary code"

    I am sure that this topic will still "mature and develop" with the time,
    and I am very content indeed with what Steven stated above, namely that the cases chosen will
    be on purpose cases that teach and check PL/SQL knowledge.

    Thanks a lot & Best Regards,

  35. Iudith, regarding CONSTANT (or something like this) I promise that I try to remove it before you can see it :)

  36. Steven,

    I think there might need to be some additional clarification to your two posts on 3/05 at 19:08 and 19:16. Not sure that I saw this issue resolved sufficiently in the posts following these. This may also result in needing to adjust the rules.

    PL/SQL Collections => composite data type => data types => identifiers == "You must remove a delimiter, identifier or literal in its entirety. Removal of a part of an delimiter, identifier or literal is not allowed". Using the simple example provided by Iudith, this would mean that "PLS_INTEGER" and "TIMESTAMP WITH LOCAL TIME ZONE" both fall under the category of "identifer" and "LOCAL" could thus not be removed. More problematic would be that as written, this could mean that any example of a PL/SQL collection type could not be modified by removing pieces. The PL/SQL collection could only be removed in its entirety.


  37. Arrggggh. That is frustrating, but yes it does look as you are right. Well, I don't want to make it that restrictive. I am sure there is a clear-enough way to say, in effect:

    You can remove "words" separated by spaces, but cannot remove characters from a "word."

    But I am not yet certain of how to phrase it.

  38. I would not only say that you can remove "words separated by spaces" but that you can remove "words separated by whitespace or delimiters".

    To my example: the focus I wanted to show was not so much about eliminating the CONSTANT keyword but more on the removal of the argument because the "in head compilation end excution" shows that the 2nd argument of the SubStr() wasn't needed for this specific data.

    Kind regards, Niels

  39. Hello Niels, All,

    If so, then this is another "kind of thing" that is meant just to test whether one knows how Oracle works
    ( not so much PL/SQL in this case, because the SUBSTR function belongs to the core Oracle... ),
    in other words, you are supposed to know that there is a DEFAULT available for the 3-rd argument ...

    In fact, if we look after the "more efficient code", I guess that relying on a default will probably demand
    "more work" from Oracle's side, because it has to also figure out that the caller has not passed a parameter
    and thus it has to make sure that a default is indeed available and what is its value.

    I wonder sometimes whether there exists any single developer in the world who knows by heart, for example,
    all the "headers" of the Oracle-supplied packages and functions ...

    Thanks & Best Regards,

  40. Iudith, whatever overhead is required to check for default values, I am fairly certain that happens at compile time.

    And while I think that testing everyone's knowledge of SUBSTR's arguments' default values is valid for the "unnecessary quizzes", I don't think I would take Niels approach in testing this knowledge.

    Back to finalizing the wording, I suggest that I change this:

    You must remove a delimiter, identifier or literal in its entirety. Removal of a part of an delimiter, identifier or literal is not allowed. For example, you cannot remove "PLS_" from "PLS_INTEGER" and be left with "INTEGER". Another example: you cannot remove single quotes from around a literal string.


    PL/SQL is composed of delimiters, identifiers and literals. Removal of part of a delimiter or literal is not allowed. You can, however, remove entire "words" from an identifier. Examples: you cannot remove "PLS_" from "PLS_INTEGER"; you cannot remove single quotes from around a literal string; you can remove "ZONE" from TIMESTAMP WITH TIME ZONE".

    How does that work?

  41. "Brigt, going from dynamic to static is certainly a change to the code, just not a change according to the rules of this quiz. Do you feel that it is not clear or that this is just TOO WRONG and should not be allowed? :-)"

    I thought it was not sufficiently made clear in the rules you initially laid out - I will abide by any clear set of rules (and in some cases will even do so without complaint ;)).

    I still think that the clarification in the comments that the "must remove entire literal" rule prevents changing dynamic to static SQL can still be more clearly specified in the text of the ruleset (my addition within the asterisks: "Another example: you cannot remove single quotes from around a literal string*, such as dynamic SQL*.". Although I suppose that could be considered "unnecessary text" =).

  42. At first, I was going to write "I am happy to emphasize in the example that the switch to static from dynamic SQL is not allowed."

    But then I considered the critical issue of data normalization.

    My "users" are supposed to be highly trained logicians, masquerading as programmers. They should be able to think abstractly, applying general rules to specific situations.

    So I think that as long as the rule makes the switch from dynamic to static invalid, that will have to be sufficient.

    And thanks, Brigt, for your willingness to play without complaint, though I hope you never hold back for the sake of my delicate sensibilities.

    I lost them somewhere, a long time ago. There do not seem to be any lingering sensibilities.


  43. Steven, you should be aware that the rules text, without the corollary comments, is prone to misunderstanding - for a prime example of this, see your own comment from 05 March, 2013 11:53. You say there the rules CLEARLY allow changing from dynamic to static SQL. And yet, now you think they sufficiently clearly disallow it?

    I do agree that it is logically sound that the rules preclude changing to static SQL and changing the rules text is therefore not strictly necessary. Still, I would prefer a bit of embellishment for clarity.

    As an aside, I think you misread me a bit. I tend to complain when I disagree, and am aware of my argumentative nature - which is what I was talking about when saying I will only occasionally abide by rules without complaint.

  44. Brigt, the reason I say that now it is clear from the rules that a switch from dynamic to static would not be allowed is that we do not allow parts of literals to be removed and I offer as an explicit example that "you cannot remove single quotes from around a literal string".

    Doesn't this cover the dynamic->static switch, at least in the form that came up in the first quiz? Why

  45. Yes, I do (still) agree that it is logically sound - merely prone to misunderstanding, as evidenced by your own initial stance that it was allowed.

    Or, maybe I am confused and the no-partial-literal-removal rule wasn't part of the rules to begin with? I thought it was, and that it was Niels explaining the ramifications which convinced you that it could not be allowed after all.

    Anyway, for myself things are clear, so I guess I'll drop this discussion and reap the benefit of others' potential misunderstanding =).

  46. That's precisely it, Brigt. According to the original (vague) rules), it WAS allowed to switch from dynamic to static in that way. It will be no longer acceptable.

  47. Hello Steven, All,

    Looks to me that this thread becomes more and more difficult to follow ...

    In the example above, it is NOT the word "ZONE" from "TIMESTAMP WITH TIME ZONE" that can be removed,
    but the word "LOCAL" from "TIMESTAMP WITH LOCAL TIME ZONE" ... or ... well ...
    if you wish, to remove ALL the words and leave only "TIMESTAMP" alone .

    Anyway, I still think that this is a data type name, even if composed of several words, which is pretty unusual,
    in other similar cases there used to be an underscore between the component words.

    I don't think that here each component word can be considered as a separate identifier,
    so, though the standard definition of an identifier does not allow for embedded blanks without surrounding
    the entire identifier with double quotes, however, I think that data type names should still be considered
    similar to the other identifiers, aka, no partial removal allowed.

    Objections will follow anyway, I am pretty sure about it ...

    However, I don't think that losing sensibility is the best way to go,
    in life in general, and among friends especially.

    Thanks a lot & Best Regards,

  48. Rather than trying to get super precise wording that prevents clever parsing. Wouldn't it be simpler to just open it up and then construct questions with or without the conditions.

    Which of the code examples below have any text except white space that could be removed and produce the same output.

    Then, simply use single character variables and INT instead of PLS_INTEGER and VARCHAR instead of VARCHAR2.

    Or, if you want to lose styling preferences then leave the general rule in place but list explicit exclusions.

    With the following exceptions that may not be altered in anyway:

    Which of the code examples below have any text except white space that could be removed and produce the same output.

    If you want PLS_INTEGER limits to be in effect, then arrange the question so you'll run into those limits.

    If you don't want dynamic sql to be converted to static, then write sql statements that are actually dynamic.
    It seems wrong to me to disallow conversion of static sql to static sql simply because the original sql was forced through unnecessary dynamic execution (especially in a quiz about unnecessary code detection.)

    Anytime an author is creating an "unnecessary code" quiz, he or she is probably checking to see if the players will notice a particular feature or set of features. So... code for that those particular features.

    If by chance something slip through and extra code is detected by player, then change the scoring for everyone to reflect the correct answer. If the question text is unambiguous that ANYTHING except white space and the ONLY criteria for acceptance is if the output matches some stated text. Then it will be easy after-the-fact to confirm the results and score accordingly. If the scoring wasn't what was intended, so be it.

    1. Thanks, Sean. Your idea makes an awful lot of sense. On the one hand, after all this effort at crafting consistent rules (which I believe at this point has been achieved successfully), I hate to give it up. On the other hand, I worry about our many non-English-speaking players having to work through all that verbiage.

  49. Hello Steven, All,

    Looks like after some progress in the direction of favoring common sense and prevailing of PL/SQL features,
    we again drop back to simply trying to wipe out text to make the code lines shorter, without any implication
    of any knowledge ...

    Again, shortening identifiers to one single letter and changing data types to those whose names are shorter
    is what we are really after in these quizzes ?!?

    That would be very sad and disappointing ...

    I think that the removal should be limited to what indeed is removable based on PL/SQL (and in fact, Oracle)
    behavior, as was the intention of the authors and reviewers for the quiz of March 1 ...

    Since then, following Sean's exploiting of that poor dynamic SQL statement ...
    we are just sinking and sinking deeper into "word puzzles" instead of focusing on PL/SQL knowledge.

    I agree that a stuffy and verbose set of rules will not ease the life of any players, not just of those who are
    non-native English speakers, but, above all, it will make a tremendous anti-service to the PL/SQL knowledge itself, which should remain the focus point of this competition.

    Instead of rigid rules, we are maybe just one step away from rather formulating "rigid quizzes",
    aka, quizzes that specify exactly the line numbers where removal is allowed.

    Hope that no one will feel angry for my remarks, I cannot but be as sincere as always.

    Thanks a lot & Best Regards,

  50. >>> I worry about our many non-English-speaking players having to work through all that verbiage.

    I definitely agree there.

    We had plenty of Only-English speakers that also thought what we started with was confusing.
    Make the rules simple, put the complexity in the quiz. Not the other way around.

    As long as the results can be unambiguously verified, personal preference (even of the author) of what should or should not have been included are irrelevant.

    Code X produces output Y. Can I remove non-white space text from X and still get Y? Yes or No?

    That seems bulletproof to me. Simple is better. Any dissenters want to poke a hole in it?

  51. I'm not angry, but I am confused as to how the debate can still be going.

    >>>without any implication of any knowledge ...

    I disagree with this, it takes knowledge to figure out what can or cannot be removed and still produce the same results. That seems obvious to me, but maybe I'm missing the point of that statement.

    >>> we are just sinking and sinking deeper into "word puzzles" instead of focusing on PL/SQL knowledge.

    That's exactly my point in wanting simple rules. If you have to think "is this an exception?" "I could remove abc, but will it be counted?" Then the rules are too complex and the quiz is about rule parsing and not code parsing.

    Simple rule: If you can remove it and the output doesn't change, then it's not necessary. It might be good practice, it might be bad practice, it might be philosophically completely different, but it's not necessary.

    If the author wants to test knowledge of a particular pl/sql feature. Then put that into the quiz. All of the disputed removals in the first quiz could have been avoided simply by writing the code examples a little differently.

    It seems the counter arguments to simple rules are based on personal preference of what types of knowledge are considered valuable.

    I definitely disagree that removing execute immediate and the corresponding quotes was simply a word-puzzle. It required pl/sql knowledge to know that removal would have no impact on the results. Was it sophisticated knowledge? No, but then again, none of the intended or unintended removals were particularly complicated.

    That fact that some players didn't think of some of the removals doesn't invalidate the fact that the removals were legal. Furthermore, if some players did notice the removals and then decided to dismiss them as not important - that personal preference is the error, not the rules and not the quiz.

    As I mentioned before, I find it distasteful to mark bad code as "correct". However, that's a personal preference too. If the bad code generates the expected output of the quiz, then it's "correct" by those rules.

    It's definitely playing games with the rules to say an answer was technically correct but shouldn't have scored that way. Being correct about easily verifiable code is not an "exploit", it's simply being correct.

    I'll say it again because it's the core of my argument and I have yet to read a counter to it.

    Simple rule: if you can remove it and the output doesn't change, then it's not necessary. It might be good practice, it might be bad practice, it might be philosophically completely different, but it's not necessary.

  52. Hello Sean, All,

    If we go back to the quiz of March 1, the introduction to that quiz presented this example:


    and suggested that NULL; is an example of unnecessary code.

    From here, probably most straight-forward thinking players (and I really mention here straight-forwardness
    as a positive quality of a good developer !) were automatically driven to consider "code" as being the same as
    "statement", or, at most, maybe an entire variable declaration line.

    In this light, EXECUTE IMMEDIATE was NOT "code" (aka statement !) that could be removed,
    but just text, that happened to be followed by a particular string ...

    Therefore, I strongly believe that we should still stick to only allow removing entire entities
    and not just performing "cosmetic text changes" that happen to leave the result in place,
    like the SUBSTR in Niels' example in one of the previous posts ...

    Also, I strongly disagree that everything that leaves the result in place should be removed !!!
    EVEN if it is possible to create an artificial set of rules that will cover such cases,
    they are counter-educative and of no real value, except for avoiding such post-quiz discussions ...

    Of course, it requires some attention to figure out whether a result remains the same if we remove
    just some part of an identifier or part of a data type name ... but I would not go for calling this
    PL/SQL knowledge, and it is NOT a good method to educate the players in the direction of constructive development ...

    So, if you don't like the simple term of "knowledge" whose usage is so imperiously demanded
    by each post in this thread, then let's call it "constructive knowledge", in opposition with just
    "word juggling capacity".

    I dare say that pretty all the players that answered that 4-th choice differently from what you have considered,
    did so not because they don't know that a static INSERT has the same effect as a dynamic one ...
    but because they did NOT consider that removing part of a statement is in accord with the
    let's name it, not so precise rules.

    It is very frustrating to be punished for having violated a "rule" that you did not know about !!!
    Or, better say, one that you have misinterpreted or, more precisely, you have interpreted
    exactly the same as the authors and reviewers did by themselves, before the debate started and other
    possible interpretations were suggested ...

    Thanks & Best Regards,

  53. There is no added value in making the "entire entities" restriction than in allowing anything to be removed. It's just preference.
    However, the cost of making that restriction is complicated and arcane rules wording.

    As for "constructive knowledge" I've already given up on that argument. Changing the code in anyway that alters the performance (more/less memory, more/less latches, more/less io, more/less cpu, more/less time, etc.) is extremely important but has already been thrown out as part of the scope of these types of quizzes. I think that diminishes the quizzes, but so what? As long as the quiz intent is clear and verifiable it's a good quiz. These quizzes, like all others, are about whether you can identify the significance of various forms of syntax. You can call them "cosmetic" if you want, and, if you ignore performance ramifications, a lot of the changes could be considered cosmetic only; but again... so what?

    Removal is an interesting twist on syntax identification; but it rings false to apply some special consideration that we could remove some syntax but we're going to arbitrarily not allow you do so.
    Plus, and this is very important, by using a simple rule. It frees the question authors to make more types of questions. With a complex rule, they are just as bound by tricks of word wizardry in forming the questions to make sure they are really asking.

    The execute immediate example that everyone seems to carry on about is silly. The snippet has bad code, my suggestion was remove the bad part and you'll get the same results. To me that was obvious but I didn't know if performance was supposed to be part of the results. So in the end, I simply guessed. The ramifications of the change (performance and output) were obvious to me but I couldn't say for sure what the author was going to consider. That's a bad quiz.

    Since the rules have now been clarified (simplified) that performance doesn't matter, I don't like it; but who cares? Neither you nor I have to "like" a question, we simply have to agree that what is asked and what is answered are easily identifiable.

    I don't want to win this argument because I've convinced anyone the questions will have better content (although I think they might.) I want to win this argument because I've convinced everyone (or at least Steven) that removing ambiguity and confusion is paramount. When there is no confusion, there is no argument, or at least no reasonable argument.

    >>> they are counter-educative and of no real value, except for avoiding such post-quiz discussions ...

    You're ascribing deficiencies to questions that haven't even been created yet. I trust the quiz authors, including you, can create helpful quizzes with simple, clear rules.

    I don't really expect to avoid post-quiz discussions, rather to make them short and to the point. Whether correcting a player that got it wrong, or highlighting something that everyone else missed, the discussion becomes simply: run the code, verify results, score accordingly.

    I think you and I are probably at an impasse. I value clarity of the questions over the actual content of the questions. You put the content higher.
    So our arguments don't really appeal to each other and, in fact, probably don't even make sense to the other.

    For example, I've done network ACL configuration more often than I've written code with varrays. So, if you ask me a question about varray I'll probably learn more than if you ask me one about how conflicting and complimentary ACLs resolve.

    Since the author can't know who will know what; the only universal aspect is the clarity of quiz text and rules for answering it.

    I think I've said the same thing at least 5 times now; so I've probably used up my quota of repetition. In the spirit of the thread, I'll remove myself now as further repetition will be unnecessary.

  54. Hello Sean,

    You are right, probably our positions seem to be irreconcilable at this moment,
    though, interestingly enough, my impression was all the way that you are the one who favorizes the content
    aspect (resource consumption), having stated it as the reason for which you considered
    the EXECUTE IMMEDIATE removal to be allowed.

    I agree with you that rules are topmost important, but I won't like to arrive to the point where
    the rules will become the central points of the quizzes.

    If "word wizardry" starts to be allowed, then we will find ourselves just amending/extending the rules all the time
    for covering all the upcoming different "clever exploits" of any quiz, by those who might happen
    "to overthink the authors" in one case or another... as it happened with this first quiz and some other ones
    in the past.

    There is no "official" set of rules for such a topic, we will have to invent our own set,
    and, while a set might seem to be comprehensive, clear, and closed, it is just a matter of time when some specific quiz will arrive and prove that this was not the case.

    The problem with the March 1 quiz was not whether the author did or did not think about performance implications of the code removals, it was clear from the quiz that all what did matter was ONLY the result/outcome.

    What was not so clear was what is a "legal code removal" ... and here is where this discussion started
    and it still lasts for about two weeks ... though, probably, all the other participants have decided
    to retire from it because it will probably never arrive to an equally satisfactory end for everyone.

    Thanks & Best Regards,

  55. Right! Thanks to everyone who contributed, especially Sean and Iudith - for your ideas, patience and passion. :-)

    I feel at this time that I have a set of rules I can use and also an excellent idea from Sean to take the opposite approach and define limited rules relevant for each quiz. So it it is a fine time to end this discussion.

    I will offer another "unnecessary code" quiz next week, based on the rule set. The rules will be available BEFORE you play on the "launch" page. So players will not have to pay with quiz time to read through the text.

    We'll see how that goes....

  56. Hello Steven,

    Is that something new ? ( I don't want do deal with whether this is good news or bad news ... )

    Usually a quiz topic is *not* published ahead of time ... for good and for worse ...

    If this is the case, a revision of the finally (actually ?) adopted set of rules will be a good service for everybody,
    I personally find it difficult enough "to screen them out" from this stuffy pros-and-cons looooong thread ...

    Thanks a lot & Best Regards,

  57. The final set of rules are those you see at the top of this thread (original post). I updated them as I accepted suggestions for changes.

    Yes, I suppose I have given everyone advance notice of the "topic" for that quiz - but it's such a general topic, I don't think it will help anyone...do you?

  58. >>> Removal of a part of an delimiter

    "a" instead of "an"

    This probably won't apply, but what if the existing code is invalid. What does removing code from a syntax error do to the results? I suppose it could change the compilation error, but does that count as necessary?

    What about invalid code where I remove something and it still fails with the same error? Is that unnecessary?

  59. Thanks, I will fix that typo. I don't plan to show invalid code, so that won't be a worry.

  60. Hello Steven, All,

    A case that I think was not discussed up to here:

    If a variable is defined as NUMBER(m,n) , then is the removal of any of the following:

    a. (m,n)
    b. m,
    c. ,n

    allowed ?

    Well ... some coders may even put a blank in between, like in "NUMBER (m,n)" ,
    so the precision/scale element can even be a "separate word" .

    As by how I understand the rules, I guess that such a removal is allowed.

    Also, the example with "TIMESTAMP" in the rules list should be corrected,
    you cannot remove ZONE only, just eventually LOCAL from "TIMESTAMP WITH LOCAL TIME ZONE",
    or ALL the words except TIMESTAMP itself.

    And, also, eventual precision if specified, similarly as for the NUMBER case above.

    Thanks & Best Regards,

  61. I will add a definition of word ("text separated by delimiter or whitespace") to the rules.

    Yes, I think that you can remove a, b, and c according to the rules.

    And according to my rules, you certainly could remove WITH, LOCAL, TIME or ZONE from that type declaration. The result may not be valid code and therefore will not be correct, but it would be a valid removal.

  62. Hello Steven,

    Now it comes to define precisely "delimiter" as well

    In some contexts, an "underscore" is also considered a delimiter.
    For example in functions like INITCAP, any non-alphanumeric character is a delimiter.

    But in our set of rules I guess it is NOT, at least as the example of PLS_INTEGER shows.

    Thanks a lot & Best Regards,

  63. The Oracle PL/SQL User Guide defines "delimiter" for us:


  64. And I will add that resource to the quiz topic.