Problem with function based index on timestamp

Hello all,

I tried to build an index like:

CREATE TABLE A
(

   TS TIMESTAMP(3) WITH LOCAL TIME ZONE );

CREATE INDEX IDX_B ON A
(TO_NUMBER(TO_CHAR(TS,'YYYYMMDD')))

LOGGING
NOPARALLEL; SELECT * FROM A; After creating the index I cannot select from the table any more. Looks like there are type conversion problems but why?

I face this problem on Oracle 10g 10.1.0.4

Select column_expression
from sys.DBA_IND_EXPRESSIONS
where index_name = 'IDX_B'

results in:

TO_NUMBER(TO_CHAR("TS",:1)) Björn Received on Thu Dec 15 2005 - 10:39:50 CST