Wednesday, May 26, 2010

PL/SQL Challenge not useful if you're not on Oracle11g?

I just received the following email from a person who tried out the PL/SQL Challenge:

"I don't have 11g installed on my home computers, and my workplace hasn't upgraded to it (not until 2011). So not having had a chance to use it or any reason to study the documentation, I'm not going to answer any 11g questions. I'd like to recommend the PL/SQL Challenge to people where I work. My principal professional focus, however, is improving the general SQL and programming (PL/SQL) skills of developers, rather than improving folks understanding of the esoterica of PL/SQL. While I personally do appreciate the PL/SQL challenge (especially in regards to the frustrating quirks of the language), I cannot at this time recommend it for a general audience in the industry I work (Financial Services)."

I found this reaction fascinating on several levels and sent the developer this reply:

I will remove your user account from the PL/SQL Challenge, but please read my response below first and then confirm that you want nothing further to do with it.

I can certainly understand your frustration at being confronted with an 11g question, which generally would seem to be completely irrelevant to what you are doing.

First, allow me to point out that the vast majority of our questions are based on a 10.2 platform; it would surely be hasty to reject the Challenge as a whole because of this question's 11g dependency.

Second, oddly enough, this question is a great example of why the PL/SQL Challenge could be so important for developers who are precisely in your position: not yet on 11g, but surely you know it is coming your way (and for your industry).

The function result cache is an incredibly powerful and important 11g feature; it is definitely not "esoterica."
In fact (1), it is so powerful and important that once you learn about it, it should change the way you write your applications right now, in order to be prepared to take smooth and rapid advantage of it when you upgrade.

In fact (2), I have been presenting for over a year now a presentation titled " Why you should care about Oracle 11g now" (available here) to drive home precisely this point.

Bottom line: I hope you will reconsider, continue playing and encourage others to do so.

And for everyone else: the Challenge is now being played regularly by over 700 developers each day. I hope that you will give it a try yourself. You could learn an awful lot about PL/SQL and you could win some excellent prizes!


João Borges Barreto said...

Hi Steven,

I have a question that is annoying me, and frankly, quite a lot.
[about Result Cache]: "it is so powerful and important that once you learn about it, it should change the way you write your applications right now".
And the "great" question is:

How do I accomplish this? That is, how do I write 11g ready code on a 10gR2.

Some days ago I went through some refactoring on a package, and I wanted to set up result cache for some functions (user and general application parameters) through conditional compilation but with no luck :(.

I currently use 10gR2, and I will have to wait quite a bit for 11g.

Thanks a lot,
João Barreto

Steven Feuerstein said...

It's easy to prepare for the 11g result cache feature!

Stop writing queries in your application code. Instead, put EVERY SINGLE QUERY inside its own function, and call the function.

Then when you upgrade, you add RESULT_CACHE to the function and you are done.

Does that answer the question?