Forum Stats

  • 3,824,844 Users
  • 2,260,430 Discussions
  • 7,896,328 Comments

Discussions

Change lenght semantics default from BYTE to CHAR

Luís Gustavo Lira
Luís Gustavo Lira Member Posts: 119 Silver Badge
edited Jul 7, 2020 1:47PM in Database Ideas - Ideas

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.

Luís Gustavo LiraThorsten KettnerSven W.carajandb
5 votes

Active · Last Updated

Comments

  • Simon Moore
    Simon Moore Member Posts: 9 Blue Ribbon

    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
  • Luís Gustavo Lira
    Luís Gustavo Lira Member Posts: 119 Silver Badge

    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
    Simon Moore Member Posts: 9 Blue Ribbon

    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
    Luís Gustavo Lira Member Posts: 119 Silver Badge

    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
    Luís Gustavo Lira Member Posts: 119 Silver Badge

    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.

    Thorsten Kettner