Describe REFCURSORs

In Oracle 11g, I really like the interoperability between DBMS_SQL cursors and REFCURSORs. Thanks God, they are finally friends and can "talk" to each other.
I remember that a few months ago I needed a feature through which to be able to describe a weak ref cursor, that is to know how many columns are in the result set along with the corresponding types for every column. Of course, I knew that DBMS_SQL package provides such a feature but it didn't apply to ref cursors therefore, bad luck!
Now, with the new Oracle 11g, it is possible to convert a ref cursor into a DBMS_SQL cursor and vice-versa so, virtually, this opens new gates, right?... including my problem with describing ref cursors.
Lets take a very simple example. Suppose we have a basic procedure which receives a ref cursor as a parameter and it has to know the structure of that cursor. Well, not a big deal in 11g. Just see below how it works:

SQL> create or replace procedure desc_refcursor(pi_cursor sys_refcursor) is
  2    l_ref sys_refcursor;
  3    l_cursor integer;
  4    l_cols_count integer;
  5    l_desc_tab dbms_sql.desc_tab;
  6 
  7  begin
  8    l_ref := pi_cursor;
  9    l_cursor := dbms_sql.to_cursor_number(l_ref);
 10    dbms_sql.describe_columns(l_cursor, l_cols_count, l_desc_tab);
 11    dbms_output.put_line('number of columns = ' || l_cols_count);
 12    dbms_output.new_line;
 13    dbms_output.put_line('DESCRIBE CURSOR');
 14    dbms_output.put_line('===========================');
 15    for i in 1..l_cols_count
 16    loop
 17      dbms_output.put_line(l_desc_tab(i).col_name);
 18    end loop;
 19    dbms_sql.close_cursor(l_cursor);
 20  exception
 21    when others then
 22      if dbms_sql.is_open(l_cursor) then
 23        dbms_sql.close_cursor(l_cursor);
 24      end if;
 25      raise;
 26  end desc_refcursor;
 27  /

Procedure created

The whole trick consists in using the new DMBS_SQL.TO_CURSOR_NUMBER function (see line 9). Maybe you wonder why this function was not used against the pi_cursor parameter and why that additional assignment at the line 8. That's because the TO_CURSOR_NUMBER function expects an input/output parameter and because pi_cursor is read only the compiler will complain.

Now, lets test the above procedure:

SQL> set serveroutput on
SQL> declare
  2    l_cursor sys_refcursor;
  3  begin
  4    open l_cursor for select * from user_tables;
  5    desc_refcursor(l_cursor);
  6  end;
  7  /

number of columns = 50

DESCRIBE CURSOR
===========================
TABLE_NAME
TABLESPACE_NAME
CLUSTER_NAME
IOT_NAME
STATUS
PCT_FREE
PCT_USED
INI_TRANS
MAX_TRANS
INITIAL_EXTENT
NEXT_EXTENT
MIN_EXTENTS
MAX_EXTENTS
PCT_INCREASE
FREELISTS
FREELIST_GROUPS
LOGGING
BACKED_UP
NUM_ROWS
BLOCKS
EMPTY_BLOCKS
AVG_SPACE
CHAIN_CNT
AVG_ROW_LEN
AVG_SPACE_FREELIST_BLOCKS
NUM_FREELIST_BLOCKS
DEGREE
INSTANCES
CACHE
TABLE_LOCK
SAMPLE_SIZE
LAST_ANALYZED
PARTITIONED
IOT_TYPE
TEMPORARY
SECONDARY
NESTED
BUFFER_POOL
ROW_MOVEMENT
GLOBAL_STATS
USER_STATS
DURATION
SKIP_CORRUPT
MONITORING
CLUSTER_OWNER
DEPENDENCIES
COMPRESSION
COMPRESS_FOR
DROPPED
READ_ONLY

PL/SQL procedure successfully completed

As you can see we were able to obtain the structure of the REF CURSOR and with a minimum effort. However, there are some pseudo-limitations when using this feature and the most important one is that, as soon as you convert the REF CURSOR into the DBMS_SQL cursor representation then you cannot switch back to REF CURSOR without explicit conversion. This means that a scenario in which you'd want to describe the REF CURSOR and after that you would like to continue with your REF CURSOR using FETCH, CLOSE etc. (REF CURSOR specific), those operations will simply not work. You'll have to do the whole further cursor handling using DBMS_SQL package. However, you can re-convert to the REF CURSOR using DBMS_SQL.TO_REFCURSOR. So, the DESCRIBE_REFCURSOR procedure can be modified like this:

SQL> create or replace procedure desc_refcursor(pio_cursor in out sys_refcursor) is
  2    l_cursor integer;
  3    l_cols_count integer;
  4    l_desc_tab dbms_sql.desc_tab;
  5  begin
  6    l_cursor := dbms_sql.to_cursor_number(pio_cursor);
  7    dbms_sql.describe_columns(l_cursor, l_cols_count, l_desc_tab);
  8    dbms_output.put_line('number of columns = ' || l_cols_count);
  9    dbms_output.new_line;
 10    dbms_output.put_line('DESCRIBE CURSOR');
 11    dbms_output.put_line('===========================');
 12    for i in 1..l_cols_count
 13    loop
 14      dbms_output.put_line(l_desc_tab(i).col_name);
 15    end loop;
 16    pio_cursor := dbms_sql.to_refcursor(l_cursor);
 17  end desc_refcursor;
 18  /

Now the invoking anonymous block can continue using ref cursor operations:

SQL> set serveroutput on
SQL> declare
  2    l_cursor sys_refcursor;
  3  begin
  4    open l_cursor for select * from cat;
  5    desc_refcursor(l_cursor);
  6    close l_cursor; -- REFCURSOR operation
  7  end;
  8  /

number of columns = 2

DESCRIBE CURSOR
===========================
TABLE_NAME
TABLE_TYPE

PL/SQL procedure successfully completed

Yes, that's it! Nice feature!