1

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

  • 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. – Wernfried Domscheit Dec 2 '19 at 20:06
2

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>
  • Thanks, got it now. It somehow didn't work with sqldeveloper, but sqlplus did the job. – Collin P. Nov 29 '19 at 10:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.