How do I combine result sets in Oracle?

Question

I have a procedure that has an in parameter and an out cursor. The results given by that cursor look like:

0100 | 0
0130 | 1
0200 | 2
0230 | 0
...

The first column is a static time code. The second column is an aggregation of how many times something is scheduled in that time slot on a given day.

That procedure is:

PROCEDURE DAILYLOAD (datep IN DATE, results OUT SYS_REFCURSOR)
   AS
   BEGIN
   Open results for
      SELECT RUN_TIME_C, COUNT (SCH_RPT_I)
    FROM    ITS_SCH_RPT_RUN_TIME
         LEFT OUTER JOIN
            ITS_SCH_RPT
         ON (   RUN_TIME_C = RUN_TIME1_C
             OR RUN_TIME_C = RUN_TIME2_C
             OR RUN_TIME_C = RUN_TIME3_C)
   WHERE EXP_DATE_D IS NULL
         OR datep < exp_date_d AND datep > start_date_d AND SUSPENDED_USER='N'
            AND (   ( (TO_CHAR (datep, 'D') = 1) AND RUN_SUNDAY_C = 'Y')
                 OR ( (TO_CHAR (datep, 'D') = 2) AND RUN_MONDAY_C = 'Y')
                 OR ( (TO_CHAR (datep, 'D') = 3) AND RUN_TUESDAY_C = 'Y')
                 OR ( (TO_CHAR (datep, 'D') = 4) AND RUN_WEDNESDAY_C = 'Y')
                 OR ( (TO_CHAR (datep, 'D') = 5) AND RUN_THURSDAY_C = 'Y')
                 OR ( (TO_CHAR (datep, 'D') = 6) AND RUN_FRIDAY_C = 'Y')
                 OR ( (TO_CHAR (datep, 'D') = 7) AND RUN_SATURDAY_C = 'Y'))
GROUP BY RUN_TIME_C
ORDER BY RUN_TIME_C;
   END DAILYLOAD;

I want to call this procedure from a wrapping procedure several times with different parameters so that I can come up with weekly load and monthly load. Conceptually, this would be done by concatenating the individual result sets through something like union all and grouping that by the first column summing the second column for each grouping.

Right now, I have something like

Dailyload(datep, results1);
Dailyload(datep + 1, results2);
...

OPEN results FOR
  SELECT run_time_c, 
         SUM(rpt_option_i) 
    FROM SELECT * 
           FROM results1 
         UNION ALL 
         SELECT * 
           FROM results2 
         UNION ALL ... 
         GROUP BY run_time_c 
         ORDER BY run_time_c

Is there a way I can do this in Oracle? Fetch with bulk collect looked promising, but I didn't see a good way to use it for my specific scenario.

Answer 1

You could do this as a union, including a column that identifies the Group. The individual selects would replicate more or less what your DailyLoad SP is doing.

 select foo.Mygroup, sum(foo.col1) 
  from
 (
 select 'A' as MyGroup, col1 WHERE ...
 union all
 select 'B' as MyGroup, col1 WHERE ...
 union all
 select 'C' as MyGroup, col1 WHERE ...
 ) as Foo
 group by MyGroup

If the number of groups is not known in advance, you could build a dynamic sql statement that conforms to this basic structure.

If the number of groups is so large that your dynamic statement would be too large, you could use a stored procedure that pushes the results from each call into a temp table along with a MyGroup column. Then you could issue your group by select statement against the temp table.

Answer 2

If the procedure's out parameter is a ref cursor, and you can't replicate what it's doing internally to make a nice single set-based query as OMG Ponies suggests, this previous answer may help. You can use an intermediate pipelined function to turn the sys_refcursor results into something you can treat as a table:

create package p as
    type tmp_rec_type is record (run_time_c varchar2(4),
        rpt_option_i number);
    type tmp_table_type is table of tmp_rec_type;

    procedure dailyload(p_date in date, p_results out sys_refcursor);
    function func(p_date in date) return tmp_table_type pipelined;
    procedure sumload(p_start_date in date, p_results out sys_refcursor);
end;
/

create package body p as
    /* Your existing procedure, which may be elsewhere */
    procedure dailyload(p_date in date, p_results out sys_refcursor) is
    begin
        open p_results for
            select to_char(created, 'HH24MI') as run_time_c,
                count(*) as rpt_option_i
            from all_objects
            where trunc(created) = trunc(p_date)
            group by to_char(created, 'HH24MI');
    end;

    /* Intermediate pipelined function */
    function func(p_date in date) return tmp_table_type pipelined is
        tmp_cursor sys_refcursor;
        tmp_rec tmp_rec_type;
    begin
        dailyload(p_date, tmp_cursor);
        loop
            fetch tmp_cursor into tmp_rec;
            exit when tmp_cursor%notfound;
            pipe row(tmp_rec);
        end loop;
    end;

    /* Wrapper function to join the result sets together */
    procedure sumload(p_start_date in date, p_results out sys_refcursor) is
    begin
        open p_results for
            select run_time_c, sum(rpt_option_i) from (
                select * from table(func(p_start_date))
                union all
                select * from table(func(p_start_date + 1))
                union all
                select * from table(func(p_start_date + 2))
            )
            group by run_time_c;
    end;
end;
/

Guessing your data types, and picking data from a random table just as an example, of crouse. To call from SQL*Plus or SQL Developer:

var results refcursor;

exec p.sumload(to_date('01-Jun-11','DD-Mon-RR'), :results);

print :results

Answer 3

I haven't the time to test this, but I believe this will work:

1. Modify your sproc to make the SYS_REFCURSOR an IN OUT parameter, rather than just an OUT.
2. Set your parameters in for/each loop (whatever language you are working in...)
3. In the loop pass in the reference to the same SYS_REFCURSOR.
4. Inside the sproc create a local SYS_REFCURSOR variable to select into as you currently do.
5. Inside the sproc merge the local and the parameter SYS_REFCURSOR

This should build your result set.

if you don't want to test this, I may build a test case for this in C#/Oracle 10g over the weekend in order to test my hypothesis.

Another option, if you are on 11g, would be a Pipelined Query as discussed How to create Oracle stored procedure which can return specific entities as well all entity (look to @tbone's answer and the link he provides...)

Answer 4

You can use oracle global temporary table to accumulate and further process the data.

It is in-memory structure and has very little overhead.