Function-based indexes -- please help!

I have followed the things suggested on this (and other sites) for minimum requirements for the function-based index creation:

QUERY REWRITE privilege granted to the index creator

COMPATIBLE set to 8.1.7

ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED
... and I get the following errors:

CREATE UNIQUE INDEX index1
ON table1 (col1, col2, col3, NVL(dateCol1, TO_DATE('12/31/9999', 'MM/DD/YYYY'))) ORA-01031: insufficient privileges

... or, if the index creation line is modified to this:
CREATE UNIQUE INDEX index1
ON table1 (col1, col2, col3, NVL(dateCol1, '31-12-9999')) , a different error:

ORA-01743: only pure functions can be indexed

Any ideas on how to work around this?

Thank you in advance!

--Alex Received on Wed Apr 30 2003 - 13:34:33 CDT