PL/SQL Challenge

31 July 2012

Results from Q2 2012 Championship Playoff

You will find below the rankings for the Q2 2012 playoff; the number next to the player's name is the number of times that player has participated in a playoff.

Congratulations first and foremost to our top-ranked players:

1st Place: kowido wins Amazon.com US$250 Gift Card .
2nd Place: Justin Cave of United States wins Amazon.com US$175 Gift Card.
3rd Place: Frank Schrader of Germany wins Amazon.com US$100 Gift Card

In the Q1 2012 playoff, the highest % correct was 87. In this playoff, five players exceeded 90% accuracy, with our second-place winner actually getting every single choice correct! Nice work, Justin. You might then be asking yourself: "Why is Justin in second place if he got every choice correct?" That's because kowido, the first-place winner, was almost as correct (only 2 wrong out of 23 choices - a performance matched by three others) and he submitted his answers in about half the time of Justin.

Sometimes it pays to hurry - at least if you really know your stuff. And as you can see from the table below, kowido squeaked into first place with just one more point than Justin.

Congratulations to everyone who played in the playoff. I hope you found it entertaining, challenging and educational. And for those who were not able to participate in the playoff, you can take the quizzes next week through the Practice feature.

Steven Feuerstein

PS - Below the table of results for this playoff, you will find another list showing the playoff history of each of these players.

Playoff Results

Rank	Name	Country	Total Time	% Correct	Total Score
1	kowido (6)	No Country Set	16 mins 04 secs	91%	2879
2	Justin Cave (6)	United States	31 mins 06 secs	100%	2878
3	Frank Schrader (8)	Germany	19 mins 27 secs	91%	2811
4	Frank Schmitt (2)	Germany	15 mins 06 secs	87%	2748
5	Niels Hecker (8)	Germany	30 mins 42 secs	91%	2586
6	Siim Kask (7)	Estonia	25 mins 58 secs	87%	2531
7	Giedrius Deveikis (1)	Lithuania	17 mins 21 secs	83%	2518
8	Dieter Kowalski (2)	Germany	27 mins 16 secs	87%	2470
9	Sean Stuber (5)	United States	34 mins 57 secs	91%	2466
10	Scott Wesley (2)	Australia	15 mins 15 secs	78%	2410
11	Anna Onishchuk (6)	Ireland	33 mins 52 secs	87%	2373
12	Viacheslav Stepanov (6)	Russia	32 mins 38 secs	87%	2362
13	Rakesh Dadhich (1)	India	17 mins 34 secs	74%	2324
14	Jerry Bull (5)	United States	23 mins 49 secs	78%	2324
15	Ivan Blanarik (2)	Slovakia	34 mins 56 secs	87%	2316
16	Vincent Malgrat (3)	French Republic	25 mins 48 secs	83%	2274
17	Sebastian Kolski (2)	Poland	30 mins 18 secs	83%	2259
18	james su (5)	Canada	24 mins 36 secs	74%	2098
19	mentzel.iudith (7)	Israel	34 mins 52 secs	78%	2018
20	Mike Pargeter (7)	United Kingdom	20 mins 03 secs	70%	2014
21	Kuzmin Konstanin (1)	No Country Set	16 mins 59 secs	70%	2000
22	Randy Gettman (7)	United States	29 mins 30 secs	74%	1935
23	_tiki_4_ (3)	Germany	24 mins 23 secs	70%	1887
24	swart260 (1)	Netherlands	19 mins 26 secs	65%	1876
25	Kevan Gelling (6)	Isle of Man	31 mins 01 secs	70%	1870
26	Chad Lee (5)	United States	34 mins 28 secs	74%	1861
27	Yuan Tschang (4)	United States	34 mins 55 secs	74%	1827
28	Syed Ariful Bari (4)	Bangladesh	08 mins 05 secs	57%	1778
29	Zoltan Fulop (2)	Hungary	32 mins 43 secs	65%	1671
30	Goran Stefanović (2)	Serbia	34 mins 46 secs	65%	1620
31	Anil Jha (1)	United States	26 mins 18 secs	57%	1389
32	Vinu (1)	India	29 mins 38 secs	48%	1072
33	Stelios Vlasopoulos (4)	Belgium	29 mins 57 secs	48%	1041

