How to read value from sys_refcursor variable

How to read value from sys_refcursor variable?

Developer often write functions which return type is sys_refcursor.

To directly read the value of the returned function we can use the below method:-

create or replace function TESTCALENDERDAYS return sys_refcursor is
rc sys_refcursor;
begin
open rc for select distinct ADD_MONTHS(SYSDATE, -365) as "day",
            COOL_id, COOL_name,MATION_id
            from QA.PQRS_STATS_VIEW
            union all
            select distinct ADD_MONTHS(SYSDATE, -365)+1 as "day",
            COOL_id, COOL_name,MATION_id
            from QA.PQRS_STATS_VIEW where ADD_MONTHS(SYSDATE, -365)+1 < sysdate;
return rc;
end;


create or replace function TESTCALENDERDAYS return sys_refcursor is
rc sys_refcursor;
begin
open rc for select distinct to_char(ADD_MONTHS(SYSDATE, -365)) as "day",COOL_id, COOL_name,MATION_id from TESTCALENDERDAYS_MV2
            union all
            select distinct to_char(ADD_MONTHS(SYSDATE, -365)+1) as "day",COOL_id, COOL_name,MATION_id
            from TESTCALENDERDAYS_MV2 where ADD_MONTHS(SYSDATE, -365)+1 < sysdate;
return rc;
end;

select to_date(day),COOL_ID,COOL_NAME,MATION_ID from (select * from xmltable('/ROWSET/ROW'
PASSING xmltype(TESTCALENDERDAYS())
columns
DAY date  PATH 'DAY',
COOL_ID PATH 'COOL_ID',
COOL_NAME PATH 'COOL_NAME',
MATION_ID PATH 'MATION_ID'
));

select * from  TESTCALENDERDAYS_MV2 from dual





select TESTF() from dual
select TESTCALENDERDAYS() from dual

select * from (select * from xmltable('/ROWSET/ROW'
PASSING xmltype(TESTF())
columns
ID  PATH 'ID',
COMPANY PATH 'COMPANY'
));

select * from TESTCALENDERDAYS_MV2

CREATE TABLE "SWARNASHIS"."TESTCALENDERDAYS_MV2" 
   ( "day" DATE, 
"COOL_ID" NUMBER(38,0), 
"COOL_NAME" NVARCHAR2(512), 
"MATION_ID" NUMBER(38,0)
   )
   tablespace users

insert into TESTCALENDERDAYS_MV2 values(sysdate-1,11,'ashisswarn',12);
insert into TESTCALENDERDAYS_MV2 values(sysdate-2,12,'ashisswarn',13);
insert into TESTCALENDERDAYS_MV2 values(sysdate-3,13,'ashisswarn',14);
insert into TESTCALENDERDAYS_MV2 values(sysdate-4,14,'ashisswarn',15);
insert into TESTCALENDERDAYS_MV2 values(sysdate-5,15,'ashisswarn',16);
insert into TESTCALENDERDAYS_MV2 values(sysdate-6,16,'ashisswarn',17);
insert into TESTCALENDERDAYS_MV2 values(sysdate-7,17,'ashisswarn',18);
insert into TESTCALENDERDAYS_MV2 values(sysdate-8,18,'ashisswarn',19);
insert into TESTCALENDERDAYS_MV2 values(sysdate-9,19,'ashisswarn',20);
insert into TESTCALENDERDAYS_MV2 values(sysdate-1,11,'ashisswarn',12);
insert into TESTCALENDERDAYS_MV2 values(sysdate-1,11,'ashisswarn',12);
insert into TESTCALENDERDAYS_MV2 values(sysdate-1,11,'ashisswarn',12);
insert into TESTCALENDERDAYS_MV2 values(sysdate-1,11,'ashisswarn',12);
insert into TESTCALENDERDAYS_MV2 values(sysdate-1,11,'ashisswarn',12);

commit


This works for fetching:

select extractvalue(column_value,'/ROW/day') day,
extractvalue(column_value,'/ROW/COOL_ID') COOL_id,
extractvalue(column_value,'/ROW/COOL_NAME') COOL_name,
extractvalue(column_value,'/ROW/MATION_ID') MATION_id
--     , extractvalue(column_value,'/ROW/LAST_NAME') last_name
from table(xmlsequence(TESTCALENDERDAYS()))

====

select day,COOL_id from (select extractvalue(column_value,'/ROW/day') day,
extractvalue(column_value,'/ROW/COOL_ID') COOL_id,
extractvalue(column_value,'/ROW/COOL_NAME') COOL_name,
extractvalue(column_value,'/ROW/MATION_ID') MATION_id
--     , extractvalue(column_value,'/ROW/LAST_NAME') last_name
from table(xmlsequence(TESTCALENDERDAYS()))) where COOL_id