Re: 9i: Check- constraint Upper(Column)

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Wed, 02 Apr 2008 20:37:34 +0200
Message-ID: <47F3D26E.5040606@gmail.com>

Shakespeare schrieb:

> "Andreas Mosmann" <mosmann_at_expires-30-04-2008.news-group.org> schreef in 

>> Now I tried
>> CREATE UNIQUE INDEX MySchema.MyIndexName
>> ON MySchema.MyTable trim(upper(trim(CLOGINNAME)))
>> TABLESPACE MyIndexTableSpace LOGGING;
>> and it works ...
>>
>> The outer function is ignored. Is this my mistake or an oracle bug?
>>
>>> Cheers.
>> thank you
>>> Carlos.
>> Andreas Mosmann
>>
>>
>> --
>> wenn email, dann AndreasMosmann <bei> web <punkt> de
>
> I can reproduce this behaviour on my system.

Just to comment a little bit - it is of course not an oracle bug, as Shakespeare already shown, the brackets are *not* optional. The whole syntax diagram for table index clause looks like [ schema. ]table [ t_alias ]
(index_expr [ ASC | DESC ]

   [, index_expr [ ASC | DESC ] ]...)
[ index_properties ]
( can be looked up
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_5010.htm#i2062403 ),
so , what OP has done ( as in example above) - created function based index on (upper(trim(CLOGINNAME))) for table MySchema.MyTable with an *alias* trim, on this way the outer trim function will not do what OP expected.

Best regards

Maxim Received on Wed Apr 02 2008 - 13:37:34 CDT