Playoff Performance History

After each name, the quarter in which he or she played, and the ranking in that playoff.

Name	History
kowido	Q1 2011:25th, Q2 2011:9th, Q3 2011:16th, Q4 2011:20th, Q1 2012:4th, Q2 2012:1st
Justin Cave	Q3 2010:26th, Q4 2010:3rd, Q1 2011:5th, Q3 2011:3rd, Q1 2012:25th, Q2 2012:2nd
Frank Schrader	Q3 2010:12th, Q4 2010:30th, Q1 2011:1st, Q2 2011:5th, Q3 2011:1st, Q4 2011:1st, Q1 2012:13th, Q2 2012:3rd
Frank Schmitt	Q4 2011:23rd, Q2 2012:4th
Niels Hecker	Q2 2010:2nd, Q3 2010:1st, Q4 2010:15th, Q1 2011:7th, Q3 2011:7th, Q4 2011:10th, Q1 2012:2nd, Q2 2012:5th
Siim Kask	Q1 2011:29th, Q2 2011:7th, Q3 2011:10th, Q4 2011:3rd, Q1 2012:9th, Q2 2012:6th
Giedrius Deveikis	Q2 2012:7th
Dieter Kowalski	Q1 2012:21st, Q2 2012:8th
Sean Stuber	Q3 2010:5th, Q1 2011:21st, Q2 2011:11th, Q1 2012:16th, Q2 2012:9th
Scott Wesley	Q3 2010:30th, Q2 2012:10th
Anna Onishchuk	Q1 2011:6th, Q2 2011:22nd, Q3 2011:20th, Q4 2011:17th, Q1 2012:24th, Q2 2012:11th
Viacheslav Stepanov	Q1 2011:9th, Q2 2011:4th, Q3 2011:13th, Q4 2011:19th, Q1 2012:19th, Q2 2012:12th
Rakesh Dadhich	Q2 2012:13th
Jerry Bull	Q2 2011:33rd, Q3 2011:8th, Q1 2012:14th, Q2 2012:14th
Ivan Blanarik	Q1 2012:3rd, Q2 2012:15th
Vincent Malgrat	Q4 2011:9th, Q1 2012:15th, Q2 2012:16th
Sebastian Kolski	Q1 2012:22nd, Q2 2012:17th
james su	Q3 2010:56th, Q2 2011:8th, Q3 2011:11th, Q4 2011:14th, Q2 2012:18th
mentzel.iudith	Q4 2010:4th, Q1 2011:17th, Q2 2011:24th, Q3 2011:5th, Q4 2011:4th, Q1 2012:8th, Q2 2012:19th
Mike Pargeter	Q4 2010:22nd, Q1 2011:15th, Q2 2011:10th, Q4 2011:5th, Q1 2012:6th, Q2 2012:20th
Kuzmin Konstanin	Q2 2012:21st
Randy Gettman	Q3 2010:8th, Q1 2011:26th, Q2 2011:12th, Q3 2011:4th, Q4 2011:11th, Q1 2012:20th, Q2 2012:22nd
_tiki_4_	Q4 2011:27th, Q1 2012:18th, Q2 2012:23rd
swart260	Q2 2012:24th
Kevan Gelling	Q2 2011:21st, Q3 2011:2nd, Q4 2011:6th, Q1 2012:7th, Q2 2012:25th
Chad Lee	Q2 2011:27th, Q3 2011:18th, Q4 2011:12th, Q1 2012:1st, Q2 2012:26th
Yuan Tschang	Q2 2012:27th
Syed Ariful Bari	Q3 2011:19th, Q4 2011:28th, Q1 2012:32nd, Q2 2012:28th
Zoltan Fulop	Q1 2012:17th, Q2 2012:29th
Goran Stefanović	Q1 2012:34th, Q2 2012:30th
Anil Jha	Q2 2012:31st
Vinu	Q2 2012:32nd
Stelios Vlasopoulos	Q4 2010:38th, Q4 2011:21st, Q1 2012:30th, Q2 2012:33rd

