Skip to Main Content
  • Questions
  • Dynamically Discover Ref Cursor Attributes Like Column Name, Column Value

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Michael.

Asked: January 08, 2018 - 9:24 pm UTC

Last updated: January 09, 2018 - 11:30 am UTC

Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Viewed 1000+ times

You Asked

Hi,

Is it possible to dynamically discover Ref Cursor attributes like, for example, Column Name?

The attached LiveSQL Link contains many details about my question. It is my first time to use LiveSQL Link. If it does not work, please let me know.

I have been dynamically discovering DBMS_SQL cursor attributes using the DBMS_SQL.DESC_TAB and DBMS_SQL.DESC_REC collections. I understand that DBMS_SQL cannot process Ref Cursors. I am hoping there is some way to dynamically discover Ref Cursor column names.

Thank you for your help!!

- Mike Kemp

with LiveSQL Test Case:

and Chris said...

You can't bind a ref cursor using dbms_sql. But what you can do is convert a ref cursor to a dbms_sql cursor. Then you can describe the columns in that!

So call your procedure as normal. Then convert the returned cursor to a dbms_sql one:

declare
  o1        sys_refcursor;
  l1        varchar2( 1 ) := 'Y';
  l2        varchar2( 1 );
  l3        varchar2( 1 );
  
  procedure desc_cursor ( cur sys_refcursor ) as
    col_cnt pls_integer;
    rec_tab   dbms_sql.desc_tab;
    rec_rec   dbms_sql.desc_rec;
    handle pls_integer;
  begin
    handle := dbms_sql.to_cursor_number(o1);
    dbms_sql.describe_columns( handle, col_cnt, rec_tab );
    << loop1 >> 
    for i in 1..rec_tab.last loop
      rec_rec   := rec_tab( i );
      dbms_output.put_line( rec_rec.col_name );
    end loop loop1;
    
    dbms_sql.close_cursor(handle);
  end desc_cursor;
  
begin
  dbms_output.put_line( '########### 1st call ###########' );
  
  p1(o1, l1, l2, l3);
  
  desc_cursor (o1);
  
  dbms_output.put_line( '########### 2nd call ###########' );

  l1 := null;
  l3 := 'Y';
  
  p1(o1, l1, l2, l3);
  desc_cursor (o1);
  
end;
/

########### 1st call ###########
F1
F2
########### 2nd call ###########
F1
F2
F3


PS - the LiveSQL example works great. Though you don't need spend so much time to proving basic functionality ;)

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

AWESOME - THANK YOU!!!

Michael Kemp, January 09, 2018 - 2:53 pm UTC

Excellent!! Thank you!!

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library