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

"Andreas Mosmann" <mosmann_at_expires-30-04-2008.news-group.org> schreef in bericht news:1207208024.45_at_user.newsoffice.de...
> Maxim Demenko schrieb am 02.04.2008 in <47F3D26E.5040606_at_gmail.com>:
>
>> 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.
> Interesting. But where or how can I use this *alias* "trim"? It is not to
> be seen anywhere. So it sounds to me as *alias* is a nice word for a bug.
> An error "missing brackets" would IMHO be more helpful.
>
>> Best regards
> Thank you for your explaination
>> Maxim
> Andreas Mosmann
>
> --
> wenn email, dann AndreasMosmann <bei> web <punkt> de

Actually, it is not an alias for the index, but an alias for the table. In some situations, the table is referenced in the index definition (but I have never seen this in practice). This is one from the books:

CREATE INDEX salary_func_i ON persons p

   (TREAT(VALUE(p) AS part_time_emp_t).salary);

In this case, there is a table of part_time_emp_t (a user defined type) and the index is built over the salary attribute of the type. So VALUE(p) must be treated as the type part_time_emp_t and somehow you need to reference the table in the index.

An error 'missing brackets' is not appropriate here, for the syntax is correct. Maybe it would have been better to force a space in the syntax. In that case your definition would not have met the syntax, and would have caused less confusion.

Shakespeare Received on Thu Apr 03 2008 - 03:07:53 CDT