SQL & PL/SQL

I am working on a datawarehousing application that relies heavily on dynamic SQL. Essentially the user can supply a set of parameters which define the different ways they want to aggregate different types of data. There are literally thousands of different combinations.

I have been able to create the code to do what I want. For efficiency purposes, I make heavy use of the SYS_CONTEXT function, since I do not know until run time how many bind variables there may be (a great tip picked up on the asktom.com website).

Anyway, I have come across what I think is a bug. For demonstration purposes, I will use the EMP table in the SCOTT schema. Overlook the fact that the following queries make no sense, they illustrate the problem fairly well. In reality, my queries are much more difficult and are dynamically created.

SQL> select DEPTNO,MGR,count(distinct empno) from emp where ename != sys_context('USERENV','SESSION_USER')
2 group by cube(DEPTNO,MGR);
select DEPTNO,MGR,count(distinct empno) from emp where ename != sys_context('USERENV','SESSION_USER')
*
ERROR at line 1:
ORA-02070: database does not support operator SYS_CONTEXT in this context


This seems to be due to a combination of using COUNT(DISTINCT) and CUBE() with more than one column.


Remove the DISTINCT:
SQL> select DEPTNO,MGR,count(empno) from emp where ename != sys_context('USERENV','SESSION_USER')
2 group by cube(DEPTNO,MGR);

DEPTNO MGR COUNT(EMPNO)
---------- ---------- ------------
1
14
7566 1
7698 5
7782 1
7788 1
7839 4
7902 1
10 1
10 4
10 7782 1

DEPTNO MGR COUNT(EMPNO)
---------- ---------- ------------
10 7839 2
20 4
20 7566 1
20 7788 1
20 7839 1
20 7902 1
30 6
30 7698 5
30 7839 1

20 rows selected.


Or leave DISTINCT and CUBE by only one column:
SQL> select DEPTNO,count(distinct empno) from emp where ename != sys_context('USERENV','SESSION_USER')
2 group by cube(DEPTNO);

DEPTNO COUNT(DISTINCTEMPNO)
---------- --------------------
10 4
20 4
30 6
14

4 rows selected.


Has anyone else experienced this error? Any suggestions?

Thanks in advance.