I've got a PL/SQL package that returns a sys_refcursor based on the id that you pass it. I'd like to iterate through some ids and create a new ref cursor with one column from the original result set repeated for each id. (Sort of a cross tab.) A very simplified version of the PL/SQL block looks like:
create or replace package body dashboard_package is
procedure visits(RC in out sys_refcursor, IdNumber varchar2) as
BEGIN
OPEN RC FOR
select *
from (
select cat, cat_order, subcat, label_text
, trim(to_char(sum(v.current_month),'9,999,999,999')) current_month
, trim(to_char(sum(v.ly_month),'9,999,999,999')) ly_month
, trim(to_char(sum(v.ytd_tot),'9,999,999,999')) ytd_tot
, trim(to_char(sum(v.lytd_tot),'9,999,999,999')) lytd_tot
, trim(to_char(sum(v.ly_tot),'9,999,999,999')) ly_tot
from dashboard v
where v.id_number = IdNumber
group by cat_order, subcat, cat, label_text
union all
...
) a
order by cat_order, subcat;
END;
END;
I think if I had something like this
create or replace procedure test_refcur is
refCursorValue SYS_REFCURSOR;
begin
dashboard_package.visits(refCursorValue,12345);
for cursrow in refCursorValue loop
dbms_output.put_line(cursrow.ytd_tot);
end loop;
end test_refcur;
working, I could take it from there... any thoughts? Or perhaps clarification on the question that I should be asking.
If you're coming in with a number of IDs, then first prize would be to run only one SQL query to fetch the lot in one go, using a bulk in-bind for the IDs. This would probably require a modification to dashboard_package.visits
, or writing a new version of the visits
procedure to accept a PL/SQL table of IDs instead of a single ID.
If your hands are tied WRT modifying dashboard_package, then you could write a pipelined function to return the rows for a set of IDs:
-- create some helper types for the pipelined function
create type visitobj as object
(id number
,cat dashboard.cat%type
,cat_order dashboard.cat_order%type
,subcat dashboard.subcat%type
,label_text dashboard.label_text%type
,current_month varchar2(13)
,ly_month varchar2(13)
,ytd_tot varchar2(13)
,lytd_tot varchar2(13)
,ly_tot varchar2(13));
create type visittable as table of visitobj;
create or replace function test_refcur
return visittable deterministic pipelined is
refCursorValue SYS_REFCURSOR;
cat dashboard.cat%type;
cat_order dashboard.cat_order%type;
subcat dashboard.subcat%type;
label_text dashboard.label_text%type;
current_month varchar2(13);
ly_month varchar2(13);
ytd_tot varchar2(13);
lytd_tot varchar2(13);
ly_tot varchar2(13);
begin
for id in (/*iterate through the IDs*/) loop
dashboard_package.visits(refCursorValue, id);
loop
fetch refCursorValue into cat, cat_order, subcat, label_text,
current_month, ly_month, ytd_tot,
lytd_tot, ly_tot;
exit when refCursorValue%NOTFOUND;
pipe row (visitobj (id, cat, cat_order, subcat, label_text,
current_month, ly_month, ytd_tot,
lytd_tot, ly_tot));
end loop;
end loop;
return;
end test_refcur;
-- now you can simply do this:
SELECT * FROM TABLE(test_refcur);
(Of course, "/*iterate through the IDs*/
" would be whatever method you want to use to gather the IDs for which the function should be called - e.g. could be a PL/SQL table of IDs, or perhaps another query).
Again I'd stress that "first prize" is to not do any of this extra work at all - just have a dashboard_package.visits
that does it all in one SQL.
On a side note, trim(to_char(sum(v.ly_tot),'9,999,999,999'))
can be simplified to to_char(sum(v.ly_tot),'FM9,999,999,999')
. Also, if you use the format 'FM9G999G999G999'
instead, it will be non-locale-specific.