Oracle 11G: describing a refcursor

Anton Scheffer
0 0
Read Time:58 Second

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

About Post Author

Anton Scheffer

Oracle Consultant at AMIS
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %
Next Post

JSF EL Calculator bean - to overcome coercion errors and add functionality to EL Expressions

In a recent project I was working on JSF page that needed to display the result of a calculation performed on values taken from various bean properties. The beans were somewhat beyond my control, the properties were all of a numeric type so I tried an EL expression like: <af:outputText […]
%d bloggers like this: