Puzzle me this

Like Jeff Kemp, I got yesterday's PL/SQL challenge wrong too.

The question rested on what identifier/alias Oracle would assign to a column derived from an expression if no explicit alias is supplied.

If you have a cursor with such a column, and you base a variable on a ROWTYPE of that cursor, you really want to know the identifier to use. Steve says "The problem is that this expression has not been assigned an alias. As a result, there is no way in my PL/SQL code to refer to that expression". Jeff says "I had this stupid idea that Oracle would step in and magically create an alias for the column".

Actually, Oracle does assign an alias, or at least an identifier. It is just a little more complicated than any of the offered choices (though in this example I've switched the table used so that I don't need to create any special objects)..

DECLARE
   CURSOR profitable_internet_stocks
   IS
      SELECT SUM (num_rows) FROM user_tables;
   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;
   dbms_output.put_line(few_and_far_in_between."SUM(NUM_ROWS)");
END;  

The following block shows how to use DBMS_SQL.DESCRIBE_COLUMNS to determine the column names assigned by Oracle.



DECLARE
  PROCEDURE temp (p_query IN VARCHAR2) IS
    col_cnt   INTEGER;
    rec_tab   dbms_sql.desc_tab2;
    v_tab     dbms_sql.varchar2s;
    v_cursor  NUMBER;
  BEGIN
    v_cursor := dbms_sql.open_cursor;
    dbms_sql.parse(v_cursor, p_query, 1);
    dbms_sql.describe_columns2(v_cursor, col_cnt, rec_tab);
    dbms_sql.close_cursor(v_cursor);
    FOR v_pos in 1..rec_tab.LAST LOOP
      dbms_output.put_line(rpad(rec_tab(v_pos).col_name,32));
    END LOOP;
  END;BEGIN
  temp('SELECT SUM (num_rows) FROM user_tables');
END;
/

Of course it is generally better to use an alias when you have the choice.


Could Oracle be 'smarter' in allocating an identifier ? Probably not. There's nothing wrong with the identifier it came up with except that it has to be expressed as a quoted identifier. If the expression had been a simple numeric value (eg SELECT 1 FROM user_tables) then there isn't an appropriate identifier that it can come up with that doesn't require quoting.

Incidentally, it is the SQL engine, rather than the PL/SQL engine, that assigns these identifiers.
The following shows that the "SUM(NUM_ROWS)" identifier is automatically assigned to the first column as well as being used as the alias for the second. As such, Oracle can't work out what column is being referred to in the outer select.

select "SUM(NUM_ROWS)" from
(SELECT sum(num_rows), 1 as "SUM(NUM_ROWS)" FROM user_tables)