ORA-32635: not a single cell reference predicate

By Franck Pachot

.
I spent 30 minutes to try to understand this error with a query that I’ve run in many environments for years. So if you google it you may arrive here and the solution may be checking whether you have NLS_COMP=LINGUISTIC

SQL> alter session set nls_comp=linguistic;

Session altered.

SQL> alter session set nls_language=french;

Session modifiee.

SQL> select * from (
  2  select 42 snap_id,'TM' type,'DB time' name,0 microseconds from dual
  3  ) model return updated rows
  4    partition by (snap_id)
  5    dimension by (type,name)
  6    measures (microseconds) ( microseconds['','']=microseconds['TM','DB time'])
  7  /
  measures (microseconds) ( microseconds['','']=microseconds['TM','DB time'])
                                                             *
ERREUR a la ligne 6 :
ORA-32635: ce n'est pas un predicat de reference de cellule unique

SQL> host oerr ora 32635

32635, 00000, "not a single cell reference predicate"
// *Cause: A predicate that is not a single cell reference predicate was specified
//         where a single cell reference predicate was expected.
//         A single cell reference predicate is either a constant expression
//         or a predicate of the form 
// *Action: Make sure that the predicate is a proper single cell
//          reference. In some cases, you might have to put explicit
//          type conversion operators (or casts) on the constant expression.

This error message was not very clear to me. Of course the NLS parameters were set at instance level, so not immediately visible.
I had to reduce my long query to one similar to the one above (because initially I suspected duplicate values in the dimensions but I was wrong) and with one line. Given the misleading clue “you might have to put explicit type conversion operators (or casts) on the constant expression” (I tried to cast to CHAR first) I finally checked at NLS parameters.

So now you have the solution:

SQL> alter session set nls_comp=BINARY;

Session modifiee.

SQL>
SQL> select * from (
  2  select 42 snap_id,'TM' type,'DB time' name,0 microseconds from dual
  3  ) model return updated rows
  4    partition by (snap_id)
  5    dimension by (type,name)
  6    measures (microseconds) ( microseconds['','']=microseconds['TM','DB time'])
  7  /

   SNAP_ID TY NAME    MICROSECONDS
---------- -- ------- ------------
        42                       0

The linguistic comparison may not give a unique result depending on the character set, and that may be the cause for this message. With BINARY comparison (the default) there’s no problem.

By the way, I was not surprised to find nothing in Google or MOS. I know that I may be the only one using the MODEL clause 😉 and database with such NLS settings are rare (hopefully).