Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Marat.

Asked: November 11, 2009 - 7:14 pm UTC

Last updated: November 12, 2009 - 10:35 am UTC

Version: 10.2.0.4

Viewed 10K+ times! This question is

You Asked

Dear Tom,

how can I describe columns of a REF CURSOR? (analog of dbms_sql.DESCRIBE_COLUMNS).



Thanks

and Tom said...

you cannot, not until 11g Release 1 anyway, in plsql.

You would have to use a client external from the database. Any external client like java/jdbc, C, C++, visual basic, etc - would do this the way they describe any cursor.

In 11g Release 1 and above, you have dbms_sql.


ops$tkyte%ORA11GR1> declare
  2          l_rcursor sys_refcursor;
  3          l_colCnt number;
  4          l_descTbl dbms_sql.desc_tab;
  5  begin
  6          open l_rcursor for select * from all_users;
  7
  8          dbms_sql.describe_columns
  9          ( c       => dbms_sql.to_cursor_number(l_rcursor),
 10            col_cnt => l_colCnt,
 11            desc_t  => l_descTbl );
 12
 13          for i in 1 .. l_colCnt
 14          loop
 15                  dbms_output.put_line( l_descTbl(i).col_name );
 16          end loop;
 17  end;
 18  /
USERNAME
USER_ID
CREATED

PL/SQL procedure successfully completed.



but not before.

Rating

  (1 rating)

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

Comments

A workaround (kind of...)

Morten Braten, November 12, 2009 - 11:16 am UTC

It's not exactly a "describe" as such, but using DBMS_XMLGEN you can take a ref cursor and generate XML from it. You could then inspect the XML and get the column names (and try to parse a few of the values in the first rows to guess at the data types).

See http://tkyte.blogspot.com/2006/01/i-like-online-communities.html for a code example.


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