Re: Create an operator with returning a fixed length string

On 7 Jan 2004 18:13:23 -0800, dmx_frank_at_yahoo.com (Frank Y) wrote:

>dmx_frank_at_yahoo.com (Frank Y) wrote in message news:<9d599275.0401052027.495c4690_at_posting.google.com>...
>> Hello there,
>>
>> I am using Oracle 9i on Windows 2000 Server. I need to create an
>> operator to return a string with length 20.
>>
>> If I use the following statement:
>> CREATE OPERATOR test ....
>> RETURN VARCHAR2....
>> ...
>>
>> the return string has a default length 4000.
>>
>> Does any one know to make the return string length 20?
>>
>>
>> thanks a lot.
>>
>> Frank
>
>The reason I ask this because I need to create an operator to build a
>domain index. And this operator is used in a View. When my
>application accesss this view, an buffer overflow was occured because
>the return length of the operater is 4000. I cannot change the
>application source code because I donot own it. The only way I can
>think is to limit the output size of the operator, that is why I ask
>this question.
>
>Any one can have ideas to help me?

I haven't worked with user-defined operators. But in the case of a function-based index, you have to "tell" the database the size of the return value by always doing a SUBSTR(...,1,maxlength) on the return value, and declaring the function as DETERMINISTIC. Perhaps something similar applies to operators.

John

--
Photo gallery: http://www.pbase.com/john_russell/