How do I get the columns from a REF CURSOR?

When working with dynamic SQL you sometimes have to get the columns (names, types etc.) at runtime because the result isn't always the same. This can easily be achieved when working with a cursor by using dbms_sql.describe_columns, but when you have a SYS_REFCURSOR things get a bit tricky.

Recipe #1 - Oracle 11g only

Since Oracle 11g there's a method called DBMS_SQL.TO_CURSOR_NUMBER(l_rcursor) which you can use to cast a ref cursor into a cursor and use dbms_sql.describe_columns. The following example shows you how to get a list from an SQL SELECT query which is located in a string and dynamically opened into a REF CURSOR.

DECLARE
  TYPE ref_cursor IS REF CURSOR;
  rc_        ref_cursor;
  c_         NUMBER;
  i_         NUMBER;
  col_count_ NUMBER;
  desc_tab_  DBMS_SQL.DESC_TAB;
BEGIN
  OPEN rc_ FOR 'SELECT year, revenue FROM revenues';
  c_ := DBMS_SQL.to_cursor_number(rc_);
  DBMS_SQL.DESCRIBE_COLUMNS(c_, col_count_, desc_tab_);
  FOR i_ IN 1..col_count_ LOOP
    DBMS_OUTPUT.PUT_LINE(desc_tab_(i_).col_name);
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(c_);
END;

If you're working with an Oracle version older than 11g, the possibilities are a bit limited. You could use a Java procedure to get access to more features when working with ref cursors but that's mostly a bit beyond for such a simple problem.

However, if you just want to get the column names, there's one way to achieve that, look at the following example:

DECLARE
  cur_ SYS_REFCURSOR;
  CURSOR get_columns IS
    SELECT t2.column_value.getrootelement() name,
           EXTRACTVALUE(t2.column_value, 'node()') VALUE
    FROM (SELECT * FROM TABLE (XMLSEQUENCE(cur_))) t1,
         TABLE (XMLSEQUENCE(EXTRACT(t1.column_value, '/ROW/node()'))) t2;
BEGIN
  OPEN cur_ FOR 'SELECT 42 the_answer FROM DUAL';
  FOR rec_ IN get_columns LOOP
    DBMS_OUTPUT.put_line(rec_.name || ': ' || rec_.VALUE);
  END LOOP;
END;

What does this little script do? It basically casts a ref cursor into an XMLSequence from which you can get the column names as well. This can be quite handy if you'd like to export a table with an unknown structure into a text file.