06 July 2012

High Achievers at the PL/SQL Challenge

As Chicago endures another 100 degree day, I thought it would be a good time to honor some of our high achievers: players who were 100% correct for an entire month and those who played every quiz for an entire year or quarter. Even if your temperatures were not very high as you took all these quizzes, it certainly required a lot of effort!

05 July 2012

Site Still Down 4:45 UTC 5 July

Unfortunately, the PL/SQL Challenge website is still down, and will likely not be up for several hours more.

So it looks like we will be not doing a quiz today (5 July) either.

We are as disappointed with this state of affairs as the many players who have contacted me directly, wondering when they will be able to play their quiz.

Such dedication! I hate to disappoint you, but, well, look at it this way: we are giving you a little vacation from the quiz. It's July, so for most of you the sun is shining, the air is warm. In other words, it's time to go outside and play.

Don't worry, we'll be back up soon and then you can come back inside and take the quiz.

Thanks for your patience,
Steven Feuerstein

04 July 2012

Quiz for 4 July Will Not Be Ranked

The PL/SQL Challenge website has been down for most of the day, as we recover the database from 02:00 UTC 4 July. Our apologies for the inconvenience and lost opportunity to compete.

The results for 4 July will not be used in rankings and determining qualification in the Q3 championship playoff.

So if you are an American, it's just our way of saying: "Don't work (or 'play' with your technology)! Get away from your computer and celebrate the founding of your nation!"

Sigh.....

02 July 2012

Time for the Q2 2012 Championship Playoff!

The second quarter of 2012 ended last week...and that means it's time for the next championship playoff.

As you may recall, last quarter we initiated a new qualifier process in which selected players are asked to verify their performance during the quarter before being able to participate in the playoff.

I am very pleased to announce that none of the players this quarter will be required to take qualifier quizzes. What this means, in essence, is that none of the players submitted answers in astonishing and perhaps bewildering shorts amounts of time.

There is one other unusual aspect to the player group for this playoff: no players were able to qualify under the Wildcard category (see the FAQ for more information on how players are chosen).

In the table below, the number in parentheses after their names are the number of playoffs in which they have already participated. Congratulations to all listed on their accomplishment and best of luck in the upcoming competition!

We currently plan to hold the playoff on 26 July; that date may change if enough players are unable to play on that day.

Name	Rank	Qualification	Country
Frank Schrader (7)	1	Top 25	Germany
Stelios Vlasopoulos (3)	2	Top 25	Belgium
mentzel.iudith (6)	3	Top 25	Israel
Viacheslav Stepanov (5)	4	Top 25	Russia
Justin Cave (5)	5	Top 25	United States
Rakesh Dadhich (0)	6	Top 25	India
Jerry Bull (4)	7	Top 25	United States
kowido (5)	8	Top 25	No Country Set
Siim Kask (6)	9	Top 25	Estonia
Vincent Malgrat (2)	10	Top 25	French Republic
Sebastian Kolski (1)	11	Top 25	Poland
Anna Onishchuk (5)	12	Top 25	Ireland
_tiki_4_ (2)	13	Top 25	Germany
Ivan Blanarik (1)	14	Top 25	Slovakia
Niels Hecker (7)	15	Top 25	Germany
Chad Lee (4)	16	Top 25	United States
james su (4)	17	Top 25	Canada
Dieter Kowalski (1)	18	Top 25	Germany
swart260 (0)	19	Top 25	Netherlands
Scott Wesley (1)	20	Top 25	Australia
Sean Stuber (4)	21	Top 25	United States
Randy Gettman (6)	22	Top 25	United States
Giedrius Deveikis (0)	23	Top 25	Lithuania
Zoltan Fulop (1)	24	Top 25	Hungary
Mike Pargeter (6)	25	Top 25	United Kingdom
Kevan Gelling (5)	26	Correctness	Isle of Man
Thierry Poels (3)	63	Correctness	Belgium
Syed Ariful Bari (3)	84	Correctness	Bangladesh
Yuan Tschang (3)	86	Correctness	United States
Vinu (0)	90	Correctness	India
Goran Stefanović (1)	100	Correctness	Serbia
anil_jha (0)	105	Correctness	United States
Frank Schmitt (1)	114	Correctness	Germany
John Rowbottom (1)	200	Correctness	United Kingdom
Kuzmin Konstanin (0)	224	Correctness	No Country Set

