Having a question about the database character settings


We have received different opinions from our DBA for the international language set. Is it OK to leave it as BYTE?

  • Database Character Set: AL32UTF8
  • Character Length Semantic: BYTE
  • Message: WARNING!! The database character set is inconsistent with the character length. When using a UTF character set which supports multibyte characters the NLS_LENGTH_PARAMETER must be set to CHAR. Refer to the Administration Guide for more details

In the following Oracle 11g document and Oracle 12c document, there is a caution given by Oracle when setting the parameter to CHAR:

  • NLS_LENGTH_SEMANTICS = ‘CHAR’
  • Caution: Oracle strongly recommends that you do NOT set the NLS_LENGTH_SEMANTICS parameter to CHAR in the instance or server parameter file. This may cause many existing installation scripts to unexpectedly create columns with character length semantics, resulting in runtime errors, including buffer overflows.

TRIRIGA is not one of those application that is impacted. In fact, we require it for the length of fields to be saved correctly. Oracle can be safely ignored in this case. If they do not use NLS_LENGTH_SEMANTICS = ‘CHAR’ and they put MBCS data into their database, they will run into issues when saving data. When we create tables, let’s say with the size of 100, we expect it to be 100 CHARs not 100 BYTEs, and if saving 100 MBCS characters, you will not be able to save. If you are saving a single byte date, it doesn’t matter, BYTE is fine.

Having said that, the NLS_LENGTH_SEMANTICS is not something to change midstream. If they already created their TRIRIGA database and the length semantic was at BYTE, their database fields are probably already set to BYTE. TRIRIGA is not one of those applications where this is applicable. In fact, without it set correctly, we will have save issues. We rely on the database to handle field size properly and our documentation clearly states that if you are going to save MBCS data, your NLS_LENGTH_SEMANTICS should be set to ‘CHAR’ prior to creating the TRIRIGA database.

Continue reading

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s