CUBE and ROLLUP in PL/SQL

The ROLLUP operator can be used in the GROUP BY clause of a SELECT statement to group the selected rows based on the values of expressions, thus returning a single row summary for each group. The CUBE operator can be used to produce cross-tabulation values, thus produces totals in all possible dimensions. The CUBE and ROLLUP operators are not supported in PL/SQL, Release 8.1.5. The workaround for any new SQL enhancements that are not yet supported in PL/SQL is to use Dynamic SQL.

SQL> create or replace procedure test_rollup as
  2
  3     my_year test.year%type;
  4     my_region test.region%type;
  5     my_sum    int;
  6     my_count  int;
  7
  8  begin
  9
 10     select year, region, sum(profit), count(*)
 11       into my_year, my_region, my_sum, my_count
 12     from test
 13     group by rollup(year, region);
 14
 15  end;
 16  /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE TEST_ROLLUP:

LINE/COL ERROR
-------- -----------------------------------------------------------------
10/4     PL/SQL: SQL Statement ignored
13/13    PLS-00201: identifier 'ROLLUP' must be declared

SQL> create or replace procedure test_rollup as
  2
  3     type curTyp is ref cursor;
  4     sql_stmt varchar2(200);
  5     tab_cv curTyp;
  6
  7     my_year test.year%type;
  8     my_region test.region%type;
  9     my_sum    int;
 10     my_count  int;
 11
 12  begin
 13
 14     sql_stmt := 'select year, region, sum(profit), count(*) ' ||
 15                 'from test ' ||
 16                 'group by rollup(year, region)';
 17
 18     open tab_cv for sql_stmt;
 19     loop
 20
 21          fetch tab_cv into my_year, my_region, my_sum, my_count;
 22
 23          exit when tab_cv%NOTFOUND;
 24
 25          dbms_output.put_line (my_year || ' '||
 26                                nvl(my_region,'       ') ||
 27                                ' ' || my_sum || ' ' || my_count);
 28     end loop;
 29     close tab_cv;
 30
 31  end;
 32  /

Procedure created.