ORa-1743 error when tried to create function based index
Hi all,
Database version 10.2.0.4.
Problem:
Have a table column of DATE type.
create table my_table(col1 date);
Wanted to create a function based index on it like this:
create index ix_my_table_col1 on my_table (cast(from_tz(cast(col1 as timestamp),'UTC') at time zone 'CET' as date))
Received error: ORA-01743: only pure functions can be indexed.
This is the definition of the error.
// *Cause: The indexed function uses SYSDATE or the user environment.
// *Action: PL/SQL functions must be pure (RNDS, RNPS, WNDS, WNPS). SQL expressions must not use SYSDATE, USER, USERENV(), or anything else dependent on the session state. NLS-dependent functions are OK.
Database version 10.2.0.4.
Problem:
Have a table column of DATE type.
create table my_table(col1 date);
Wanted to create a function based index on it like this:
create index ix_my_table_col1 on my_table (cast(from_tz(cast(col1 as timestamp),'UTC') at time zone 'CET' as date))
Received error: ORA-01743: only pure functions can be indexed.
This is the definition of the error.
// *Cause: The indexed function uses SYSDATE or the user environment.
// *Action: PL/SQL functions must be pure (RNDS, RNPS, WNDS, WNPS). SQL expressions must not use SYSDATE, USER, USERENV(), or anything else dependent on the session state. NLS-dependent functions are OK.
0