Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_SQL Describe columns from cursor (10g)
DBMS_SQL Describe columns from cursor [message #349651] Mon, 22 September 2008 09:01 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi,
We have procedures that returns a cursor like:


create procedure a (param1 date, cur_out out sys_refcursor) is
begin
...
end;



Now I'm trying to get the list of the column names returned by that cur_out variable. I've look into dbms_sql but it looks like it only accepts select statements within quotes to parse a cursor. Is there a way to get the column names of that cursor? Thanks.

Here's the usual:


declare
    c number;
    col_cnt number;
    rec_tab dbms_sql.desc_tab;
begin
    c := dbms_sql.open_cursor;
    
    --is it possible to call our procedure that returns the cursor here?
    dbms_sql.parse(c, 'select ''x'' a from dual', dbms_sql.native); 
    
    dbms_sql.describe_columns(c, col_cnt, rec_tab);
    
    dbms_output.put_line(rec_tab(1).col_name);
    
    dbms_sql.close_cursor(c);
end;
/

[Updated on: Mon, 22 September 2008 09:08]

Report message to a moderator

Re: DBMS_SQL Describe columns from cursor [message #349667 is a reply to message #349651] Mon, 22 September 2008 10:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at T.Kyte's print_table function.

Regards
Michel
Re: DBMS_SQL Describe columns from cursor [message #349672 is a reply to message #349667] Mon, 22 September 2008 10:45 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thanks Michel, but I think that procedure also uses the describe_columns procedure which just accepts a query inside quotes as similar to the one I've posted. What we have is number of procedures that returns a ref cursor, and we want to read on that cursor and get the list of column names. Please note if I miss something or on the link you gave. Thanks.



Found this, looks like it's only available on 11g.

[Updated on: Mon, 22 September 2008 10:50]

Report message to a moderator

Re: DBMS_SQL Describe columns from cursor [message #349676 is a reply to message #349672] Mon, 22 September 2008 11:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dbms_sql sql indeed accepts only dbms_sql cursor handle and not ref cursor before 11g.
I think there is no way before.

Regards
Michel

OK, I see you found it while I was writing my answer.

[Updated on: Mon, 22 September 2008 11:05]

Report message to a moderator

Re: DBMS_SQL Describe columns from cursor [message #349680 is a reply to message #349676] Mon, 22 September 2008 11:18 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thanks again Michel. I guess I'll just write a java stored proc for this. Thanks again.
Re: DBMS_SQL Describe columns from cursor [message #349682 is a reply to message #349651] Mon, 22 September 2008 11:32 Go to previous message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
Remember that dbms_sql parses a string, then a varchar2 data type. Probably there is no way to do it with PL/SQL probably before 11g as Michel said already.

But I know you could do it with a java stored procedure anyway. Data returned as sys_refcursor is acquired by java as the class (Data Type)ResultSet witch gives you the opportunity to call the method getMetaData() with all the information you're looking for.

Bye Alessandro
Previous Topic: Selecting specufuc column programmaticaly
Next Topic: employee salary in words
Goto Forum:
  


Current Time: Sun Dec 04 10:43:37 CST 2016

Total time taken to generate the page: 0.23062 seconds