How to set NLS_CHARACTERSET and NLS_LENGTH_SEMANTICS?

[sixth-instinct]

Hi Gerald,

This question refers to your Docker image for Oracle XE at https://hub.docker.com/r/gvenzl/oracle-xe.

How can I set the following parameters to my Oracle XE?

1. NLS_CHARACTERSET
2. NLS_LENGTH_SEMANTICS

I tried to set NLS_LENGTH_SEMANTICS in my docker-compose.yml as an environment variable, but it did not make any difference:

    environment:
        - ORACLE_PASSWORD=${ora_admin_password}
        - NLS_LENGTH_SEMANTICS=CHAR

NLS_LENGTH_SEMANTICS remained set to BYTE in the database. Perhaps the name of the environment variable is different from the actual parameter name? Is there a list of environment variables we can use?

I'm currently using image gvenzl/oracle-xe:21.3.0.

Thank you.

chris

[gvenzl]

Hi @sixth-instinct,

Both of these are database internal properties and not environment variables.

NLS_CHARACTERSET cannot be changed, it merely holds the value of the character set that the database has been created with (AL32UTF8).

NLS_LENGTH_SEMANTICS is a database parameter and can be set via the SQL prompt:

SQL*Plus: Release 21.0.0.0.0 - Production on Fri Sep 2 03:22:52 2022
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> show parameter nls_length

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics             string  BYTE

SQL> alter system set nls_length_semantics=CHAR;

System altered.

SQL> show parameter nls_length

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics             string  CHAR

[sixth-instinct]

Hi @gvenzl,

Thanks for your answer.

If the default setting is the following, I would not need to change it because this is exactly what our applications require:

 NLS_CHARACTERSET = AL32UTF8

Thanks for your explanation.

chris

[gvenzl]

Hi @sixth-instinct,

Yup, that's the one and also the recommended character set by Oracle since 11g R2.