Setting NLS_LENGTH_SEMANTICS for session via trigger

Question

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';

NLS_LENGTH_SEMANTICS value

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

System information

system information

• Wernfried Domscheit: Why does the supplier demands to set the NLS_LENGTH_SEMANTICS parameter to CHAR? If they require it then they should call an ALTER SESSION SET NLS_LENGTH_SEMANTICS ... in their deployment scripts.

Answer 1

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> 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> create user sapient identified by sapient;
User created.
SQL> grant create session to sapient;
Grant succeeded.
SQL> show parameter seman
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics string BYTE
SQL> 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> grant select_catalog_role to sapient;
Grant succeeded.
SQL> connect sapient/sapient
Connected.
SQL> show parameter seman
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics string CHAR
SQL>

• Collin P: Thanks, got it now. It somehow didn't work with sqldeveloper, but sqlplus did the job.

Answer 2

Additionnal note since I didn't understand with it "didn't work" for me (but it was).

On SQL Developer, NLS PARAMETER are provided by the tool, so the trigger does not work from there.

You can change the NLS parameters from Tools->Preferences->Database->NLS

From the documentation :

The NLS Parameters pane specifies values for globalization support parameters, such as the language, territory, sort preference, and date format. These parameter values are used for SQL Developer session operations, such as for statements executed using the SQL Worksheet and for the National Language Support Parameters report.

Sql Developer Documentation