Ask Tom "Describe REF CURSOR columns":

Dear Tom,

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

Thanks

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

But then in 11g R1, magic happens.

SET SERVEROUTPUT ON; -- Tom forgets to remind us about this
declare
        l_rcursor sys_refcursor;
        l_colCnt number;
        l_descTbl dbms_sql.desc_tab;
begin
        open l_rcursor for select * from all_users;

        dbms_sql.describe_columns
        ( c       => dbms_sql.to_cursor_number(l_rcursor),
          col_cnt => l_colCnt,
          desc_t  => l_descTbl );

        for i in 1 .. l_colCnt
        loop
                dbms_output.put_line( l_descTbl(i).col_name );
        end loop;
end;
/

I'm trying to figure out what columns were returned by a stored procedure call, and it's been a bear so far trying to remember how to use sprocs in SQL Deveoloper and to change the default setup in the test harness to SYS_REFCURSOR -- and figure out how to play with each row in the cursor (that would be ROW_TEMP CUR_RESULT%ROWTYPE;) and...

Labels: