Change lenght semantics default from BYTE to CHAR

Luís Gustavo Lira

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.

Simon Moore

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.

Luís Gustavo Lira

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

Simon Moore

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

Luís Gustavo Lira

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.

Luís Gustavo Lira

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.