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.
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.
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.