New Play a Quiz Features

As explained in a previous blog posting, the Play a Quiz table on the home page has gotten very full and busy. Players have been complaining about not being able to find the quizzes they need to take, or recently took.

So over the weekend we put in place a number of changes that should help you:

1. Moved Play a Quiz to the top of the page
2. Save filter setting as default
3. Quick access to My Technologies
4. Separate tab for Taken/Closed quizzes

Taken altogether, the home page now looks like this:

The Not Taken tab will show you all quizzesthat are open (can be played). Note that you might not be eligible to play all of them (such as the quarterly playoff), but for the most part they will be precisely those quizzes you can play. Quizzes are ordered by those that will be over soonest.

The Taken/Closed tab shows you only those quizzes that you have taken or are closed (you missed the chance to take them). Those quizzes you answered most recently appear at the top of the list.

If you only want to see quizzes for technologies in which you are interested, change the filter to My Technologies:

And if you don't want to have to change this filter setting each time you visit the home page, click on "Save Filter as Default".

Finally, if you want to change any of the settings for the technologies in which you are interested, click on "Edit My Technologies" to take you directly to the Technologies to Play page of your account.

We hope these changes make it easier to play your quizzes and review the results after you've played.

IE8 and Compatibility View: TURN IT OFF!

While we continue to have problems displaying the plsqlchallenge.com site on IE7, I was particularly dismayed to receive a bug report regarding IE8. A player reported that when he visited the home page, it looked like this:

Not good.

And what bothered me most of all is that on my laptop, PLSQLChallenge.com works just fine on IE8.

Well, it took a little bit of investigation, but we finally discovered that if you have Compatibility View turned on in IE8 (and, likely, in IE9 as well), you end up with a home page like that shown to the left.

If, on the other hand, I turn off Compatibility View, the home page displays without any problem. So if your home page looks weird, check this setting before you submit a bug report:

PL/SQL Challenge Activity Summary for 5 September 2011 - 9 September 2011

Oracle PL/SQL Quizzes

In the past week (5 September - 9 September), 822 Oracle technologists were busy submitting 2,465 answers to quizzes and exploring the library of past quizzes. Here are the PL/SQL quizzes played in that period:

5 September 2011: PLS_INTEGER and its subtypes will raise an overflow error if the value assigned to it is greater than 2147483647; SIMPLE_INTEGER will "wrap around" to -2147483648 and "count up" from there.
                550 Players ** Avg. Correct: 72% ** Avg. Time: 219 seconds ** Rating: 4 stars

6 September 2011: PL/SQL offers a number of ways to retrieve multiple rows from a dynamic SQL query; OPEN-FOR is especially helpful when you plan to use the BULK COLLECT clause as you fetch, but also want to use the LIMIT clause to manage memory consumption.
                658 Players ** Avg. Correct: 78% ** Avg. Time: 321 seconds ** Rating: 4 stars

7 September 2011: It is often necessary to continue executing your application code, even after an error is raised. Both SAVE EXCEPTIONS and LOG ERRORS allow you to do this when errors occur in DML statements. It is important, however, to be aware of the differences between the two, and choose the feature that best matches your requirements.
                642 Players ** Avg. Correct: 63% ** Avg. Time: 282 seconds ** Rating: 4 stars

8 September 2011: Oracle will perform implicit conversions under many circumstances. Often, such conversions will have no or minimal impact on performance or correctness. Generally, however, it is best to perform explicit conversions to ensure correct behavior of your programs.
                632 Players ** Avg. Correct: 6% ** Avg. Time: 567 seconds ** Rating: 4 stars

9 September 2011: When you need to check for and match on a NULL value in your CASE statement or expression, you need to use a searched, not simple, CASE format.
                560 Players ** Avg. Correct: 81% ** Avg. Time: 191 seconds ** Rating: 4 stars

SQL Quizzes

Between 13 August - 9 September, Oracle technologists played these SQL quizzes:

13 August 2011: Whenever your column values may contain NULLs, you should be very careful in how you construct your SQL statement to take this into account.
              494 Players ** Avg. Correct: 89% ** Avg. Time: 894 seconds ** Rating: 4 stars

20 August 2011: If a condition refers to an aggregate function, put that condition in the HAVING clause. Otherwise, use the WHERE clause. It is always recommended to include ORDER BY Clause in order to sort records. Don't rely on possible implicit sorting of GROUP BY Clause.
              517 Players ** Avg. Correct: 70% ** Avg. Time: 627 seconds ** Rating: 4 stars

27 August 2011: The NULL value is not a value like "abc" or 123. It actually means "there is no value" and is handled very differently by Oracle from any non-NULL values.
              526 Players ** Avg. Correct: 79% ** Avg. Time: 301 seconds ** Rating: 4 stars

3 September 2011: Use of analytic function COUNT with the RANGE clause to count records with values within defined ranges.
             523 Players ** Avg. Correct: 82% ** Avg. Time: 2070 seconds ** Rating: 4 stars

Oracle Application Express Quizzes

Between 13 August - 9 September, Oracle technologists played these APEXquizzes:

13 August 2011: Use validation functions for plug-in items so that you can build validations into the plug-in, and avoid writing validations on each page where a relevant item appears. Use built-in APEX procedures to communicate errors to the user.
              237 Players ** Avg. Correct: 62% ** Avg. Time: 755 seconds ** Rating: 4 stars

20 August 2011: Dynamic actions are one of most exciting features of APEX 4.  They allow actions to be performed that previously would have been done with jQuery. As well as the built-in dynamic actions you also have the option of entering your own Javascript. Some issues that may arise when incorrectly coding dynamic actions are outlined below as well as the best pratices.
              232 Players ** Avg. Correct: 56% ** Avg. Time: 983 seconds ** Rating: 4 stars

27 August 2011: By using a combination of application processes, page groups and conditions you can
conditionally hide or show items in the application. Page groups allow the applications pages to be easily grouped and subsequently identified as to which pages they are related too and what part of the application they are used in. It helps to understand the different syntax that can be used within a condition.  It highlights two different methods of setting a condition with a "PL/SQL Expression" and the "Value of Item in Expression 1 = Expression 2".
              241 Players ** Avg. Correct: 62% ** Avg. Time: 1939 seconds ** Rating: 4 stars

3 September 2011: This question will teach users how the APEX data dictionary views can be used to find out information about the applications in the workspace.
                226 Players ** Avg. Correct: 59% ** Avg. Time: 913 seconds ** Rating: 4 stars

Mysteries of Implicit Conversions (7254)

The 8 August quiz asked you to determine how many conversions Oracle performs for us implicitly when the following block is executed:

DECLARE
   l_timestamp   TIMESTAMP := '2011-08-01';
   l_increment   PLS_INTEGER := SUBSTR ('10x10=100', 4, '2');
BEGIN
   DBMS_OUTPUT.put_line (
      TO_DATE (l_timestamp) + (l_increment || 0));
END;
/

We scored 8 as the correct answer. Two players wrote with the following observations:

1. The strange thing here is the fact that over the different versions, Oracle has changed the overloadings of PUT_LINE in the DBMS_OUTPUT package. Specifically, in Oracle 7.3.4.0.0 there were 3 overloads, for VARCHAR2, NUMBER and DATE. This was even explicitly mentioned in the following: http://www.en8848.com.cn/reilly%20books/books/oracle/advprog/ch02_08.htm In Oracle 8.1.7.4.1, there remained only two overloads, for VARCHAR2 and NUMBER. In Oracle 10g and higher there remained only a single overload, for VARCHAR2. Why did Oracle do this ? Just for introducing an additional implicit conversion ? I even remember a version where using DBMS_OUTPUT.put_line( a_number ) used to fail, and you had to use an explicit DBMS_OUTPUT.put_line( TO_CHAR(a_number) ). In the older versions, when the overloads exist, the argument passed was indeed stored in the DBMS_OUTPUT buffer in its native data type, and only converted to a string when retrieved for display (ex. by SQL*PLUS ). This one is probably the one that many players have missed ... In fact, we use this "bad practice" day by day in the quiz, DBMS_OUTPUT.put_line is the top star of each pl/sql block ... Does anybody think that we should change this ? I would hardly believe it ... 

