Re: user defined fct indexes vs 'normal' fct indexes

On 19 Jan 2003 23:00:38 -0800, rene.nyffenegger_at_gmx.ch (Rene Nyffenegger) wrote:
>Hello
>
>I am trying to make Oracle 9.2.0.1.0 on solaris to use my user
>defined function. And I don't have any success. Here's what happens:
>
>create or replace function qq_fnct (v in varchar2)
>return varchar2 deterministic
>as
>begin
> return translate(upper(v),':AEIOU',':');
>end;
>/

Oracle needs to know the maximum length, of the return value for each row that the function processes. You can make an index on a built-in function like UPPER(x) or LOWER(x), where Oracle knows that the return value is the same length as the input value. But a user-written function might return a longer or shorter string than the input parameter. So create your index on
SUBSTR(your_function(your_args),1,max_length) so that the index value is guaranteed never to be longer than the length that you specify.

John Received on Mon Jan 20 2003 - 23:56:46 CST