Changing Session NLS parameters with ON LOGON Trigger

Changing Session NLS parameters with ON LOGON Trigger

Sometimes there is a need to change the default Oracle Client NLS  Parameters.
Default NLS settings determine the NLS settings in Oracle Client session.

Here is a simple way of changing NLS parameters in Client session using ON LOGON Trigger.

Step 1 - Check current NLS settings:

COL PARAMETER FOR A30
COL VALUE FOR A30
SET PAGESIZE 200

SQL> SELECT * FROM  V$NLS_PARAMETERS;

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_LANGUAGE                   HEBREW
NLS_TERRITORY                  ISRAEL
NLS_CURRENCY                   ש"ח
NLS_ISO_CURRENCY               ISRAEL
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RRRR
NLS_DATE_LANGUAGE              HEBREW
NLS_CHARACTERSET               AL32UTF8
NLS_SORT                       HEBREW
NLS_TIME_FORMAT                HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT           DD-MON-RRRR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RRRR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY              ₪
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           CHAR
NLS_NCHAR_CONV_EXCP            FALSE

19 rows selected


Step 2 - Create the ON LOGON Trigger
CREATE OR REPLACE TRIGGER ALTER_NLS_ON_LOGON_TRIGGER
AFTER LOGON ON my_user.SCHEMA
BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE=''AMERICAN''';
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TERRITORY=''AMERICA''';
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_CURRENCY=''$''';
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_ISO_CURRENCY=''AMERICA''';
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''DD-MON-RR''';
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_LANGUAGE=''AMERICAN''';
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT=''BINARY''';
END;
/

Step 3. - Open a new sessions and validate changes
SQL> SELECT * FROM  V$NLS_PARAMETERS;

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_CHARACTERSET               AL32UTF8
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           CHAR
NLS_NCHAR_CONV_EXCP            FALSE

19 rows selected