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.

   CURSOR profitable_internet_stocks
      SELECT SUM (share_price) FROM nasdaq_listings
       WHERE profits > 0 AND sector = 'INTERNET';
   few_and_far_in_between   profitable_internet_stocks%ROWTYPE;
   OPEN profitable_internet_stocks;
   FETCH profitable_internet_stocks
   INTO few_and_far_in_between;
   CLOSE profitable_internet_stocks;

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.

2 thoughts on “Quick Draw Fail

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



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


Comments are closed.