Kbase P3942: How are case-insensitive indexes displayed when using DataServer
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
08/08/2005 |
|
Status: Verified
GOAL:
How are case-insensitive indexes displayed when using DataServer
GOAL:
How the Case Insensitive index shown with DataServer
GOAL:
How to modify a table to support case-insensitive index in Oracle DataServer
FACT(s) (Environment):
Oracle
Oracle DataServer
FIX:
To support case-insensitive indexes, the DataServer creates a new field just prior to the case-insensitive indexed field.
The name of the field will start with U##column-name
Example: (U##COUNTRY, where COUNTRY is part of an INDEX).
When you use the Progress-to-ORACLE migration utility, Progress automatically modifies ORACLE data definitions to support case-insensitive indexes.
If you didn't migrate using Progress-to-ORACLE migration utility, to use case-insensitive indexes with an ORACLE database, make the following changes to your ORACLE database table:
1) Add a column of the same data type adjacent to the indexed column. The new column must precede the indexed column.
2) Name the column U##column-name.
For example, if your table has an indexed column named emp_id, name the column U##emp_id. The new column accommodates the uppercase version of the index.
3) Set the U## column to the uppercase value of the original column. The SQL statements have the following syntax:
UPDATE table-name
SET U##column-name = UPPER(column-name);
4) If non-Progress applications will be modifying the original column, you must create an ORACLE trigger to populate the U##column-name with the uppercase value of column-name.
5) Re-create the index with the U## column as a component in place of the original column.
6) If you have already created your schema holder, use the DataServer utilities in the Data Administration tool to update your schema image.