Beta 2 of PL/SQL Challenge V2 now available for review and testing

We have published a new beta version of PL/SQL Challenge 2.0.

You will notice a radically redesigned home page and welcome page (the page you see when you are not already logged in). We like it a lot, and I hope you will, too.

The home page offers an expanded scorecard, as well as quick links to recently taken quizzes and your own submitted quizzes. The Play a Quiz table is now (we believe; you tell us!) much easier to see and work with, especially given the upcoming "explosion" of multiple quizzes that you can take.

Guests (not yet registered) can now play a sample quiz and view up to five past quizzes.

The process of taking a quiz should be bug free. Please check that out.

There are, of course, a number of known issues. I suppose I could list them here, but instead you can view the current list of known issues by clicking on the Feedback button and then choosing the Known Issues report on the bottom of the page.

At this point, we are focused on cleaning up the site, getting everything working, and pushing it out for production use in early May. We welcome - and thank you in advance for - your feedback on this beta.

The beta site may found at:

http://beta.plsqlchallenge.com

You can use your regular email address and password to log in. You will see all the past quiz data available on the production site, as of 13 April.

Cheers, Steven

Yes, cursor FOR loops can be infinite loops! (2205)

In the 15 April quiz on infinite loops scored the following as incorrect (not an infinite loop):

BEGIN
   sys.DBMS_OUTPUT.disable ();

   FOR rec IN (SELECT * FROM all_source)
   LOOP
      sys.DBMS_OUTPUT.put_line ('See you later!');
   END LOOP;
END;
/

and provided the following explanation: "A cursor FOR loop will never be an infinite loop, because a query can never return an infinite number of rows."

Ah, naive Steven! Ah, Steven of the limited understanding of SQL and all of its "tricks"!

Several players wrote to say that while the choice correctly identifies a query that will always return a finite number of rows, it is, in fact, possible to craft a query that returns an unlimited number of rows. They offered these examples:

BEGIN
   FOR c IN (    SELECT dummy
                   FROM DUAL
             CONNECT BY LEVEL > 0)
   LOOP
      DBMS_LOCK (sleep (1));
   END LOOP;
END;
/

BEGIN
   FOR c IN (SELECT *
               FROM (    SELECT LEVEL
                           FROM DUAL
                     CONNECT BY LEVEL < LEVEL + 1))
   LOOP
      DBMS_LOCK (sleep (1));
   END LOOP;
END;
/

Thanks for bringing this to my attention. I will change the answer text to reflect this "nuance" of SQL.

12 April quiz a "trick question"? (2202)

The 12 April quiz tested your knowledge of what can happen when you create database objects with the same name as the schema in which they are created, such as a table named HR in a schema named HR.

A player wrote the following after seeing the answer to the quiz:

Please pass on my thanks to Iudith Mentzel, this trick question caught me! I didn't even notice that the table name was the same name as the schema, so totally missed it :) in fact, I didn't even look at the table name. I learned something knew, so it was worth it.

I was staring at the answers for quite some time before answering, because I couldn't work out if it was an Advanced or a Beginner question. I didn't have access to a database at the time to try it out. In the end, I decided it must be a Beginner question because it was dealing in simple concepts (functions called via SQL vs. PL/SQL), and answered accordingly.

Personally, I feel this question is an argument in favor of bringing back the "Beginner/Intermediate/Advanced" classification that we used to be able to see when answering a question.

Alternatively, the question should have drawn attention to the fact that the table had the same name as the schema - then it would no longer be a trick question, but a test of one's knowledge about name resolution in SQL vs PL/SQL.

I agree with the player: Iudith deserves congratulations; but I disagree that this is in any way a "trick question." First, congratulations: this quiz is an excellent example of using the quiz to uncover quirky behavior in the Oracle database and warn developers of a hard-to-diagnose problem.

I have run into this scenario several times as I supported the Quest Code Tester tool. A user would complain about the product not working properly and we'd lose way too much time diagnosing the problem before checking the name of the schema - same as the name of a package and BAM!

As for the suggestion that we should have "drawn attention to the fact that the table had the same name as the schema" - I would argue that we did just that. The question starts off with:

"My DBA has created a schema named PLCH in which I am doing my development."

How often does a quiz specifically name a schema? Very rarely. So that should, in fact, have been something of a tip-off regarding the goings-on in the quiz.

I continue to resist the idea of showing the difficulty level of the quiz. In the real world, when you are confronted by a bug or trying to understand some code, you don't get a hint regarding the difficulty or complexity of the problem. The problem is the problem and the code is the code. We dive in and we analyze. So I don't see why we'd provide this information for the quiz.

Your thoughts?

Cheers, SF