Obtaining the names of columns in a dynamic SELECT

Question:
I store SELECT statements in a relational table, and then retrieve and execute them dynamically in PL/SQL, as specified by the user. I need to obtain the names of the columns in my queries, to use in the display of the headers. How can I get this information?
Answer:
Oracle provides a wonderfully useful utility in the DBMS_SQL package—the DESCRIBE_COLUMNS procedure—to solve this problem.
You pass DBMS_SQL.DESCRIBE_COLUMNS an already parsed cursor, and it returns a collection of records, each element of which gives you information about a column or an expression in the SELECT list of the query.
DBMS_SQL.DESCRIBE_COLUMNS allows you to describe the columns of your dynamic cursor, returning information about each column in an associative array of records. This capability offers you the possibility of writing very generic cursor-processing code; this procedure will come in particularly handy when you are writing Method 4 dynamic SQL and you are not certain how many columns are being selected. It also allows you to easily obtain the names of each of those columns.
To use this procedure, you need to have declared a PL/SQL collection based on the DBMS_SQL.DESC_TAB collection type (or DESC_TAB2, if your query might return column names that are longer than 30 characters). You can then traverse the table and extract the needed information about the cursor. The anonymous block in Listing 4, available with the online version of this column, shows the basic steps you will perform when working with the DBMS_SQL.DESCRIBE_COLUMNS built-in. (To simplify the code, I assume that the datatypes of all columns are VARCHAR2 or implicitly convertible to VARCHAR2.)
Code Listing 4: Anonymous block using DBMS_SQL.DESCRIBE_COLUMNS
DECLARE
-- This query might be read from a table...
l_query VARCHAR2 ( 4000 ) :=
'SELECT last_name, salary FROM employees';
l_cursor PLS_INTEGER := DBMS_SQL.open_cursor;
l_columns DBMS_SQL.desc_tab2;
l_numcols PLS_INTEGER;
l_value VARCHAR2 ( 4000 );
l_feedback PLS_INTEGER;
BEGIN
-- Parse the query.
DBMS_SQL.parse ( l_cursor, l_query, DBMS_SQL.native );

-- Retrieve column information
DBMS_SQL.describe_columns2 ( l_cursor, l_numcols, l_columns );

-- Define each of the column names (and display column names)
FOR colind IN 1 .. l_numcols
LOOP
-- Specify maximum size of the string being retrieved.
DBMS_SQL.define_column ( l_cursor, colind, l_value, 4000 );
DBMS_OUTPUT.put_line ( l_columns ( colind ).col_name );
END LOOP;

-- Now execute the query....
l_feedback := DBMS_SQL.EXECUTE ( l_cursor );

LOOP
EXIT WHEN DBMS_SQL.fetch_rows ( l_cursor ) = 0;

FOR colind IN 1 .. l_numcols
LOOP
-- Retrieve each value and display it.
DBMS_SQL.COLUMN_VALUE ( l_cursor, colind, l_value );
DBMS_OUTPUT.put_line ( l_columns ( colind ).col_name
|| ' = '
|| l_value
);
END LOOP;
END LOOP;

-- Cleanup
DBMS_SQL.close_cursor ( l_cursor );
END;
/