i have this query that i build into a reference cursor. the problem that i see was that how do i call the reference cursor with unknown multiple columns from a pl/sql. thank you.
this the sample query
select *
from (select '102034' mid, '2015' fyr, 51885334.33 pmt from dual union all
select '130000' mid, '2015' fyr, 8815676.42 pmt from dual union all
select '130386' mid, '2015' fyr, 2718094.18 pmt from dual union all
select '102034' mid, '2016' fyr, 95746494.85 pmt from dual union all
select '130000' mid, '2016' fyr, 4746454.75 pmt from dual union all
select '130386' mid, '2016' fyr, 2717724.76 pmt from dual union all
select '102034' mid, '2017' fyr, 22619702.28 pmt from dual union all
select '130000' mid, '2016' fyr, 5659881.46 pmt from dual) cpy
pivot (sum(nvl(cpy.pmt,0)) for fyr in (2010,2011,2012,2013,2014,2015,2016,2017))
MID 2010 2011 2012 2013 2014 2015 2016 2017
------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
102034 51885334.3 95746494.8 22619702.2
130000 8815676.42 10406336.2
130386 2718094.18 2717724.76
this is the reference cursor:
create or replace function wt_get_cpay (pStartYear varchar2,
pEndYear varchar2) return sys_refcursor as
refCurPay SYS_REFCURSOR;
vSQL VARCHAR2(32767);
BEGIN
vSQL := 'select *
from (select ''102034'' mid, ''2015'' fyr, 51885334.33 pmt from dual union all
select ''130000'' mid, ''2015'' fyr, 8815676.42 pmt from dual union all
select ''130386'' mid, ''2015'' fyr, 2718094.18 pmt from dual union all
select ''102034'' mid, ''2016'' fyr, 95746494.85 pmt from dual union all
select ''130000'' mid, ''2016'' fyr, 4746454.75 pmt from dual union all
select ''130386'' mid, ''2016'' fyr, 2717724.76 pmt from dual union all
select ''102034'' mid, ''2017'' fyr, 22619702.28 pmt from dual union all
select ''130000'' mid, ''2016'' fyr, 5659881.46 pmt from dual) cpy
pivot (sum(nvl(cpy.pmt,0)) for fyr in (';
for i in (select level, (pEndYear + 1) - level fy from dual connect by level <= (pEndYear - pStartYear) + 1) loop
vSQL := vSQL || '''' || i.fy || ''',';
end loop;
vSQL := RTRIM(vSQL,',') || ')) ORDER BY mid';
open refCurPay for vSQL;
end;
note: that the actual query will not have the hardcoded values