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
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?