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.

22 June 2012

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.

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:

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); ].

19 June 2012

I made a very big mistake today, and the result is that many of you could not play Monday's quiz.

I switched to maintenance mode at 15:00 to fix a bug, but then neglected to return to normal mode of play.

Then, seeing as I am in Taos, NM, I left the world of computers behind for Bandelier National Monument, an amazing place....but as a result, I did not notice that so many of you were notifying me of a problem with the site.

My deepest apologies. We will have to void the results for Monday's quiz.

I will change the site so that maintenance mode is always turned off after a period of time, so that if I happen to forget again, the site will correct itself.