In the 21 January quiz, I asked you to select the choices that would display "Ellison". Two of the choices were PL/SQL blocks containing calls to DBMS_OUTPUT.PUT_LINE. Two were queries. One block and one query were scored as correct. I received the following objections.
1. "There were () at the end of function invocation. PL/SQL syntax would not allow that."
In the 19 January 2011 quiz, you were asked to choose the block containing the most efficient looping through employee data. It was:
BEGIN FOR emp_rec IN (SELECT last_name FROM plch_employees) LOOP DBMS_OUTPUT.put_line (emp_rec.last_name); END LOOP; END; /
The primary lesson of the quiz is that in Oracle Database 10g and higher, with optimization set to at least level 2 (the default), the compiler optimizes cursor FOR loops so that execute at BULK COLLECT-like levels of performance.
One of my students asked for some help on developing a MySQL stored procedure that would conditionally drop a foreign key constraint. I put together a quick example, which compiled fine but failed at run time. With some investigation it appears that either the MySQL 5.5 Documentation in Section 12.6 is incorrect or you can’t [...]
The 13 January quiz tested your knowledge of nested tables, one of the three types of collections available in PL/SQL (two of which, nested tables and varrays, can be manipulated within SQL).
In the 11 January quiz, we asked:
Which of the following statements correctly describe a way to improve the performance, readability or maintainability of this block of code?
BEGIN FOR month_index IN 1 .. 12 LOOP UPDATE monthly_sales SET pct_of_sales = 100 WHERE company_id = 10006 AND month_number = month_index; END LOOP; END;
We scored as correct the following choice:
"Replace all hard-coded literal values with named constants or function calls."
Just got this note from O'Reilly and thought I would pass it along:
Save 60% Ebook & Video Deal of the Day
Top 10 Database/SQL Titles
Some players in the past have expressed concerns about the fact that the daily quiz assumptions do not currently specify English as the language. I have worked with Michael Brunstedt (well, to be honest, he did all the work), one of the quiz reviewers, to come up with the following text for assumptions regarding national language settings. I'd like to get your feedback before putting them into effect.
Tanks in advance for your comments and advice.
The 5 January quiz tested your knowledge or (or, more likely, introduced you to) the Oracle11g fine-grained dependency feature, which maintains dependency information down to columns within tables and parameters within subprograms.
The quiz demonstrates that if I add a column to a query that is executed in a stored program unit, then that the program unit is not invalidated, since that unit couldn't possibly have depended on the new, previously non-existent, column.
We hope that you had a fine new year's celebration (assuming, that is, that you celebrate the new year on 1 January), took some time away from PL/SQL and the rest of your professional responsibilities, and re-charged those batteries.
A new year, a new quarter - and that means it's time for championship playoff for the previous quarter, Q4 of 2010. So a very special "Happy New Year!" and congratulations to the following 45 PL/SQL Challenge players, who will soon be competing for top ranking and prizes.
The first 25 names listed qualified for participation through their ranking. The twenty players after that qualified either as a wildcard entry or through the "correctness" raffle. Check out the FAQ for details.
Dennis Klemme (Germany) ranked 1 with 66 quizzes in 3942 seconds 96.6% correct