Setting NLS_LENGTH_SEMANTICS for session via trigger I have the following problem with my Oracle 12c database.

I have to install a third party application, which needs an Oracle database instance. The supplier demands to set the NLS_LENGTH_SEMANTICS parameter to CHAR. However, Oracle strongly recommends not to change it globally from BYTE to CHAR.

My solution was to create a trigger, that changes the parameter for the session. The trigger looks like follows:

create or replace TRIGGER SYS.ALTER_NLS_FOR_SAPIENT
AFTER LOGON on DATABASE
BEGIN
    IF ( user like 'SAPIENT%' ) THEN
        execute immediate 'ALTER SESSION SET NLS_LENGTH_SEMANTICS="CHAR"';
    END IF;
END;


Unfortunately, the trigger does not change the session parameter. I suspect the trigger doesn't fire up, so I ensured it by writing a file onto disk. It's been definitely triggered.

If I change the session parameter on e.g. SQL developer from client side, the parameter is set.

ALTER SESSION SET NLS_LENGTH_SEMANTICS="CHAR";
select * from NLS_SESSION_PARAMETERS where parameter='NLS_LENGTH_SEMANTICS';




Does anyone know why it's not possible to set the NLS_LENGTH_SEMANTICS parameter from server side?

System information
It is possible.

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 29 08:29:35 2019

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

Connected to an idle instance.

SQL&gt; startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             343933032 bytes
Database Buffers          721420288 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
SQL&gt; create user sapient identified by sapient;

User created.

SQL&gt; grant create session to sapient;

Grant succeeded.

SQL&gt; show parameter seman

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

SQL&gt; create or replace TRIGGER SYS.ALTER_NLS_FOR_SAPIENT
AFTER LOGON on DATABASE
BEGIN
    IF ( user like 'SAPIENT%' ) THEN
        execute immediate 'ALTER SESSION SET NLS_LENGTH_SEMANTICS="CHAR"';
    END IF;
END;  2    3    4    5    6    7
  8  /

Trigger created.

SQL&gt; grant select_catalog_role to sapient;

Grant succeeded.
SQL&gt; connect sapient/sapient
Connected.
SQL&gt; show parameter seman

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics                 string      CHAR
SQL&gt;