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.

07 July 2010

Players point out workable solution for 6 July 2010 quiz(462)

In the 6 July quiz, we asked you, essentially, which call to DBMS_OUTPUT.PUT_LINE would allow to display the value returned by this cursor:

SELECT SUM (share_price)

FROM nasdaq_listings
WHERE profits > 0 AND sector = 'INTERNET';

None of the choices were correct. But about a dozen players pointed out the following: if you do not provide an alias for an expression in the SELECT list, you can still reference the value returned by the query by putting the expression itself inside double quotes, as in:

DBMS_OUTPUT.PUT_LINE (few_and_far_in_between."SUM(SHARE_PRICE)"

I will add this alternative to the answer text for this question. Several players also commented on this technique and the issue of best practices.
Greg: "When a test involves a very poor coding practice, as in today's (July 6th) quiz which is centered on an unnamed expression without an alias in a cursor, shouldn't we get points for being unknowledgeable about the subject? I mean, since we always use best practices and all ... :)"
Michal: "Hi guys, I unchecked everything in today's quiz. I believe that you can't reference such a column expression by neither of those names. But my point is that I'll never ever need this information. Why? Because not giving an expression a name is a bad practice I do not follow. I would always name it so there is no doubt about how to reference it (of course, I haven't done it from the beginning, it is a result of experience). If you want to teach by showing a bad example and stating tomorrow that it is bad and should not be done then it's probably OK. Although my strictly subjective opinion is that this type of "educational service" should not take this approach. If you're not planning this then in my opinion in this case the challenge fails it's main purpose - to raise the skill level of developers."
Here's our feeling regarding best practices: first, we are still struggling with a way to ask best practice-oriented questions in an unambiguous way. "What is the best solution?" is a very different question from "What is a valid use of a feature?" Second, the point of the quiz is not to promote best practices. It is to test your knowledge of the language. I plan to offer other resources to developers for best practice coding. Michal is right, though. When we have code that includes a best practice, our answer should point this out.
What is your opinion on all this?

3 comments:

Mine opinion is to ask questions like this, no problem. Even adding this nice "SUM(.." answer is acceptable. But in the answer on this correct choice, a warning should be placed that this type of usage is very tricky, because it could be depending on Oracle versions (it works for 10.2 I noticed), because there are no hard rules in documentation how expressions are referenced. An Oracle update could break this code. What would happen if the field was named "SharePrice" in Oracle. Then it becomes "SUM(""SharePrice"")"? I did not test this, leave it as an excercise for the reader.....

Frankly speaking I dnt knw this kind of usage and I really thank to all those who pointed this out :-) As mark said there should be one option to point an usage like this so that we should have tried that out and learned that then itself.

Hope everyone comes here and sees this blog and get this nice little tip.

Wim, neither unquoted nor quoted identifiers are permitted to contain a double-quote; therefore, an expression such as SUM("SharePrice")would require an alias in order to be referenced as a component of a ROWTYPE variable.

Michal's comment, "I'll never ever need this information ... [because it] is a bad practice I do not follow" ignores the sometimes unfortunate reality that some (perhaps much) of the code that developers work with was written by others who may not have followed good programming practices.