Kbase P128621: Unable to retrieve data from an indexed field containing extended characters against a UTF-8 Oracle
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
3/3/2010 |
|
Status: Unverified
SYMPTOM(s):
Unable to retrieve data from an indexed field containing extended characters against a UTF-8 Oracle schema holder and database
Exact match is specified for extended characters in the query
Records containing extended characters are not found when querying an indexed field
DataServer generates the following clause when querying an indexed field:
WHERE (U##FIELD1= upper(:1))
Records containing extended characters are found when querying a non-indexed field
DataServer generates the following clause when querying a non-indexed field:
WHERE (upper(FIELD2) = upper(:1))
Querying the U## shadow field via SQLPLUS shows that extended characters are not upper-cased
ASCII characters are converted to upper-case as expected
FACT(s) (Environment):
OpenEdge environment is configured as follows:
Schema holder codepage: UTF-8
Client: -cpinternal UTF-8
Oracle environment is configured as follows:
NLS_CHARACTERSET=AMERICAN.AL32UTF8
NLS_LANG=.AL32UTF8
Database migrated from Progress to Oracle
Oracle contains shadow field to support case insensitivity of the original Progress database
OpenEdge 10.1C
All Supported Operating Systems
Oracle DataServer
Oracle 9i
Oracle 10g
CAUSE:
Bug# OE00164406
CAUSE:
The data in shadow columns is supposed to be upper-cased. Querying an indexed field uses the shadow column as the point of comparison for the WHERE clause, and the WHERE clause is constructed as follows in the SQL statement passed to Oracle:
WHERE (U##FIELD1 = upper(:1))
If the data in the shadow column is not upper-cased then the search criteria is never matched hence the failure of the query. Conversely, a non-indexed field has no shadow column and its WHERE clause is constructed as follows:
WHERE (upper(FIELD2) = upper(:1))
The UPPER function is used on both sides so the search criteria is matched and the query returns the expected record(s).
FIX:
Option #1
Upgrade to 10.1C01 or later
Option #2
Avoid the use of shadow fields. This can be achieved differently whether the migration has already been performed:
- Before migration: Do not select the option "Create Shadow Columns"
- After migration: Delete all shadow columns on the Oracle side then update the schema holder.