ORA-00932: inconsistent datatypes: expected - got CLOB

Si vous tombez sur l’erreur ORA-00932 c’est que vous avez exécuté une requête contenant une colonne de type LOB dans la clause GROUP BY ce qui n’est absolument pas possible dans Oracle.

Voici un petit exemple très simple :

SQL> create table emp2 as select * from emp;

Table created.

SQL> alter table emp2 add (job_desc clob);

Table altered.

SQL> update emp2 set job_desc=to_clob(job);

14 rows updated.

SQL> commit;

Commit complete.

SQL> select job, count(1) from emp2 group by job order by 2 desc;

JOB         COUNT(1)
--------- ----------
CLERK              4
SALESMAN           4
MANAGER            3
ANALYST            2
PRESIDENT          1

SQL> select job_desc, count(1) from emp2 group by job_desc order by 2 desc;
select job_desc, count(1) from emp2 group by job_desc order by 2 desc
                                             *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB

Dans mon exemple je pourrais m’en sortir en convertissant mon champ CLOB en VARCHAR2. Ceci n’est possible bien sûr que si mon CLOB ne fait pas plus de 4000 octects :

SQL> col jd for A20
SQL> select to_char(job_desc) jd, count(1) from emp2 group by to_char(job_desc) order by 2 desc;

JD                     COUNT(1)
-------------------- ----------
CLERK                         4
SALESMAN                      4
MANAGER                       3
ANALYST                       2
PRESIDENT                     1

Cette restriction fait partie d’un ensemble de restrictions d’utilisation de la clause GROUP BY. Ces restrictions sont listées dans la doc oracle « SQL Language reference » (chapitre 19) :
http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_10002.htm#i2065646

This clause is subject to the following restrictions:
• You cannot specify LOB columns, nested tables, or varrays as part of expr.
• The expressions can be of any form except scalar subquery expressions.
• If the group_by_clause references any object type columns, then the query will not be parallelized.