Quick Draw Fail
It rarely pays to answer too quickly.
Yesterday’s question at the PL/SQL Challenge was about aliases for expressions in cursor-based record types.
DECLARE CURSOR profitable_internet_stocks IS SELECT SUM (share_price) FROM nasdaq_listings WHERE profits > 0 AND sector = 'INTERNET'; few_and_far_in_between profitable_internet_stocks%ROWTYPE; BEGIN OPEN profitable_internet_stocks; FETCH profitable_internet_stocks INTO few_and_far_in_between; CLOSE profitable_internet_stocks; /*[display_statement]*/ END;
The question asked what code, inserted where the “display_statement” comment is, would display the result of the query. Without thinking too hard about it, I ticked the fourth option as correct:
DBMS_OUTPUT.PUT_LINE (few_and_far_in_between.sum_share_price);
I had this stupid idea that Oracle would step in and magically create an alias for the column*. A millisecond after clicking “Accept”, I realised I’d got it wrong. I’d assumed that at least one of the answers were likely to be correct, which is obviously not true.
* EDIT: Gary Myers has blogged about this too and explains that Oracle does, indeed, create an alias.
Scott Wesley
8 July 2010 - 12:19 pm
I was fine on this question, first time I responded with nothing. It’s the next day on exceptions that got me with the quick-draw!
As soon as I clicked, I noticed the when-others was listed first, and it occurred to me that exception AND exception was kinda silly.
Dangnebbit.
Jeffrey Kemp
8 July 2010 - 12:51 pm
Heh heh 🙂
You know, I think the hardest questions to answer quickly AND correctly are the ones with subtle errors that I am unlikely to ever make myself. For example, putting “AND” in a list of exceptions just doesn’t make sense so I’d never code it – and so if someone else were to do so it’s too easy to gloss over it and miss the error.