Re: PL/SQL and ROLLUP

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 21 Jun 1999 11:58:23 GMT
Message-ID: <376f285d.3191238@newshost.us.oracle.com>

A copy of this was sent to "Rob Edgar" <robedgar_at_hkstar.com> (if that email address didn't require changing) On Mon, 21 Jun 1999 17:29:47 +0800, you wrote:

>I am using PL/SQL and have a SELECT statement that uses the GROUP BY
>ROLLUP() command.
>
>I have excuted the SQL Statement in a sql toolup and it works OK so it is a
>valid statement but in a PL/SQL package I get " Identifier ROLLUP must be
>declared" when I try to save the package.
>
>Does anyone know what is wrong with this.
>Rob
>

Bug 881365 has been filed for this. Rollup is a new Oracle8i feature that creates 'super aggregates' for you. For example:

SQL> select deptno, decode( grouping(deptno), 1, 'Total:', '' )l , count(*) c   2 from emp
  3 group by rollup (deptno)
  4 /

    DEPTNO L C
---------- ------ ----------

        10                 3
        20                 5
        30                 6
           Total:         14

But the plsql sql parser doesn't yet understand this new syntax as demonstrated:

SQL>
SQL> declare

  2      cursor c1 is
  3      select deptno, decode( grouping(deptno), 1, 'Total:', '' ) l, count(*)
c
  4      from emp
  5      group by rollup (deptno) ;
  6  begin
  7      null;

  8 end;
  9 /
declare
*
ERROR at line 1:

ORA-06550: line 5, column 14:
PLS-00201: identifier 'ROLLUP' must be declared
ORA-06550: line 3, column 5:

PL/SQL: SQL Statement ignored

The workaround is to use dynamic sql (the following dynamic sql is Oracle8i, release 8.1 specific -- it will not work in earlier releases)

SQL>
SQL> declare

  2      type refcur is ref cursor;
  3      c1          refcur;
  4      l_deptno    number;
  5      l_l         varchar2(25);
  6      l_c         number;
  7  begin
  8      open c1 for
  9      'select deptno, decode( grouping(deptno), 1, ''Total:'', '''' ) l,
 10              count(*) c
 11      from emp
 12      group by rollup (deptno)';
 13  
 14      loop
 15          fetch c1 into l_deptno, l_l, l_c;
 16          exit when c1%notfound;
 17          dbms_output.put_line( l_deptno || ' ' || l_l || ' ' || l_c );
 18      end loop;
 19  
 20      close c1;

 21 end;
 22 /
10 3
20 5
30 6
Total: 14

PL/SQL procedure successfully completed.

that makes it so that plsql doesn't do the parsing of the query and you can use extensions to sql that plsql doesn't yet understand.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Fine Grained Access Control", added June 8'th  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Jun 21 1999 - 06:58:23 CDT