Try OpenEdge Now
skip to main content
DataServer for Oracle
Initial Programming Considerations : Database design issues : Case-sensitive


OpenEdge 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 OpenEdge and Oracle databases. When a field is not case sensitive, OpenEdge 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. Oracle supports case-insensitive indexes by using the UPPER(column name) function as an index component.
After the OpenEdge to Oracle migration process, if you perform a schema-pull operation, the columns migrated as case-insensitive, which were used as index components, get appropriately pulled as case-insensitive columns into the schema holder, but, the other columns that did not participate in indexes do not get any indication from the Oracle server of their case-insensitivity. Because Oracle databases are assumed to be case-sensitive by default, when the Update/Add Table Definitions option is selected during the Schema pull operation to pull table definitions directly from an Oracle server, the unindexed table columns are assumed to be case-sensitive, even if they were migrated as case-insensitive and you had not made any changes to the table definitions you pulled. If the generated r-code was compiled before the schema pull operation, it would have a schema mismatch with the pulled server definitions. This is because the r-code takes into account case-sensitivity during the schema check.
To avoid schema mismatch during r-code execution, run the Adjust Schema utility against the original OpenEdge database after the Update/Add Table Definitions operation on tables that were originally migrated with unindexed case-insensitive columns. This causes the schema holder to be adjusted with the case-insensitive attribute of the column originating from the migrated OpenEdge database.