SQL & PL/SQL

Hi,

I am on Oracle 10g. To start with I cannot use Java, Dot Net or any other framework,language other than PL/SQL to read a weakly typed ref cursor being returned from a PL/SQL procedure.

I have a number of packages with procedures that return a weakly typed ref cursor.

The procedures are of the following format:

PROCEDURE test( i_acct_num IN NUMBER,
                       o_ref_cur OUT SYS_REFCURSOR )
AS
BEGIN

OPEN o_ref_cur
FOR
  SELECT col1,
             col2,
             col3,
             col4,
             col5,...col(n) 
  FROM table1 left outer join 
              table2 on ( <condition > )
                      left outer join 
                                table3 on ( <conditon> ) 
                                               WHERE < condition >;
END test;

Now debugging these procedures are a lot tougher since I need to know the exact number of columns being returned and the column data types from the query.

I need to go through each and every table in the query to find out the datatypes of the columns and finally the number of columns in the query itself!. Some queries return more than 30 columns, so the task is more tougher!

I end up a lot with this error

ORA-06504: PL/SQL: Return types of Result Set variables or query do not match 

I want to know if there is absolutely any way I can find out what the ref cursor is returning from PL/SQL itself without having to go through each and every query to determine the number of columns and it's datatype? .

package DBMS_SQL has not been used anywhere at all.

I cannot use SQL* Developer as well. I have TOAD.

Is there any way I can find from v$shared_cursors or any other Oracle SYS views( v$<name> ) about the ref cursor?. After all the value of the ref cursor does contain the memory address of the result set on the database server. Anyways to read that memory address in ref cursor?.

Cannot upgrade to Oracle 11g at the moment so cannot use "dbms_sql.to_cursor_number" function.

Thanks