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.

11 June 2011

You will find below the results of the Q1 2011 championship playoff. You can take a look at the quizzes that were in the competition through the Library page. These results are also available through the Rankings page.

Congratulations to all players, especially Frank Schrader (#1), Gary Myers (#2) and Yriy Pedan (#3)! As I note in comments after the rankings, these players had to deal with some tough questions.

This playoff was different from the last three in several ways, most importantly:

This was the first time that the new platform was used for a playoff and overall it worked quite well. Players had much more flexibility than in previous playoffs: they could pick the time they wanted to play, and could move back and forth through the quizzes.

The playoff consisted of just 5 quizzes instead of 10. Players had complained about not having enough time to complete the quizzes, so I decided to reduce the number of quizzes, but keep the same time limit of 20 minutes.

The feedback from players was fairly consistent:

The process was too flexible, and therefore a bit too and unnecessarily complicated. Players could make their selections but not save them (in case they wanted to change them later). But if and when they ran out of time, they were left with unsaved answers, which caused some consternation.

I over-compensated for the smaller number of quizzes by making the the five I did provide a bit too challenging, at least from the aspect of being able to read lots and lots of code and make sense of it in the time allowed.

As one player put it: "The playoff was really good and also really, really hard. I had lots of fun and a smoking head."

My apologies, dear players - but I suppose it is better to err on the side of too hard than too easy. It is, after all, a playoff among top-ranked players!

And, my oh my, it is just three more weeks till the end of the second quarter! So it is time to think about coming up with five more mind-benders for the Q2 playoff. If anyone has any ideas for some clever and interesting quizzes, please submit them!

10 June 2011

Sometimes you may find that your time to answer a quiz is very high (thousands of seconds, for example). This an happen when you start a quiz, but are interrupted, and do not submit till much later. Or you may start a quiz, lose your connection, and then come back later to finish (a "double visit"). In both cases, you end up with a answer time that distorts your performance statistics.

Even if you are not competing for a top spot, it can be a bit embarrassing to show your time to answer quizzes in the 10s of thousands of seconds while others are at 2500.

You can request "forgiveness" for up to five excessive answer times, per quarter (previously you could only request an adjustment for a double visit).

To do this, take the following steps:

1.Visit your Account (link in upper right of site), then drill down to Quizzes Taken (at bottom of Account main menu page).

2. Check the box next to the quizzes you want to adjust, and press the "Adjust Answer Time" button. For double visits, we will change your answer time to the last visit/submit time. For long answer times (and a single visit), your answer time will be reduced to ten minutes.

Note that the impact of your adjustment will not be instantly visible on the page or in rankings. That will happen by the following day, after materialized views are refreshed.

We hope this helps improve your experience at and satisfaction with the PL/SQL Challenge.

08 June 2011

The 7 June quiz tested your knowledge of performing record-level assignments and, in particular, the rules for what determines compatibility between records of different types. When it comes to records, Oracle is quite flexible, as explained in the Lesson Summary:

You can assign a %ROWTYPE record to a user-defined record (and vice-versa) if their fields match in number and order, and corresponding fields have compatible datatypes. This is true even when the record is being passed as an argument to a subprogram.

Several players wrote with concerns about this, based on what they found in the documentation. I offersome of their comments below.

1. Nice quiz and another proof that you can't trust the documentation: it says that you can only assign %ROWTYPE-Records to declared records where the names and datatypes are identical.

2. Surprisingly enough, for today's quiz all the choices are right. Four of them seem to contradict what is explicitly stated in the Oracle PL/SQL Documentation namely: "Assigning One Record Variable to Another You can assign the value of one record variable to another record variable only in these cases: 1. The two variables have the same RECORD type (as in Example 5-42). 2. The target variable is declared with a RECORD type, the source variable is declared with %ROWTYPE, their fields match in number and order, and corresponding fields have the same data type (as in Example 5-43)." So, unfortunately, one who is always using best practices and has never tried to check whether the 2-nd restriction above does indeed hold, was mislead in answering this quiz.

My responses:

1. I have not seen anything in the documentation that specifies that names of fields must be identical. So if a player can provide that link, I would much appreciate it.

2. The second comment reflects, I believe, a mis-reading of the documentation (and makes me think they need to tighten it up). The documentation offers two different rules, but they are to be read as OR not AND. In other words, either the two variables can have the same record type OR their fields match in number and order. In fact, I (and, I expect, Oracle) would argue that the clause "only in these cases:" implies the OR. That is, they are presented as different "cases", not as a single set of requirements, all of which must be true.

07 June 2011

Now that 2.0 of the PL/SQL Challenge is up and running, we are looking ahead to 2.1. Our plan is to add a for-fee membership option that will give you access to special member-only features.

We are still finalizing the list of those features, but one of them will be the QuizBook: the ability to generate PDFs or other document formats that contain one or more quizzes, with all the supporting documentation, essentially creating focused "books" of knowledge about topics in the PL/SQL language (and, over time, APEX and SQL).

One key ingredient to making this happen is to utilize a powerful, flexible report generator. We have just started exploring our options, which clearly include PL/PDF and Jasper Reports.

I am sure that at least some of the Oracle technologists playing the PL/SQL Challenge have some experience in this area, and I hope that we can benefit from that experience to move this project along.

So please either post a comment to this blog or contact me directly at steven@stevenfeuerstein.com, if you have any insights or recommendations regarding this aspect of website development, especially any "gotchas" regarding integration of such of features into an APEX application.

06 June 2011

As you may have noticed last week, IBM is now a premier sponsor of the PL/SQL Challenge!

You might think it odd, at first glance, that IBM, with its own competing relational database technology (DB2) , would sponsor an Oracle PL/SQL-focused website.

In fact, it makes perfect sense, because as you can read right here, DB2 9.7 can run Oracle applications on Linux, UNIX, and Windows!

To quote the link above:

"IBM DB2 9.7 for Linux, UNIX, and Windows has out-of-the-box support for Oracle's SQL and PL/SQL dialects. This allows many applications written against Oracle to execute against DB2 virtually unchanged. In this article, get a high-level overview of what Oracle compatibility means in DB2. Whether you want to switch your custom application to DB2 or extend your DBMS vendor support to DB2, now is your time."

That's right - IBM has implemented support for compilation and execution of PL/SQL program units in the DB2 9.7 database!

IBM's motivation for doing this is clear: they want to make it easier (and far less expensive) to convert from Oracle to DB2. You might then ask: "Why would the PL/SQL Challenge help IBM move customers off of Oracle?" That answer should be clear enough: this website is all about helping PL/SQL programmers make the most of this database programming language. It shouldn't matter if you are building or maintaining applications in an Oracle database or an IBM database. PL/SQL is PL/SQL.

And while from the standpoint of Oracle Corporation, it may not be the greatest thing in the world that PL/SQL programs will become increasingly portable outside of Oracle, that could be a very good thing for the careers of PL/SQL programmers.

IBM does not yet support every single nuance of PL/SQL syntax, built-in packages and underlying architectural elements, but they are working hard on extending their compatibility.

We encourage you to check out IBM's detailed explanation of what they support and how they've gone about implementing their PL/SQL compatibility.