Saturday, January 31, 2015

EBS 12.2 / 12.1-- UTF8, too long error in Forms screen, ORA-12899: value too large for column, DMU conversion ...

In UTF8 EBS 12.2 database; you may encounter value too long errors while performing some operations..
We have recently faced with an ORA-12899 in a LOV..
The column length inside the database was 40, the problematic data was 25 chars.. It had 2 turkish characters , so its byte length was 29.  (In UTF8 , turkish chars are 2 bytes in lenght)
So, based on above information, we should not encounter any errors, becuase we have the 40 bytes as column length , and we try to insert only 29 bytes(25 chars)..
After some analysis, we have found a control in the form itself.. The form was the problem point. It has the control and the control did not let us to insert more than 25 bytes on the relevant table..
We have used a workaround and did not use any turkish characters in this form..
Note that : The fix should be a patch.. (no such a patch available in Oracle Support, and SR was needed)

The interesting thing is that the problem have started after UTF8 migration.
This system was a EBS vm template and the database inside of this template was ASCII..
So , we needed to convert it .. We had convert it to uniform (UTF8 or AL32UTF8), because there were some application (like Hyperion) which were planned to use this database for storing their repositories, and these applications wants their database to be UTF8.
Also , we could not convert the database to WEISO8859P9 (turkish) charset ,because convert tool (DMU) has the capability for converting to uniform only.. Moreover; Converting to P9 was not supported in the first place. Besides, the valid reasons to choose WEISO8859P9 instead of UTF8 can be performance or db size..(except Thai chars in EBS , if using Thai chars in EBS, it might be a good to choice to use thai specific charset rather than utf8)
So, In general; column length should not be an issue. For packaged applications, application owner should already configured the table columns to support uniform char sizes. Also for in-house developed applications, column lengths may be increased accordingly. Ofcourse, In EBS actions like increasing column lenghts or changing NLS_LENGTH_SEMANTICS parameter to "char " , are not supported..

Anyways; the problem was obvious.. Possible workarounds and fix are as stated above..  The problem was not the database , or it was not the DMU.. The convertion was successful, but still I want to give you some info about the problems which may be encountered in these layers..
Okay.. Lastly, I want to share the information that I have gathered and filtered during my analysis and researches.. This info may be helpful for you in the future.

  • Despite Note 1283764.1 which allows the database character conversion from UTF8/AL32UTF8 to another character set, this is not supported for E-Business Suite as it will result in data loss.
  • Globalization Guide for Oracle Applications Release 12 (Doc ID 393861.1)
It is good to be aware of some problems which may be encountered while using DMU for Db charset conversions.

  • Existing data may expand beyond the length of its column width when converting to Unicode. For instance, WE8ISO8859P1 is a single-byte character set, so every character is stored in one byte; a 20 byte column holds 20 characters. In UTF8, some of those characters will take up more than one byte, and then the 20 characters may require more than 20 bytes of storage space. The impact of data expansion varies according to the character sets, languages, and amount of data involved.
  • If the Data Expansion is reported and the expanding data is Oracle seed data, contact Applications Support; if customer data, use the E-Business Suite UI to shorten the data. If you cannot navigate to the data, contact Applications Support.
  • Never modify the column size attribute in order to accommodate the existing data. Although the DMU allows this, Oracle E-Business Suite does not allow it and will not support a database with a modified column size.
  • Never truncate the expanding data.You can display ROWID in the DMU cleansing editor, but this is only for locating the exceptional data. E-Business Suite does not support cleansing data through the DMU.

Also , I have found a problem record for MTL_SYSTEM_ITEMS_B Materialized view. It was as follows;
"The DESCRIPTION column in the MTL_SYSTEM_ITEMS_B was converted from VARCHAR2(240) to VARCHAR2(240 Char)." 

After Converting Database To AL32UTF8 Materialized View MTL_SYS_ITEMS_SN Fails With ORA-12899: Value Too Large For Column ORA-12008 (Doc ID 1493499.1)

No comments :

Post a Comment