Get column names from procedure result

Question

I have no experience with db, but I'm trying to get the column names from the result of the stored procedure.
The code must be shared because the stored procedure is unknown. The first step is to make it work for procedures without input parameters (io_cursor only)

My code so far:

procedure fakeProc ( io_cursor in out t_ref_cursor ) 

And the code that I use:

 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 is to build this sqlstr; The error I get is: Error: PLS-00306: The wrong number or types of arguments when called on FAKEPROC. Line: 654 Text: sqlstr: = 'begin' || fakeproc () || '; end;';

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

Answer 1

You are close, you simply do not have enough DBMS_SQL.TO_CURSOR_NUMBER to convert the cursor to a cursor number and a dynamic PL / SQL bind.

 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 suggests that the procedure will have only one parameter. Is an answer required for any possible combination of arguments?

Answer 2

First, create a procedure to display 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