This content has been marked as final. Show 5 replies
in continuation to the above, what are the disadvantages if we set NLS_LENGTH_SEMANTICS to CHAR at db level.
NLS_LENGTH_SEMANTICS should not be set database-wide, from the reference manual:
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.
Installation-,patch-,upgrade scripts , which create/change data dictionary objects would lead to unintentional columns with character length semantics.
see MOS Doc please
Examples and limits of BYTE and CHAR semantics usage (NLS_LENGTH_SEMANTICS) [ID 144808.1]
Do not change this parameter in db level even if NLS_LENGTH_SEMANTICS does not apply to tables in SYS and SYSTEM schema. (The data dictionary always uses byte semantics.)
It is always easier to include an alter session command before table creation in scripts if you dont want to use varchar2(20 CHAR) syntax ...
But IMHO the best is to use (20 CHAR) precision in scripts to highlight that you use char semantics.
also check the following MOS doc for open bugs/issues about this parameter
Init.ora Parameter "NLS_LENGTH_SEMANTICS" Reference Note [ID 153365.1]
Edited by: Kecskemethy on Sep 16, 2011 5:21 AM
I am confirming the above answers. Use option 2.
Also, a common mistake, originating from incorrect documentation, is to say that NLS_LENGTH_SEMANTICS does not apply to tables in SYS and SYSTEM schema. This is true for the SYS schema only. SYSTEM schema does use the parameter.
Thank you for correcting me. Fixed my previous comment.