plsql - Column names in an empty Oracle REF CURSOR -

in pl/sql, can use trick 1 here find out name/value pairs every column in row ref cursor:

to_char of oracle pl/sql table type

that's awesome trick. doesn't work when ref cursor empty, such 1 here (that's example. real cursor doesn't select dual):

open cursor select 1 dual 1 = 0; 

does empty ref cursor have column name/type information?

yes, i've tried solution without rows, , you're right. limited point of view, think here need two different methods retrieve columns' names , values.

1) dbms_sql package retrieve columns' names.

2) tbone method retrieve data.

---

procedure

create or replace procedure demo(sqltext in varchar2)     refcur sys_refcursor;     curid  integer;     cnt    number;     ret    dbms_sql.desc_tab;     rectab dbms_sql.desc_tab;     format_string constant pls_integer := 20;      procedure printdesctab(desctab in sys.dbms_sql.desc_tab)     begin         -- want columns         in 1 .. desctab.count         loop             dbms_output.put(lpad(desctab(i).col_name, format_string));         end loop;         dbms_output.new_line;     end printdesctab;      procedure printcur(cv in sys_refcursor)     begin         c in ( --select t2.column_value.getrootelement() name,                   select extractvalue(t2.column_value, 'node()') value                     table(xmlsequence(cv)) t                          ,table(xmlsequence(extract(column_value, '/row/node()'))) t2)         loop             dbms_output.put(lpad(c.value, format_string));         end loop;         dbms_output.new_line;         dbms_output.new_line;     end;  begin     dbms_output.put_line('dynamic sql: ' || sqltext);     curid := dbms_sql.open_cursor();     --  checks sql injection do...     dbms_sql.parse(curid, sqltext, dbms_sql.native);     dbms_sql.describe_columns(curid, cnt, rectab);     printdesctab(rectab);     dbms_sql.close_cursor(curid);      open refcur sqltext;     printcur(refcur);     close refcur; exception     when others         if dbms_sql.is_open(curid)            dbms_sql.close_cursor(curid);         end if;         if refcur%isopen             close refcur;         end if;         dbms_output.put_line(sqlcode || ' - ' || sqlerrm); end demo; 

test

declare     sqltext varchar2(2000); begin     sqltext := 'select 1 one, 2 two   dual 1=0';     demo(sqltext);     sqltext   := 'select name, type || chr(13) type' -- chr(13) specific ascii carriage return                 ||'   user_plsql_object_settings'                 ||' name not ''%$%'' ,  rownum <= 10';           demo(sqltext);        sqltext := 'select 1 one, 2 two   dual ';                       demo(sqltext);  exception     when others         dbms_output.put_line(sqlcode || ' - ' || sqlerrm); end; 

result

dynamic sql: select 1 one, 2 two   dual 1=0                  1                 2   dynamic sql: select name, type || chr(13) type   user_plsql_object_settings name not '%$%' ,  rownum <= 10                 name                type      add_job_history          procedure     aft_ins_test_trg            trigger     bef_del_test_trg            trigger     bef_ins_test_trg            trigger             betwnstr           function                 bool           function     cached_fibonacci           function                debug            package                debug       package body           debug_test          procedure   dynamic sql: select 1 one, 2 two   dual                   1                 2                    1                   2