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
