unique index on varchar2 column/805

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:49:04 CDT