30 June 2012

PL/SQL Challenge at Kscope12

I returned a few days ago from ODTUG's annual Kscope (formerly Kaleidoscope) conference, this year in hot, hot San Antonio, but located at the very comfortable and luxurious JW Marriott resort.

At the suggestion of Mike Brunstedt, one of our fabulous PL/SQL quiz reviewers, I made up a PL/SQL Challenger ribbon so that players and friends of the site could proudly proclaim their Challenger-ness:

We will also be giving them out at Oracle Open World in the Fall, so there's another reason to attend Larry's outsized celebration of all things Oracle.

It was great to meet players of the PL/SQL Challenge, and hear about how playing the quizzes has helped them in their work. It was even more of a pleasure to meet up with some of the reviewers who ensure the high quality of quizzes we play on the site:

From left to right: Michael Brunstedt, Patrick Barel, Steven Feuerstein, Kim Berg Hansen

The PL/SQL Challenge wouldn't exist at all without the initial support and ongoing efforts by my Application Express expert friends at Apex Evangelists, so I demanded that they also join me for a photo:

From left to right: Dimitri Gielis, John Scott, Steven Feuerstein, Paul Broughton

Kscope12 was the usual fantastic conference, organized oh-so-well by Kathleen McCasland and her posse of capable women from Your Conference Connection, led by a friendly, enthusiastic board of directors.

Hope to see you there next year!

22 June 2012

PL/SQL Bug Confirmed from 20 June Quiz

The 20th June quiz tested your knowledge of how to remove "gaps" from a sparse array (make it dense). Daniel Kennedy posted an objection to the scoring, saying that if you use a NULL value for the index value in the collection, then none of the choices are correct.

This was a big surprise to many of us, who have long seen in our code that the idex value in an integer-indexed array cannot be NULL. Well....after checking with Bryn Llewellyn, Oracle PL/SQL Product Manager, I confirmed that this was a bug - and one that had not yet been reported!

So Bryn filed a bug report (1422744) with the explanation and code shown below.

Many aspects of the PL/SQL Challenge make me proud (the large number of players, their dedication, the growing body of knowledge about PL/SQL accessible in formats never available before, and so on). But when we identify bugs in the language or fixes required in the documentation, then I feel even more strongly how much the PL/SQL language can benefit from an active, engaged community.

Thanks, Daniel, for identifying this bug and for playing the PL/SQL Challenge.

Steven Feuerstein

v(null) := 42 succeeds when v is an IBBI – and brings Heisenberg exception handling
(from Bryn Llewellyn)

The testcase is copied below. Run it as any user with at least Create Session and Create Procedure.

The behavior of the varray and nested table tests is exactly as expected. Without the exception handling code, ORA-06502 is reported with a ORA-06512 for Line 9. And with the exception handling code, ORA-06502 is caught and nicely reported, and the program completes with no unhandled error.

However, IBBI_Test_1 runs without error and prints out 42. This is clearly a bug. Null is not a value. Rather, it's an indicator that expresses that the value is unknown.

Now it gets worse. IBBI_Test_2 simply adds the identical exception handler code for ORA-06502 around [v(null) := 42;] that Varray_Test_2 and Nested_Table_Test_2 use. But at run-time, we get this:

 
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: NULL index table key value
ORA-06512: at "USR.IBBI_TEST_2", line 13
ORA-06512: at line 1

