Determine structure of out cursor I have a function in Oracle that neither I nor my team wrote and is heavily obfuscated. However, it has a cursor out parameter. How can I find out the number and types that the cursor contains?

For an example of what I need, consider this cursor:

DECLARE
  TEMP1 VARCHAR2(500);
  TEMP2 NUMBER;
  TEMP3 RAW(16);
  TEMP SYS_REFCURSOR;
  PROCEDURE MAKE_TEMP(TEMP_RETURN OUT SYS_REFCURSOR) IS
  BEGIN
    OPEN TEMP_RETURN FOR SELECT DUMMY, 5, HEXTORAW('111') FROM DUAL;
  END;
BEGIN
  MAKE_TEMP(TEMP);
  LOOP
    FETCH TEMP INTO TEMP1, TEMP2, TEMP3;
    EXIT WHEN (TEMP%NOTFOUND);
    DBMS_OUTPUT.PUT_LINE('1: '||TEMP1);
    DBMS_OUTPUT.PUT_LINE('2: '||TO_CHAR(TEMP2));
    DBMS_OUTPUT.PUT_LINE('3: '||RAWTOHEX(TEMP3));
  END LOOP;
  CLOSE TEMP;
END;
/


If MAKE_TEMP were the function I'm trying to use, I would need to know that it has 3 values per row, and that the values are a VARCHAR2, a NUMBER, and a RAW in that order so I can define variables to hold the results.

I'm using Oracle 11.2.0.1.0, although I would appreciate if answers for newer versions exist as well.

Why I can't just look at the source

If you must know, the function is part or Oracle ASP.NET Membership Provider and is written by Oracle. I'm trying to debug a problem and would like to see what this function is returning. The function's source code starts like this:

create or replace 
FUNCTION  "ORA_ASPNET_MEM_GETALLUSERS" wrapped
0
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
3
8
9200000
1
4
0
33


As you can see, it's (at best) going to be extremely difficult to extract any information about the cursor of that.
This example code shows how to parse a ref_cursor. There are additional columns available, see the definition of SYS.DBMS_SQL.desc_tab2 for a full list of available columns.

In this example, my ref_cursor is defined as select * from user_tables. You would of course substitute in your own ref_cursor.

DECLARE
   l_csrid       INTEGER;
   l_coldesc     SYS.DBMS_SQL.desc_tab2;
   l_colcnt      INTEGER;
   l_refcursor   SYS_REFCURSOR;

   FUNCTION tostring (p_label   IN VARCHAR2
                     , p_value   IN VARCHAR2)
      RETURN VARCHAR2
   AS
   BEGIN
      RETURN RPAD (p_label, 30)
             || ': '
             || p_value
             || UTL_TCP.crlf;
   END tostring;

   PROCEDURE myput_line (p_rec      IN DBMS_SQL.desc_rec2
                       , p_colnum   IN INTEGER)
   AS
   BEGIN
      DBMS_OUTPUT.put_line (   RPAD ('_', 30, '_') 
                        || UTL_TCP.crlf
                        || tostring (p_label =&gt; 'Column'
                                   , p_value =&gt; p_colnum)
                        || tostring (p_label =&gt; 'Name'
                                   , p_value =&gt; p_rec.col_name)
                        || tostring (p_label =&gt; 'Type'
                                   , p_value =&gt; p_rec.col_type)
                        || tostring (p_label =&gt; 'Null OK'
                                   , p_value =&gt; CASE WHEN p_rec.col_null_ok
                                                     THEN 'Yes' 
                                                     ELSE 'No' 
                                                END));
   END myput_line;
 BEGIN
   OPEN l_refcursor FOR
      SELECT * FROM user_tables;

   l_csrid   := DBMS_SQL.to_cursor_number (rc =&gt; l_refcursor);
   DBMS_SQL.describe_columns2 (c       =&gt; l_csrid
                             , col_cnt =&gt; l_colcnt
                             , desc_t  =&gt; l_coldesc);

   FOR i IN 1 .. l_colcnt LOOP
      myput_line (l_coldesc (i), i);
   END LOOP;
END;