I have a procedure that return sys_refcursor as its out parameter. Below is the code :
CREATE OR REPLACE PROCEDURE my_procedure
(
o_result_set OUT SYS_REFCURSOR
) AS
BEGIN
OPEN o_result_set FOR
//some dynamic query;
END;This procedure is called from another procedure(my_procedure2). In my_procedure2 I want to read data that is returned by my_procedure in sys_refcursor. So for reading data I have to fetch this o_result_set in some variable or type. But the problem is columns are not known at design time of the stored procedure. We are getting name, datatype of column at run time.
Please suggest a way how can I fetch this sys_refcursor for reading.
Provided you're on 11g, you can convert ref cursors to dbms_sql cursors. Then you can use this to parse the query and find the columns:
create or replace procedure p(cols in int, cur out sys_refcursor) is
begin
if cols = 1 then
open cur for select 'a' c1 from dual;
else
open cur for select 'a' c1, 'b' c2 from dual;
end if;
end p;
/
create or replace procedure parse_cursor (cur in out sys_refcursor) as
curs int;
cols int;
d dbms_sql.desc_tab;
val varchar2(4000);
begin
curs := dbms_sql.to_cursor_number(cur);
dbms_sql.describe_columns(curs, cols, d);
for i in 1 .. cols loop
dbms_sql.define_column(curs, i, val, 4000);
end loop;
while dbms_sql.fetch_rows(curs) > 0 loop
for i in 1 .. cols loop
dbms_sql.column_value(curs, i, val);
dbms_output.put_line('Col ' || i || ': val: ' || val);
end loop;
end loop;
dbms_sql.close_cursor(curs);
end;
/
declare
c sys_refcursor;
begin
dbms_output.put_line('ONE COL');
p(1, c);
parse_cursor(c);
dbms_output.put_line('TWO COLS');
p(2, c);
parse_cursor(c);
end;
/
ONE COL
Col 1: val: a
TWO COLS
Col 1: val: a
Col 2: val: b
If you want, you check the data types and bind to the appropriate type when defining the columns (dbms_sql.define_column).