Oracle Database Discussions

Hi all,

I'm having a problem on a database. When creating an index on an existing table on the upper of a column, Oracle raises a ORA-30553. I made this index on 40 other databases, with the same and different versions, without a problem. When creating the index on a copy of the table, it is created succesfully...

Here's the code:

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE    11.2.0.3.0      Production

TNS for 64-bit Windows: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

SQL> CREATE INDEX IDX_SRE_E_MAIL_UPPER on fsynrelatie(upper(sre_e_mail)) tablespace idx;

CREATE INDEX IDX_SRE_E_MAIL_UPPER on fsynrelatie(upper(sre_e_mail)) tablespace idx

*

ERROR at line 1:

ORA-30553: The function is not deterministic

SQL> create table fsynrelatie2 as select * from fsynrelatie;

Table created.

SQL> CREATE INDEX IDX_SRE_E_MAIL_UPPER on fsynrelatie2(upper(sre_e_mail)) tablespace idx;

Index created.

SQL> drop table fsynrelatie2;

Table dropped.

SQL> show parameter query_rewrite_enabled

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

query_rewrite_enabled                string      TRUE