Two Mistakes in 12 May Quiz (2304)

Well, let's just start off with an apology: I was sloppy and as a result must alter the scores of 548 players for this day. Maybe it's because I was so excited at the end of last week when I found out that I will be a grandfather come November. No, that's a lousy excuse (though great news).

In any case, players wasted no time in reporting two problems with this quiz on the COUNT method:

1. We scored as incorrect a choice that resulted in an unhandled exception being raise. This error clearly indicates that the implement in that choice was flawed. The question, however, asked which choices resulted in "3" being displayed. When this choice is executed, "3" is displayed and then the error is raised. In most editing environments, you will see this output even when the block terminates with an exception.

Action taken: I changed the choice to include an exception handler that will display "ERROR" and changed the question text to ask for choices that only show "3". Anyone who marked that choice as correct has been given credit.

2. We did not include an ORDER BY on the query in the question, so the order of data placed in the collection cannot be guaranteed. THIS OMISSION REALLY IRRITATES ME - and should irritate you, too. Again, my apologies. This issue has been raised multiple times by players (for past quizzes) and I really need to be more careful.

Action taken: I added an ORDER BY to the query. Everyone who marked as incorrect the two intended-to-be-correct choices are given credit.

I also refreshed the rankings, so all data on the website should reflect the above steps.

A "good to know" regarding explain plans and DBMS_XPLAN (2303)

The 11 May quiz tested your knowledge of the DBMS_XPLAN package and how it can be used to display explain plan information. As part of the explanation of the scoring, we wrote:

This choice does not specify a statement ID, but that's OK,  because the call to the DBMS_XPLAN.display function does not pass a value for  the statement ID parameter. Oracle automatically passes back explain plan  information for the most recently explained statement.

Anna Onishchuk wrote to suggest that this statement is not entirely true:

Oracle does not pass back explain plan information for the  most recently explained statement, it passes back information for the highest  PLAN_ID which is quite different in databases with DB links. So in most cases  your explanation is correct, but not in all cases. So technically the answer should be “As we have a totally  clean database (no db links), you’ll see the plan for the last query you  run”.

She also offered this example:

If you run a query for a remote table via DB Link, the Oracle will take sequence from the remote database and use it in the local database. Here is an example:

You current sequence plan_id = 20 in your local database.
In remote database plan_id= 1000.

Run the query:

Explain plan for 'select * from dual@my_link where 1=1'
/

The query will place new records in PLAN_TABLE with PLAN_ID = 1001. If you try to do the quiz, the greatest PLAN_ID will be selected so the remote plan will be returned. (Unless you try the quiz for 800 times)

SELECT *
  FROM TABLE (DBMS_XPLAN.display ('PLAN_TABLE', NULL))
/

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|      |  1 |  1 | 3 (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL     | DUAL |  1 |  1 | 3 (0)| 00:00:01 |

===== End of example

I will update the explanation for the question with this information, but I thought it would also be worth highlighting it on the blog.

Thanks, Anne!

Final test period for PL/SQL Challenge version 2

Dear PL/SQL Challenge player,

I am pleased to inform you that test.plsqlchallenge.com is now open for testing of version 2 of the PL/SQL Challenge website.

The test period will last through 20 May. We then plan to upgrade PLSQLChallenge.com over the weekend of 21 May.

You should be able to log in using the same information you use at plsqlchallenge.com. All of your quiz results through the end of last week are loaded up.

Remember: any information you enter on the test site (quizzes taken, changes to profile, etc.) will not be carried over to the production site.

As you will see, version 2 of the website is very different from the current state of the site. We hope that you like changes! Besides a new new look and feel, the biggest change you will notice immediately is that the site is no longer focused around a single, daily quiz. There is no longer, for example, a button in the menu bar for Take Quiz. Now you simply pick the quiz you want to take from the Pick a Quiz table.

This is just one reflection of the fact that the site is now powered by a much more flexible platform for delivering quizzes. We will, as a result, soon be adding SQL and PL/SQL quizzes. We also now offer polls, right on the site, so that we can more easily gather feedback from you on how to improve the PL/SQL Challenge.

When you notice a problem with the site and click on Feedback to report a bug, please make sure to view the report on the bottom of the page (especially the Known Issues report) to avoid submitting an issue of which we are already aware.

You can also respond to this blog post with your feedback.

Many thanks in advance,
Steven Feuerstein