I wanted to see if anyone has set up the following settings in an Oracle Database for TRIRIGA 10.5.2 and 3.5.2:
- 1. NLS_LENGTH_SEMANTICS: Should this be set to CHAR? In our current production environment, it’s set to BYTE, but the TRIRIGA support documentation says that this can lead to data loss, so they recommend using CHAR.
- 2. NLS_CHARACTERSET: This is set to WE8ISO8859P1 in our current production environment, but the support document says that it must be UTF-8 or UTF-16.
- 3. Block size: This is set to 8k, but the documentation recommends using 16k.
For (1) and (2), if you never want to store multibyte characters, then what you have is fine. But if you do, then you must use what the support documentation suggests. Once you have your database created, it is difficult and time-consuming to change it, and it needs to be done outside of TRIRIGA. As for (3), I would encourage you to use 16k, since it will allow you better throughput and paging, unless you have a strong reason why you need to stay at 8k.
[Admin: This post is related to the 04.04.16 post about database character settings. NLS refers to National Language Support parameters in Oracle. To see other related posts, use the Multibyte tag, MBCS tag, or NLS tag.]
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.
I’ve currently seen an error when importing the data pump dump of Oracle. The error concerned a failed SQL when creating the REP_TEMPLATE_HDR table. After searching, the error was caused by the default value of column OM_UPDATED_DATE… The default value was a char value in format “DD-MON-RR”, which does not correspond to our nls_date_format setting.
Do you have any recommendation on NLS settings for TRIRIGA Application Platform 3.4.2? I haven’t found any information in the Installation Guide (pdf_tap_install.pdf). Should we define “DD-MON-RR” as nls_date_format and “AMERICAN” as nls_date_language? Note that we will have two languages: EN_US (US English) as the base language and FR_FR (French) as the second language. Thanks in advance for your suggestion!
[Admin: NLS refers to National Language Support parameters in Oracle.]