Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 62 Insurance
- 536K On-Premises Infrastructure
- 138.2K Analytics Software
- 38.6K Application Development Software
- 5.7K Cloud Platform
- 109.4K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.2K Integration
- 41.5K Security Software
Change lenght semantics default from BYTE to CHAR

Many people stop using unicode character set due to problems related to column size because length semantics was not defined as char when creating the table.
The problem would be avoided if the pattern was char instead of byte and unicode would be more used.
Comments
-
You can change the default using nls_length_semantics initialisation parameter (other than for SYS). The limit for standard or extended lengths will remain defined in bytes though.
-
You can change the default using nls_length_semantics initialisation parameter (other than for SYS). The limit for standard or extended lengths will remain defined in bytes though.
Hi Simon, thanks for the tip, but I already thought about changing the initialization parameter but I was discouraged by Oracle's own docs:
"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 run-time errors, including buffer overflows."
https://docs.oracle.com/database/121/REFRN/GUID-221B0A5E-A17A-4CBC-8309-3A79508466F9.htm#REFRN10124
-
Hi Simon, thanks for the tip, but I already thought about changing the initialization parameter but I was discouraged by Oracle's own docs:
"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 run-time errors, including buffer overflows."
https://docs.oracle.com/database/121/REFRN/GUID-221B0A5E-A17A-4CBC-8309-3A79508466F9.htm#REFRN10124
Yes that is good advice and I would not change the default on an existing system. Watch out for materialized views behaviour as well. If it's a new system then starting with a changed default should not be a problem. We have a development standard to specify char explicitly as it leaves no room for error.
Simon
-
Yes that is good advice and I would not change the default on an existing system. Watch out for materialized views behaviour as well. If it's a new system then starting with a changed default should not be a problem. We have a development standard to specify char explicitly as it leaves no room for error.
Simon
If this were the default behavior you would not need to specify char explicitly, In many cases DDLs are generated by third-party tools that do not always allow this type of customization, so I think it would be interesting to change the default to char.
-
When you define the size of a field you are not thinking about how many bytes that field can occupy, but what makes sense for your respective business rules, so it makes more sense to me that the default is char, if for some reason you want to specify in bytes you can do it explicitly.