ArtenSUITE Part 8: NLS ...

ArtenSUITE
A Development Journal
Part 8: NLS ...

After installing Oracle it’s important to setup the variables NLS_LANG and NLS_DATE_FORMAT. These variables determine the default settings that Oracle uses for Date formatting, Currency formatting, Sort Orders etc. More information is available here.

If Oracle is installed on a Windows server the NLS_LANG can be as an environment variable using the System Control Panel. If you are in the UK the settings you will want are as follows:

Key: NLS_LANG
Value: ENGLISH_UNITED KINGDOM.WE8ISO8859P1

NLS_DATE_FORMAT has to be set on a session by session basis, so you can either put it in the startup procedure for your application as such:

alter session set NLS_DATE_FORMAT = ‘dd/mm/yyyy’;

or you can implement it as a database trigger:

CREATE OR REPLACE TRIGGER "DATA_LOGON_TRIGGER"
after logon
ON DATABASE
begin
execute immediate
'alter session set nls_date_format = ''ddmmyyyy'' ';
end;
/
ALTER TRIGGER "DATA_LOGON_TRIGGER" ENABLE
/

"The man who has nothing to boast of but his illustrious ancestry is like the potato - the best part under ground." - Thomas Overbury

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business