Problem

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.

Solution

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 openend 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;

Recipe #2 - Before Oracle 11g

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.