The 7 July quiz tested your knowledge of using CASE expressions to implement conditional logic.
One player wrote: "I just marked all of the answers wrong because the CASE statements didn't end with END CASE (the SQL one I reckoned was wrong anyway). Is this an error in quiz, or is it deliberate, or am I wrong about CASE statements needing to end with 'END CASE'? I would have marked at least one answer as correct if it wasn't for that."
Unfortunately, this player was wrong, but the confusion is very understandable.
When you use CASE in PL/SQL, you can write either a CASE statement or a CASE expression.
A CASE statement requires an "END CASE" for termination.
A CASE expression is terminated only with an "END".
I don't really know why Oracle took these different approaches. Any thoughts out there?
The PL/SQL Challenge (www.plsqlchallenge.com) offers a daily quiz on the PL/SQL language, through which thousands of Oracle technologists demonstrate and deepen their knowledge of PL/SQL. This blog contains posts by the PL/SQL Challenge founder, Steven Feuerstein, as well as comments from players.
08 July 2011
05 July 2011
TOO_MANY_ROWS and indeterminate behavior (3574)
The 4 July quiz tested your knowledge of the impact of querying more than one row with a SELECT INTO. The TOO_MANY_ROWS exception is raised, but the question then is: what if anything will the variable(s) in the INTO clause of the SELECT statement be set to?
As we state in the answer:
We have had several quizzes that delve into such "indeterminate" behavior and generally have taken the position that if the actual, day-to-day experience does not seem to be indeterminate (in this case, the variable(s) are populated with the data from the first row fetched), then it is a legitimate topic and answer for a quiz.
Several players raised objections (of course!). One person objected to making a quiz involving undocumented behavior like this an "only one choice correct" quiz. That is too harsh a penalty for getting it "wrong" (which in this case would mean following what the documentation says). I agree with this. We are definitely going to change the question type to "more than one choice might be correct" and rescore. But we will likely also correct the scoring on some of the other choices.
Before I do that, though, I invite players who submitted concerns and code examples to post them on this blog. They've made some very interesting discoveries!
As we state in the answer:
According to Oracle documentation, the state of a variable that you SELECT INTO will be undefined if the statement raises TOO_MANY_ROWS....In practice, however, it seems that the data from the first row returned by the query is assigned to the variable(s) in the INTO clause; only when the second row is fetched is the TOO_MANY_ROWS exception raised.
We have had several quizzes that delve into such "indeterminate" behavior and generally have taken the position that if the actual, day-to-day experience does not seem to be indeterminate (in this case, the variable(s) are populated with the data from the first row fetched), then it is a legitimate topic and answer for a quiz.
Several players raised objections (of course!). One person objected to making a quiz involving undocumented behavior like this an "only one choice correct" quiz. That is too harsh a penalty for getting it "wrong" (which in this case would mean following what the documentation says). I agree with this. We are definitely going to change the question type to "more than one choice might be correct" and rescore. But we will likely also correct the scoring on some of the other choices.
Before I do that, though, I invite players who submitted concerns and code examples to post them on this blog. They've made some very interesting discoveries!
UNION ALL and sorting - first objection for a SQL quiz! (3466)
The SQL quiz on GROUPING SETS (week of 25 June) raised the following objection from a player:
Since I am fortunate enough to have authors and reviewers with much more depth in SQL than I working on the SQL quiz, I will post this objection and leave it to them (and the rest of you) to respond. Then we will conclude a next step for this quiz.
"I don't think the second choice (the "do-it-yourself implementation") is correct, because it relies on the fact, that the UNION ALL operator preserves the order of the rows returned by subqueries. This seems to be true in recent versions of Oracle database, but I cannot find it documented anywhere, so it should not be relied on. Or am I missing something here?"
Since I am fortunate enough to have authors and reviewers with much more depth in SQL than I working on the SQL quiz, I will post this objection and leave it to them (and the rest of you) to respond. Then we will conclude a next step for this quiz.
04 July 2011
Q2 2011 PL/SQL Championship Playoff Participants
We are pleased, indeed, to present to you the 44 players who have qualified to participate in the Q2 2011 PL/SQL Championship Playoff. For more details on how these players are selected, check out the FAQ. It takes lots of discipline and knowledge to make it to the playoffs, and we congratulate every single one of you.
We currently plan to hold the playoff on Thursday, 21 July, though that could change if enough players cannot participate on that day.
We currently plan to hold the playoff on Thursday, 21 July, though that could change if enough players cannot participate on that day.
_Nikotin (Russia) Ranked 1 (Ranking) Frank Schrader (Germany) Ranked 2 (Ranking) kowido (No Country Set) Ranked 3 (Ranking) Christopher Beck (United States) Ranked 4 (Ranking) Joaquin Gonzalez (Spain) Ranked 5 (Ranking) DikkieDick (Netherlands) Ranked 6 (Ranking) Jeff Kemp (Australia) Ranked 7 (Ranking) Pavel Zeman (Czech Republic) Ranked 8 (Ranking) Siim Kask (Estonia) Ranked 9 (Ranking) Dennis Klemme (Germany) Ranked 10 (Ranking) Mojibul Hoque (Bangladesh) Ranked 11 (Ranking) Jerry Bull (United States) Ranked 12 (Ranking) mentzel.iudith (Israel) Ranked 13 (Ranking) Filipe Silva (Portugal) Ranked 14 (Ranking) Mike Pargeter (United Kingdom) Ranked 15 (Ranking) Ludovic Szewczyk (Belgium) Ranked 16 (Ranking) Sean Stuber (United States) Ranked 17 (Ranking) Alexander Polivany (Ukraine) Ranked 18 (Ranking) Viacheslav Stepanov (Russia) Ranked 19 (Ranking) Hans Van Der Wildt (Belgium) Ranked 20 (Ranking) Rajesh Venkataramani (India) Ranked 21 (Ranking) Dalibor Kovač (Croatia) Ranked 22 (Ranking) clee (United States) Ranked 23 (Ranking) Randy Gettman (United States) Ranked 24 (Ranking) Kevan Gelling (Isle of Man) Ranked 25 (Ranking) Gary Myers (Australia) Ranked 30 (Wildcard) sbramhe (No Country Set) Ranked 31 (Wildcard) João Barreto (Portugal) Ranked 32 (Wildcard) Chris Saxon (United Kingdom) Ranked 33 (Wildcard) Anna Onishchuk (Ireland) Ranked 35 (Wildcard) Theo Asma (Netherlands) Ranked 38 (Wildcard) Q (Belgium) Ranked 42 (Wildcard) xtender (Russia) Ranked 55 (Wildcard) Marc Thompson (Australia) Ranked 58 (Correctness) james su (Canada) Ranked 65 (Correctness) owbeg (Ukraine) Ranked 69 (Correctness) RajenB (Mauritius) Ranked 102 (Correctness) Suresh Nair (United States) Ranked 122 (Wildcard) macabre (Russia) Ranked 126 (Correctness) Rosemary (United States) Ranked 173 (Correctness) Arockia Arul Sekar (India) Ranked 246 (Correctness) Tas Kartas (Australia) Ranked 261 (Correctness) Gerry171 (United States) Ranked 397 (Correctness) NickL (United Kingdom) Ranked 490 (Correctness)
How to View SQL and APEX Rankings
With the advent of a wide array of new quizzes (SQL, APEX, Logic - and more to come!), you will also naturally want to see rankings for those quizzes.
Today, you can view rankings for these other technologies as follows:
First, on the home page in myPerformance, simply choose a different technology from the dropdown list and you will see the playing stats for that technology.
Second, on the Classic Rankings page, you can also select the technology:
and then set the time period in which you are interested:
You cannot yet view rankings for other than PL/SQL technologies on the Interactive Rankings page. We hope to have that implemented this week.
Enjoy and best of luck in the competitions!
Steven Feuerstein
Today, you can view rankings for these other technologies as follows:
First, on the home page in myPerformance, simply choose a different technology from the dropdown list and you will see the playing stats for that technology.
Second, on the Classic Rankings page, you can also select the technology:
and then set the time period in which you are interested:
You cannot yet view rankings for other than PL/SQL technologies on the Interactive Rankings page. We hope to have that implemented this week.
Enjoy and best of luck in the competitions!
Steven Feuerstein
Subscribe to:
Posts (Atom)