Get column names from procedure result

Question

I am not experienced with db but I am trying to get the column names from the result of a stored procedure.
The code must be generic because the stored procedure is not known. The first step is to make it work for procedures with no input parametes (only the io_cursor)

My code until now:

procedure fakeProc (
       io_cursor        in out t_ref_cursor
      )

And the code that I am using:

PROCEDURE get_SQL_Fields (
       out_result    out varchar2)
as

/**/

v_cur         NUMBER        := NULL;
v_count       NUMBER        := NULL;

v_tab_desc    DBMS_SQL.DESC_TAB;
sqlstr        VARCHAR2(100);
BEGIN

v_cur := DBMS_SQL.OPEN_CURSOR;
    --Here i get errors
    sqlstr :='begin '|| fakeproc()||';end;';

    DBMS_SQL.PARSE(v_cur, sqlstr, DBMS_SQL.NATIVE);
    DBMS_SQL.DESCRIBE_COLUMNS(v_cur, v_count, v_tab_desc);

    FOR i IN 1..v_count LOOP
      out_result := out_result||v_tab_desc(i).COL_NAME||',';
    END LOOP;

  end if;
END get_SQL_Fields;

So my problem for now is to build this sqlstr ;
THe error that I get is :Error: PLS-00306: wrong number or types of arguments in call to 'FAKEPROC' Line: 654 Text: sqlstr :='begin '|| fakeproc()||';end;';

Error: PL/SQL: Statement ignored Line: 654 Text: sqlstr :='begin '|| fakeproc()||';end;';

well, even if that's probably not the only problem, `fakeProc` needs a `t_ref_cursor parameter`, and you call it without parameter... — Raphaël Althaus, May 23 '13 at 15:19
I know that. I tried to put a t_ref_cursor inside the parameter but the error is bad binding. — Nianios, May 23 '13 at 15:41
possible [duplicate](http://stackoverflow.com/questions/6265160/to-char-of-an-oracle-pl-sql-table-type/6268606#6268606) — tbone, May 23 '13 at 16:01
Maybe is a duplicate, but I don't get it. How can I use :open v_cur for select * from table(cast(v_tab as tab_type)); ? — Nianios, May 24 '13 at 07:09
You might try using DBA_ARGUMENTS to find the names and types of the procedure arguments before you try building your call. Share and enjoy. — Bob Jarvis - Слава Україні, May 24 '13 at 16:59
Also - it looks to me like the line while builds `sqlstr` should read `sqlstr :='begin fakeproc(); end;';` although this will still have problems because the arguments are missing, but see my comment above. Share and enjoy. — Bob Jarvis - Слава Україні, May 24 '13 at 17:06
@BobJarvis Thanks for your comments. I 'll try it during the weekend and let you know — Nianios, May 24 '13 at 17:32

score 3 · Answer 1 · answered May 28 '13 at 18:35

First create a procedure to show the column names and values from the ref cursor:

CREATE OR REPLACE procedure printCur(in_cursor IN sys_refcursor) IS
begin

    FOR c IN (SELECT ROWNUM rn,
                    t2.COLUMN_VALUE.getrootelement () NAME,
                    EXTRACTVALUE (t2.COLUMN_VALUE, 'node()') VALUE
               FROM TABLE (XMLSEQUENCE (in_cursor)) t,
                    TABLE (XMLSEQUENCE (EXTRACT (COLUMN_VALUE, '/ROW/node()'))) t2
               order by 1)

   LOOP
      DBMS_OUTPUT.put_line (c.NAME || ': ' || c.VALUE);
   END LOOP;

exception
    when others then raise;
end;
/

Now use it like this:

declare

v_cur   sys_refcursor;

begin
    open v_cur for select 'ABC' as vchar_col1, sysdate as date_col2 from dual;

    printcur(v_cur);
exception
    when others then raise;

end;

Output:

VCHAR_COL1: ABC
DATE_COL2: 28-MAY-2013

Thanks for your reply. I have chosen answer from jonearles because it suits better my needs and my poor knowledge. Thanks for your time — Nianios, May 29 '13 at 07:03

score 2 · Accepted Answer · answered May 29 '13 at 06:05

You're close, you're just missing DBMS_SQL.TO_CURSOR_NUMBER to convert the cursor to a cursor number, and a dynamic PL/SQL bind variable.

create or replace procedure fakeProc (io_cursor in out sys_refcursor) is
begin
    open io_cursor for 'select 1 column1, ''asdf'' column2 from dual';
end;
/

create or replace PROCEDURE get_SQL_Fields (
       out_result    out varchar2)
as
    v_cur         NUMBER        := NULL;
    v_count       NUMBER        := NULL;
    v_tab_desc    DBMS_SQL.DESC_TAB;
    v_cursor      SYS_REFCURSOR;
BEGIN
    execute immediate 'begin fakeProc(:v_cursor); end;' using in out v_cursor;
    v_cur := dbms_sql.to_cursor_number(v_cursor);
    DBMS_SQL.DESCRIBE_COLUMNS(v_cur, v_count, v_tab_desc);
    FOR i IN 1..v_count LOOP
        out_result := out_result||case when i = 1 then null else ',' end
            ||v_tab_desc(i).COL_NAME;
    END LOOP;
END get_SQL_Fields;
/

declare
    v_output varchar2(32767);
begin
    get_sql_fields(v_output);
    dbms_output.put_line(v_output);
end;
/

COLUMN1,COLUMN2

This makes the assumption that the procedure will have only one parameter. Does the answer need to work for any possible combination of arguments?

Thank you. That is what I need.I can handle more arguments – Nianios May 29 '13 at 07:00 — Nianios, May 29 '13 at 07:00

Get column names from procedure result

2 Answers2