Progress
DataServer
for ORACLE Guide


Case-insensitive Indexes

ORACLE supports only case-sensitive indexes, but Progress allows you to set the attributes of a field so that it is either case sensitive or case insensitive (case insensitive being the default). The DataServer makes this feature compatible across Progress and ORACLE databases. When a field is not case sensitive, Progress does not distinguish between uppercase and lowercase letters for that index when sorting or matching data. In general, this flexibility in an application makes data entry easier for end users since they can enter lowercase or uppercase versions of an index. However, when you want to enforce an uppercase/lowercase distinction in your applications, set the attribute to case sensitive.

Modifying Tables to Support Case-insensitive Indexes

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.
  3. 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.

  4. Set the U## column to the uppercase value of the original column. The SQL statements have the following syntax:
  5. UPDATE table-name
    SET U##column-name = UPPER(column-name); 
    

  6. 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.
  7. Re-create the index with the U## column as a component in place of the original column.
  8. If you have already created your schema holder, use the DataServer utilities in the Data Administration tool to update your schema image. See "The DataServer Tutorial," for instructions on updating a schema image.

When you use the Progress-to-ORACLE migration utility, Progress automatically modifies ORACLE data definitions to support case-insensitive indexes. For example, if your Progress database has an indexed and case-insensitive field named emp_id, the utility creates an additional column named U##emp_id in the ORACLE table and copies the uppercase value of the data into it.

When the DataServer creates uppercase values for U##column-name, it might not create expected values for 0-127 range characters in double-byte code pages. This might cause the “CHARACTER” option of the LENGTH, OVERLAY, and SUBSTRING functions to return unexpected results. Use the “RAW” or “FIXED” options. You should pass “RAW” as a string constant, not an expression.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095