I dont know yet about O8i.
How much the information do you store in this column?
If not so much, the better way is
> - make another column with same values converted to
> lower case. Create unique index on this column.
Otherwithe if there is weak insert/update activity you would use trigger
to check an uniqueless of value.
Any case you need the trigger.
Why wouldn't you store this column lowercased at all?
the simple conversion trigger can help you.
What do you mean in " This works for insert but not for select as we do not
want
to use 'like' instead of '=' " ?
You can use "=" any way.
Regards,
Mark
Philipp Florschutz <pf_at_gmx.net> wrote in message
news:7ld3o0$cbt$1_at_black.news.nacamar.net...
> When migrating from SQL server to 8.0.5. we encounter some difference
> with unique indexes on varchar2 columns.
> On SQL server if we have two strings "bla" and "Bla" these
> are the same and inserting/updating these on such a table will
> fail for the second one due to unique constraint.
> This needs to be reimplemented on Oracle, where "bla" and "Bla"
> are two different strings and insert will not collide.
>
> We are using a special OCI application that is completely
> independent from our tables and must not take any action,
> that somehow is column dependent. I am in search for a
> database internal solution not visible to the application.
> I have gathered some solutions:
>
> - wait for 8i and use functional index on column.
> No choice, as we need to do this right now. 8i for us will
> be delivered mid of July, if at all.
> (Does this actually work for select as well?)
> - make another column with same values converted to
> lower case. Create unique index on this column.
> Keep column consistent by use of pre-insert/update trigger.
> This works for insert but not for select as we do not want
> to use 'like' instead of '=' (needs high level application
> changes)
> - Modify our complete application and do all the conversion stuff
> there. This makes our complete system migration much more
> complicated as we need to do it all at once then,
> as the application must be exchanged. Also, this needs
> to be combined with solution 2 anyway.
>
> Is there a simple solution that we did not see (we even
> asked at Oracle support hotline)? This actually is a simple
> and frequent problem when coming from SQL server and
> I suspect, there is some easy way of doing it.
> Any hints, anyone? Thank you for any helpful comment.
> Philipp
>
Received on Wed Jun 30 1999 - 07:47:32 CDT
