.Few days ago at the Upgrade and Migration Workshop in Hamburg when I did talk about Unicode Migrations a customer had an interesting question.
“Why does the documentation states NOT to set
CHARin the init.ora/spfile to enable char semantics for newly build objects by default?”
Honestly I was not aware of that hint. So I did some research and tried to get an answer from the developers.
The documentation says clearly not to set that parameter permanently:
- Oracle Database Reference on
Oracle strongly recommends that you do NOT set the
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
- Oracle Database Globalization Support Guide on
- Same warning as above – but also states:
NLS_LENGTH_SEMANTICSdoes not apply to tables created in the
SYSschema. The data dictionary always uses byte semantics. Tables owned by
SYSalways use byte semantics if the length qualifier
CHARis not specified in the table creation DDL.
And my colleague from Poland, Sergiusz Wolicki did reply (as always) very quickly (thanks!!!):
“The warning is general as the problem may affect Oracle data
dictionary scripts for schemas such as SYSTEM, CTXSYS, ORDSYS, XDB,
SYSMAN, Oracle application scripts for schemas such as APEX_030000,
APPL, APPLSYS, GL, and other eBS or Peoplesoft or JDE, but also
third party prepackaged applications and customer’s own legacy
The recommendation is to avoid the dependency on the parameter
altogether. The character and byte length semantics should be
explicitly specified in column definitions and PL/SQL code:CREATE TABLE emp( ..., first_name VARCHAR2(100 CHAR), last_name VARCHAR2(100 CHAR) ) / CREATE PROCEDURE read_emp IS v_first_name emp.first_name%TYPE; v_last_name emp.last_name%TYPE; v_full_name VARCHAR2(202 CHAR); BEGIN ... SELECT first_name, last_name INTO v_first_name, v_last_name FROM emp WHERE ...; v_full_name := v_last_name || ', ' || v_first_name; END; /
If you find specifying the data type and the length explicitly as an
obvious thing, you should also expect that the length semantics
(length units) are specified explicitly.
If you need to migrate existing scripts quickly, put an
SESSION SET NLS_LENGTH_SEMANTICS=CHARat the beginning of each
script and after each
CONNECTcommand in the script. “
I this realy still a problem in 2020?
I’ve got (again) an installation instruction with included init.ora where NLS_LENGTH_SEMANTICS is defined as CHAR.
Even at database creation time!
I remember some databses that worked fine with this configuration.
I will not raise a SR, as support will only answer with the words of the reference note.
according to my colleagues taking care on this topic, this is still recommended.
And I think the BYTE requirement for upgrades and patching still rules.
we are preparing for next upgrade from 19c to next LTS (possibly 23c). TOP management ask as to make it in near to zero downtime, so we are going to use OGG. This is OK. However, we would like also to change characterset to AL32UTF8. So we will use expdp/impdp to new database (some of them are about 50TB and inplace DMU is not feasible). However we have troubles with SYS.AQ$_JMS_TEXT_MESSAGE and some silimiar types. For instance, some application AQs are using SYS.AQ$_JMS_TEXT_MESSAGE type as payload. This system type is wise, it stores string as VARCHAR2(4000) when possible and store it as CLOB in case it is > 4000. However, if you use expdp/impdp, oracle tries to import data in binary way. And if you are unlucky, and have 3950 byte string on source database, on target DB it can be let me say 4100 and it won’t fit to varchar2, causing impdp fail on buffer overflow. Yes, fortunaltey we have max_string_size=extended, but unforutnaltely, SYS TYPES are always compiled with NLS_LENGTH_SEMANTICS=BYTE (even if we crated db with instance level parameter set to CHAR, as we know, NLS_LENGTH_SEMANTICS does not aply for SYS).
But it is still possible to recompile ALTER TYPE SYS.AQ$_JMS_TEXT_MESSAGE NLS_LENGTH_SEMANTICS=CHAR on fresh created empty database. Is it supported? 🙂 I have only found it is supported to recmpile SYS.DBMS_STANDARD with PLSQL_SCOPE_SETTINTGS=ALL … so altering types could be also supported. I would recompile all SYS.AQ$_JMS_% types…
IF you do not need to migrate data, then you do not run into any issues. Even with NLS_LENGTH_SEMANTICS=BYTE set for AQ$_JMS_TEXT_MESSAGE, if you have string with 4001 bytes, oracle stores it in CLOB and everything works properly. Only the migration is issue….
I really really don’t know if this is supported. And I am not in the position of making a statement.
You please need to check with Oracle Support.
Cheers, and sorry for the late reply.