Oracle 11G: describing a refcursor

0 0

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

anton.scheffer@amis.nl

Happy

0 0 %

Sad

0 0 %

Excited

0 0 %

Sleepy

0 0 %

Angry

0 0 %

Surprise

0 0 %

Related posts:

Oracle Database Cross Session Data Cache – Introducing the Globally Accessible Database Context Oracle SQL and PL/SQL: juggling with user defined types (ADT) and XMLType – for creating an ADT based XML Service API Parsing a Microsoft Word docx, and unzip zipfiles, with PL/SQL AS_PDF, generating a PDF-document with some plsql Java is niet eng! introductie van Java voor PL/SQL ontwikkelaars – donderdag 5 april