09 September 2011

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?

08 September 2011

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.

06 September 2011

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

05 September 2011

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.