DBMS_SQL Describe columns from cursor [message #349651] |
Mon, 22 September 2008 09:01  |
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 #349672 is a reply to message #349667] |
Mon, 22 September 2008 10:45   |
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 #349682 is a reply to message #349651] |
Mon, 22 September 2008 11:32  |
 |
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
|
|
|