In Oracle 11G the supplied package DBMS_SQL is extended with two new procedures:

  • to_cursor_number, which transfers a refcursor to a "dbms_sql cursor"
  • to_refcursor, which transfers a "dbms_sql cursor" to a refcursor

These can be used, together with dbms_sql.describe_columns to describe a refcursor.

SYS@LAB> declare
  2    rc sys_refcursor;
  3    v varchar2(10);
  4    n number;
  5    c integer;
  6    cnt integer;
  7    dt dbms_sql.desc_tab3;
  8  begin
  9    open rc for 'select dummy, cast( 4 as number(3,1)) from dual';
 10    c := dbms_sql.to_cursor_number( rc );
 11    dbms_sql.describe_columns3( c, cnt, dt );
 12    dbms_output.put_line( 'no. columns = ' || cnt );
 13    for i in 1 .. cnt
 14    loop
 15      dbms_output.put_line( dt(i).col_type );
 16      dbms_output.put_line( dt(i).col_name );
 17      dbms_output.put_line( dt(i).col_max_len );
 18      dbms_output.put_line( dt(i).col_precision );
 19      dbms_output.put_line( dt(i).col_scale );
 20    end loop;
 21    rc := dbms_sql.to_refcursor( c );
 22    fetch rc into v, n;
 23    close rc;
 24  end;
 25  /
no. columns = 2
1
DUMMY
1
0
0
2
CAST(4ASNUMBER(3,1))
22
3
1
PL/SQL procedure successfully completed.

Anton