03 December 2011

The Pain of a Missing WHERE Clause Predicate

I sent the following email out to 1243 players today:

Dear PL/SQL Challenge player, 

One of things I like most about the PL/SQL Challenge is that it keeps me humble. I am reminded on an almost daily basis by one or more players that there is lots for me learn about PL/SQL and the wider Oracle technology stack. 

And then there are the moments of deep, deep humility - such as when I discover a nasty bug that has corrupted data in the PL/SQL Challenge repository. Painful lessons, those. 

Unfortunately, I write to you about precisely just such a moment. We discovered recently that a DELETE statement was missing a critical predicate in the WHERE clause (did a chill just run down your spine, as it did me when I realized what was happening?). As a result, if a player pressed the "Start Over" button while taking the Oracle Magazine quiz, all the choices made by players for this quiz were deleted. 

This bug went undiscovered for quite a long time, so it is not possible to recover the data. Instead, we are going to "wipe the slate clean", extend the close date on these quizzes, and offer you the opportunity to take them again. 

Look on the bright side: the second time around, you will likely answer faster and with higher accuracy!
Our deepest apologies for wasting even a few moments of your time. I hope you will continue to play the daily, weekly and monthly quizzes, thereby deepening your expertise in key Oracle technologies.

Trying to look on the bright side of things, this same problem would have occurred if anyone competing in the quarterly championship playoff had pressed that same "Start Over" button (it is only visible when the competition has multiple quizzes in it). That would have wreaked havoc - and required me to come up with five more playoff-worthy quizzes. 

Still, I feel horrible that we would have made such a mistake and not noticed it testing; and that I would cause any player to have to re-do something they've done already.

I have extended the close date for the Sept/Oct quiz to the end of the year. I hope that you all will find the time to take the quiz before the end of the year.

Again, my apologies....
Steven Feuerstein


  1. Hello Steven,

    I think you meant the Nov/Dec quiz to have its
    closing date extended, and not the Sep/Oct quiz,
    which does not appear on the list of quizzes to be taken.

    Yes, the bright side of the things is that ALL of us, without exception, are learning from our mistakes, and the ones we maybe don't do while coding our work projects, we unfortunately do while playing the PL/SQL Challenge ...

    I just had a few "great" occasions during the month of November to experience that very same feeling of humility ... otherwise I cannot explain so many stupid mistakes made on topics that are mastered so very well and used so successfully in the daily life !!

    Your feeling of having seen that DELETE statement without a WHERE clause is probably similar to the feeling of "total-loss" that a player feels when he realizes he has done a mistake the very moment after pressing [Submit]...

    But our Oracle God is there and wants us to keep learning and keep this so beautiful challenge going :) :)

    We all learn from your so vast experience
    and hearing you saying that you also learn from ours really gives us a high sense of a so nice collaboration.

    Learning is part of our lives, and maybe the most beautiful and valuable one, so, my own personal motto is:

    "Have you made a mistake ?
    Don't let it break you down,
    correct it, try to not repeat it and

    Thanks a lot for sharing these feelings with us
    and best regards,

    Iudith Mentzel

  2. Thanks, Iudith! I have fixed the Sept/Oct quiz so that is should now be visible.

  3. DELETE/UPDATE without WHERE should be forbidden.
    When i wrote DELETE FROM T_TABLE WHERE 1=1; COMMIT; And i do not want it - i m "stupid" and responsible for it. If i wrote DELETE FROM T_TABLE - it could be very probably ""just"" forgot mistake, much more dif when I m forced to write WHERE with 1=1..