By the way, enabling the PLSQL_WARNINGS do NOT produce any warnings for any of these 8 implicit conversions, strange by itself ... ( tested in Oracle 11.1.0.7.0 ). 

Anyway, this quiz was a too high price payed for a small issue ... Like in the last playoff, 0 or 7 versus the correct 8 were scored the same :( :( ... which is not completely right in my opinion, because here recognizing each implicit conversion by itself has its own value as "piece of knowledge", and cannot/should not be completely cancelled ... Specifically for such "total" quizzes, better than making them single choice is to allow for different choices with different "weights" (and scoring), that would reflect knowledge more accurately.

2. I think there may be two additional implicit conversions that are taking place, revolving around the Date + Number in the dbms_output.put_line statement. Although it is often said that dates are internally stored in a numeric format, it is not the same format as a numeric datatype (See reference to Concepts manual). In addition, although the (+) operation can be used with date variables, the actual (+) operation only takes place with numeric datatypes or any datatype that can be implicitly converted to a numeric datatype (see reference SQL Reference manual). Thus, to perform the operation Date + Number, Oracle is internally converting the Date to a number, performing the addition operation, and then converting the result back to a Date. This implicit conversion is different from the others referenced in the question, as there is no (to_xxx) operation that can explicitly perform this operation; it is all done internally to perform (+) operations with Date datatypes. Thus, the total number of implicit conversions for the block of code is 10. 

From 10.2 Concepts Manual Chapter 26 "Oracle stores numeric data in variable-length format. Each value is stored in scientific notation, with 1 byte used to store the exponent and up to 20 bytes to store the mantissa. The resulting value is limited to 38 digits of precision." "Date data is stored in fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second." From 10.2 SQL Reference Chapter 4, page 3. "You can use an arithmetic operator with one or two arguments to negate, add, subtract, multiply, and divide numeric values. Some of these operators are also used in datetime and interval arithmetic. The arguments to the operator must resolve to numeric datatypes or to any datatype that can be implicitly converted to a numeric datatype."

Your thoughts?

SAVE EXCEPTIONS and LOG ERRORS: objections raised (7253)

The 7 September quiz asked you to compare and contrast the use of SAVE EXCEPTIONS and LOG ERRORS, two mechanisms for continuing past errors raised in DML statements.

Three players wrote with objections, two concerning this choice, which was scored as correct:

When you use SAVE EXCEPTION with FORALL statement, if any of the DML statements fail, changes made to rows by those DML statements are rolled back. When you use LOG ERRORS with your DML statement and that statement fails, Oracle will not necessarily roll back all changes to rows modified by that statement.

1. "I think that this choice is scored wrongly. If we use FORALL with SAVE EXCEPTIONS, the changes performed by the previous DML-s ( the previous successful iterations of the same FORALL statement ) ARE NOT rolled back."

2.  "I suppose, that when you use "LOG ERRORS" clause, words "statement fails" mean only one thing: that number of invalid records have exceeded LIMIT value, and statement immediately stopped with exception. In that case, as I know, Oracle will necessarily roll back all changes to rows modified by that statement. In other hand, when number of erroneous records has not reached LIMIT value, your statement does not fail, it finished successfully."

My response to #1: I don't see this point. We are saying in the choice that the changes made by any failed statement are rolled back. We don't even talk about successful statements - and you are right, those are not rolled back.

My response to #2: ARGH! I think you make a very good point; we were a bit sloppy with words here. The DML statement as a whole does not fail, just because an attempt to change a row identified by that statement fails. The statement, in fact, continues to execute.

One player also wrote regarding this choice:

You can use FORALL SAVE EXCEPTIONS with dynamic SQL, but LOG ERRORS may only be used with static DML statements.

He said: "In the answer 4 example, FORALL is NOT part of the dynamic DML. So I doubt the question can be answered in a clear way given this example. I guess my objection will be overruled by pointing out the choice of the phrase "used with dynamic DML". Well, my grandmother could cook a great meal WITH dynamic DML, if necessary."

My response: I don't really get it. You are right; FORALL is not part of either the static or dynamic DML statement that is "attached" to the FORALL header. But my sentence doesn't claim that it is, does it?

It simply asserts that you can do this

FORALL ...
SAVE EXCEPTIONS
EXECUTE IMMEDIATE my_dynamic_dml;

That is, I can "use" FORALL SAVE EXCEPTIONS to execute a dynamic SQL statement.


I am interested to hear your comments; at this moment, though, I am inclined to issue a re-scoring on choice #483, and correct the verbiage to the following (change in bold):

When you use SAVE EXCEPTION with FORALL statement, if any of the DML statements fail, changes made to rows by those DML statements are rolled back. When you use LOG ERRORS with your DML statement and an attempt to update a row fails, Oracle will not necessarily roll back all changes to rows modified by that statement.

Finding Quizzes in Play a Quiz

Since the upgrade to v2 of the PL/SQL Challenge quiz platform, we have taken advantage of its flexibility to add several new quizzes: weekly SQL, PL/SQL and logic quizzes; the newest, Toad for Oracle weekly quiz; the Oracle Magazine PL/SQL 101 quiz.

As a result, the Play a Quiz table on the home page has gotten very full and busy. Players have been complaining about not being able to find the quizzes they need to take, or recently took. We are looking at a number of changes to avoid this confusion. In the meantime, here is an explanation of the steps you can take right now to gain clarity.

The Play a Quiz table has two tabs: Current and Upcoming. Current shows you all open quizzes that you can take, as well as some that you recently completed. You can use the Filter to restrict which quizzes are shown. You can also see other "current" quizzes by clicking on the right arrow in the bottom right corner of the table. Finally, the "View All Competitions" will take you to an Interactive Report that will allow you to search for any quiz that has already been played.

Use the filter to show quizzes for a specific technology, only those that are still open or only those for a technology in which you have expressed interest ("My Technologies"; more on this below).

My Technologies


As you can see, we offer quizzes for PL/SQL, SQL, APEX, Logic and Toad for Oracle (with more likely to come!). By default, we show quizzes for all technologies on the page. And if you take a quiz for one of these technologies, we automatically update your profile, adding this technology to your list.

If you choose "My Technologies" in the filter list, then only those quizzes for your technologies will appear. So if you are not an APEX developer and you've never played an APEX quiz, that is the quickest way to make APEX quizzes disappear from Play a Quiz.

Note: we will likely add the ability to save your Filter by selection so you do not have to keep changing it when you get to the Home page.

To modify your technology list, visit your Account (click on "Account" in top right corner) and then click on "Technologies for Play":

You can then add or remove the technologies as desired:

I hope this information helps you get the most out of the PL/SQL Challenge generally, and the Play a Quiz table more specifically.

Please offer any suggestions you have for improving how this works.

Cheers, Steven

New weekly quiz on Toad for Oracle!

Bob Marley tells us in Coming In From the Cold: "Why do you look so sad and foresaken / When one
door is closed / Don't you know another is open." 


OK, so summer is coming to an end. And it can make us sad to think about colder weather (for those of us up north, anyway!) approaching. So, yes, the "door" of warmth and sun may be closing....but another door at the PL/SQL Challenge is opening: a new quiz launches in September 2011: the Toad for Oracle Weekly Quiz!

Starting on September 3, the PL/SQL Challenge will host a weekly quiz on Toad for Oracle, sponsored by Quest Software. Toad for Oracle is used by hundreds of thousands of Oracle technologists every day. Everyone has their favorite features and there's always more to learn about this incredibly useful tool. Now, you can test,
demonstrate and deepen your expertise in Toad for Oracle with the Weekly Toad Quiz!

Your answers will be ranked, based on correctness and time to answer. Top ranked players will be displayed at ToadWorld and Quest will raffle off ebooks of the Oracle PL/SQL Language Pocket Reference from O'Reilly Media to all those who played the quiz that month.

So if you are a Toad user, this is the perfect opportunity to show how well you know this tool, and learn more about what Toad for Oracle can do for you.