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