Set NLS session parameter while making connection with Oracle

Set NLS session parameter while making connection with Oracle

You can change this NLS parameter at session level.
You can set the NLS parameter for specific user or module with help of trigger.
Following example show you to create trigger when ever connection is made then it automatically fire alter session command to SET NLS parameter for particular session at connectivity time whenever connection is established.

1. Check the list of NLS Session parameter. You can change any one while making new connection.

col value for a30
col parameter for a25
select * from NLS_SESSION_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_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_COMP                  BINARY
NLS_LENGTH_SEMANTICS      BYTE
NLS_NCHAR_CONV_EXCP       FALSE

2. Change NLS session setting if specific user is connected with help of trigger.

CREATE OR REPLACE TRIGGER sys.user_nls_session_settings AFTER LOGON ON DATABASE
DECLARE
V_SESSION_USER VARCHAR2(60);
BEGIN
SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') INTO V_SESSION_USER FROM DUAL;
IF UPPER(V_SESSION_USER) LIKE 'SCOTT'
THEN
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP=''BINARY''';
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT=''BINARY''';
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''MON-DD-RR''';
END;
END IF;
END;
/

3. Change NLS session parameter if specific module is connected.

CREATE OR REPLACE TRIGGER sys.module_nls_session_settings AFTER LOGON ON DATABASE
DECLARE
V_MODULE VARCHAR2(60);
BEGIN
SELECT SYS_CONTEXT ('USERENV', 'MODULE') INTO V_MODULE FROM DUAL;
IF UPPER(V_MODULE) LIKE 'SALES%'
THEN
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP=''BINARY''';
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT=''BINARY''';
END;
END IF;
END;
/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.