Oracle 11G: describing a refcursor

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.

declare
  rc sys_refcursor;
  v varchar2(10);
  n number;
  c integer;
  cnt integer;
  dt dbms_sql.desc_tab3;
begin
  open rc for 'select dummy, cast( 4 as number(3,1)) from dual';
  c := dbms_sql.to_cursor_number( rc );
  dbms_sql.describe_columns3( c, cnt, dt );
  dbms_output.put_line( 'no. columns = ' || cnt );
  for i in 1 .. cnt
  loop
    dbms_output.put_line( dt(i).col_type );
    dbms_output.put_line( dt(i).col_name );
    dbms_output.put_line( dt(i).col_max_len );
    dbms_output.put_line( dt(i).col_precision );
    dbms_output.put_line( dt(i).col_scale );
  end loop;
  rc := dbms_sql.to_refcursor( c );
  fetch rc into v, n;
  close rc;
end;
/
no. columns = 2
1
DUMMY
1
0
0
2
CAST(4ASNUMBER(3,1))
22
3
1

PL/SQL procedure successfully completed.

Anton