Ora-1450: varchar2 and function based indexes

[cni]

I am trying to create a function based index which is returning varchar2 and get the oracle error ORA-01450: maximum key length (3218) exceeded.

As far as I can read from the error code description in the manual the problem is that Oracle suspects that the result from my function has a longer length than supported for a index value. If this is the case my problem is that I can't specify a maximum length of the value from my function.

Does anybody have a work-around for this problem?

Here are my documentation:

create table mytable
(
felt1 number(20),
felt2 varchar2(100)
);

create function myfunction (iv integer) return varchar2
deterministic
is
begin
return substr(to_char(iv),1,5);
end;

create index myfuncindex on mytable (myfunction(felt1));
Errormessage ORA-01450: maximum key length (3218) exceeded

 

[carp]

Have you tried:

CREATE INDEX my_index ON mytable(substr(to_char(felt1),1,5));

It worked for me.