And finally, it gets worse still. IBBI_Test_3 adds more exception handling code. This time around [a := v(null);]. At run time, ORA-06502 is now obligingly caught at both [ v(null) := 42; ] and [ a := v(null); ].

------ Testcase ------
 
alter session set Plsql_Warnings = 'Enable:All'
/
 
create procedure Varray_Test_1 authid Definer is
  type t is varray(10) of pls_integer;
  v t;
  a pls_integer;
  n pls_integer;
  -- PL/SQL: numeric or value error: NULL index table key value
  ORA_06502 exception; pragma Exception_Init(ORA_06502, -06502);
begin
  v(null) := 42; -- Line 9
end Varray_Test_1;
/
begin Varray_Test_1(); end;
/
 
create procedure Varray_Test_2 authid Definer is
  type t is table of pls_integer;
  v t;
  a pls_integer;
  n pls_integer;
  -- PL/SQL: numeric or value error: NULL index table key value
  ORA_06502 exception; pragma Exception_Init(ORA_06502, -06502);
begin
  begin
    v(null) := 42;
  exception when ORA_06502 then
    DBMS_Output.Put_Line('ORA-06502 on "v(null) := 42"');
  end;
end Varray_Test_2;
/
begin Varray_Test_2(); end;
/
 
create procedure Nested_Table_Test_1 authid Definer is
  type t is table of pls_integer;
  v t;
  a pls_integer;
  n pls_integer;
  -- PL/SQL: numeric or value error: NULL index table key value
  ORA_06502 exception; pragma Exception_Init(ORA_06502, -06502);
begin
  v(null) := 42; -- Line 9
end Nested_Table_Test_1;
/
begin Nested_Table_Test_1(); end;
/
 
create procedure Nested_Table_Test_2 authid Definer is
  type t is table of pls_integer;
  v t;
  a pls_integer;
  n pls_integer;
  -- PL/SQL: numeric or value error: NULL index table key value
  ORA_06502 exception; pragma Exception_Init(ORA_06502, -06502);
begin
  begin
    v(null) := 42;
  exception when ORA_06502 then
    DBMS_Output.Put_Line('ORA-06502 on "v(null) := 42"');
  end;
end Nested_Table_Test_2;
/
begin Nested_Table_Test_2(); end;
/
 
create procedure IBBI_Test_1 authid Definer is
  type t is table of pls_integer index by pls_integer;
  v t;
  a pls_integer;
  n pls_integer;
begin
  v(null) := 42;
  a := v(null);
  DBMS_Output.Put_Line(a);
end IBBI_Test_1;
/
begin IBBI_Test_1(); end;
/
 
create procedure IBBI_Test_2 authid Definer is
  type t is table of pls_integer index by pls_integer;
  v t;
  a pls_integer;
  n pls_integer;
  -- PL/SQL: numeric or value error: NULL index table key value
  ORA_06502 exception; pragma Exception_Init(ORA_06502, -06502);
begin
  begin
    v(null) := 42;
  exception when ORA_06502 then
    DBMS_Output.Put_Line(q'{ORA_06502 on "v(null) := 42"}');
  end; -- Line 13
  a := v(null);
  DBMS_Output.Put_Line(a);
end IBBI_Test_2;
/
begin IBBI_Test_2(); end;
/
 
create procedure IBBI_Test_3 authid Definer is
  type t is table of pls_integer index by pls_integer;
  v t;
  a pls_integer;
  n pls_integer;
  -- PL/SQL: numeric or value error: NULL index table key value
  ORA_06502 exception; pragma Exception_Init(ORA_06502, -06502);
begin
  begin
    v(null) := 42;
  exception when ORA_06502 then
    DBMS_Output.Put_Line(q'{ORA_06502 on "v(null) := 42"}');
  end;
  begin
    a := v(null);
  exception when ORA_06502 then
    DBMS_Output.Put_Line(q'{ORA_06502 on "a := v(null)"}');
  end;
  DBMS_Output.Put_Line(a);
end IBBI_Test_3;
/
begin IBBI_Test_3(); end;
/