Kbase P111685: ORACLE LONG data is truncated to 256 characters when longer than 256
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
8/21/2007 |
|
Status: Verified
FACT(s) (Environment):
Progress 9.1x
Oracle DataServer
SYMPTOM(s):
ORACLE LONG data is truncated to 256 characters when longer than 256
Instead of returning expected number of characters, it returns only 256 characters
Only happens to "for each" statement
Does not happen to "find" statement
Using UTF-8 ORACLE database (NLS_CHARACTERSET=AL32UTF8 or UTF8)
On the client side, NLS_LANG is set to AMERICAN_AMERICA.WE8MSWIN1252
This is not reproducible in OpenEdge 10.0B
Problem can not be reproduced using the option QUERY-TUNING(NO-LOOKAHEAD) with the FOR EACH query
CAUSE:
Bug # OE00119049
CAUSE:
This issue is caused by an ORACLE OCI 7 bug (#4951405). The Progress DataServer for ORACLE version 9 is using the ORACLE OCI7.
In OCI8, used by the OpenEdge DataServer for ORACLE, the new OCIStmtExecute() call functions correctly and returns error code 1406 when a column is truncated due to a too small buffer. The DataServer expands the buffer and does a refetch of the column. As a result, the correct column value is returned to the user.
In contrast, in OCI7, the corresponding oexfet() call returns 0 (success) in both the column return code and the indicator fields when the column is truncated. Because of this, the DataServer has no way of knowing the data is truncated and a re-fetch is never attempted. 256 bytes were returned because that is the default buffer size for a column.
FIX:
There are 2 ways to work around this issue:
1- Upgrade to OpenEdge 10.0B or later, which is using the ORACLE OCI 8.
OR
2- If upgrading is not an option, specify the option QUERY-TUNING(NO-LOOKAHEAD).
For example:
FOR EACH Table QUERY-TUNING(no-lookahead) NO-LOCK:
DISPLAY Field.
END.