Forum Stats

  • 3,758,591 Users
  • 2,251,416 Discussions
  • 7,870,257 Comments

Discussions

Is that possible to change DATABASE_VALUE=CHAR ?

M.Dilipkumar
M.Dilipkumar Member Posts: 2
edited Aug 29, 2018 9:52PM in Globalization Support

Anyone knows, how to change DATABASE_VALUE to CHAR? my database is running on 12cr2 version. it have mistakenly created to BYTE.

I've manage to change NLS_LENGTH_SEMANTICS to CHAR by altering it.

I know DATABASE_VALUE parameter is set when we create the database, but just curious to know is that possible to change it ? or do i need to drop database and recreate back with CHAR instead of BYTE.

Or this is fine, can i ignore DATABASE_VALUE and proceed with this value?

initial parameter:

PARAMETER

--------------------------------------------------------------------------------

DATABASE_VALUE

--------------------------------------------------------------------------------

SESSION_VALUE

--------------------------------------------------------------------------------

INSTANCE_VALUE

--------------------------------------------------------------------------------

NLS_LENGTH_SEMANTICS

BYTE

BYTE

BYTE

After try: alter system set nls_length_semantics='CHAR' scope=both;

PARAMETER

--------------------------------------------------------------------------------

DATABASE_VALUE

--------------------------------------------------------------------------------

SESSION_VALUE

--------------------------------------------------------------------------------

INSTANCE_VALUE

--------------------------------------------------------------------------------

NLS_LENGTH_SEMANTICS

BYTE

CHAR

CHAR

M.Dilipkumar

Best Answer

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,782 Bronze Crown
    edited Aug 18, 2018 12:10AM Accepted Answer

    Oracle strongly advises AGAINST what you are doing/trying to do:

    https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/NLS_LENGTH_SEMANTICS.html#GUID-221B0A5E-A17A-4CBC-…

    Note: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. 

    Set it at session level, or at worst, leave it set at instance level, at your own risk.

    M.Dilipkumar
  • Sergiusz Wolicki-Oracle
    Sergiusz Wolicki-Oracle Member Posts: 2,019 Employee
    edited Aug 29, 2018 7:59PM

    The database value of NLS_LENGTH_SEMANTICS is pretty irrelevant. I do not remember it being used for anything. Do not bother changing it. Also, a change is not possible once a database is created.

    As Gaz mentioned, we discourage setting it at instance level as well to avoid inadvertently running Oracle and third-party scripts with unexpected semantics. Ideally, you should add the CHAR keyword to VARCHAR2(n) and CHAR(n) declarations in your scripts to make them independent from the setting. Alternatively, add an ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR statement after each CONNECT in your scripts.

    Thanks,
    Sergiusz

    M.Dilipkumar
  • M.Dilipkumar
    M.Dilipkumar Member Posts: 2
    edited Aug 29, 2018 9:51PM
This discussion has been closed.