Skip to content

Quick Draw Fail

July 7, 2010

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.

From → PL/SQL

  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.


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


Get every new post delivered to your Inbox.

Join 271 other followers

%d bloggers like this: