Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 10 April 2012

NLS parameters Setting in Oracle

NLS parameters determine the locale-specific behavior on both the client and the server. NLS parameters can be specified in the following ways:
  1. As initialization parameters on the server
  2. As environment variables on the client
  3. With the ALTER SESSION statement
  4. In SQL functions
Default is the lowest Priority and setting by SQL is the highest priority among them. Higher priority settings override lower priority settings. 
Choosing a Locale with the NLS_LANG Environment Variable
The NLS_LANG parameter has three components: language, territory, and character set. NLS_LANG = language_territory.charset
NLS_LANG = AMERICAN_AMERICA.WE8MSWIN1252

Note that illogical combinations can be set but do not work properly.
The following examples show how date and number formats are affected by NLS_LANG.
ENAME                      HIREDATE            SAL
Clark                      09-DEC-88           4195.83
Miller                     23-MAR-92            4366.67
If NLS_LANG is set with the language as French, the territory as France, and the character set as Western European 8-bit ISO 8859-1, the same query returns:
SQL> SELECT ename, hiredate, ROUND(sal/12,2)  sal FROM emp;
ENAME                               HIREDATE           SAL
Clark                               09/12/88           4195,83
Miller                              23/03/92           4366,67
OCINlsGetInfo() Function
User applications can query client NLS settings with the OCINlsGetInfo() function.
NLS_LANGUAGE
The default value of NLS_LANGUAGE may be specific to the operating system.
SQL> ALTER SESSION SET NLS_LANGUAGE=German;
LAST_NAME                 HIRE_DATE       SALARY
Sciarra                   30-SEP-97      962.5
Urman                     07-MÄR-98       975
Popp                      07-DEZ-99      862.5
NLS_TERRITORY
ALTER SESSION SET NLS_TERRITORY = Germany;
ALTER SESSION SET NLS_LANGUAGE = German;
SQL> SELECT TO_CHAR(salary,'L99G999D99') salary FROM employees;

SALARY
€24.000,00
€17.000,00
Note that the currency symbol has changed from $ to €. The numbers have not changed because the underlying data is the same.
NLS_Date_Format
SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD.MON.YY';
SQL> Alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';
SQL> SELECT last_name, hire_date, ROUND(salary/8,2) salary FROM employees;

LAST_NAME         HIRE_DATE         SALARY
Sciarra           30.SEP.97         962.5
Urman             07.MÄR.98        975

Changing the Date Format from Command Prompt
C:\set nls_date_format=dd/mm/yyyy;
C:\sqlplus hr/hr;
SQL> Select sysdate from dual;
Changing Default Date Format in Oracle 9i
  1. Crate a pfile from spfile
SQL> Create Pfile=c:\Pfile.sql’ from spfile;
  1. Edit the pfile by adding: nls_date_format=’DD/MM/YYYY’;
  2. Startup database with this pfile as: Startup pfile=’c:\pfile.sql’;
  3. Query the system date you will see the change has made.
Select sysdate from dual;
  1. Create spfile from this pfile as: Create spfile from pfile=’c:\pfile.sql’;
Changing Default Date Format in Oracle 10g
It is really easy to change the default NLS_DATE_FORMAT setting in oracle 10g
SQL>ALTER SYSTEM SET NLS_DATE_FORMAT=‘YYYY-MM-DD’SCOPE=SPFILE;
System altered.
SELECT value From v$nls_parameters WHERE parameter =‘NLS_DATE_FORMAT’;
Note: if instance is started with the pfile then you have to create spfile and need to restart the database.
NLS_DATE_LANGUAGE
SQL>ALTER SESSION SET NLS_DATE_LANGUAGE = FRENCH;
SQL>SELECT TO_CHAR(SYSDATE, 'Day:Dd Month yyyy') FROM DUAL;
TO_CHAR(SYSDATE,'DAY:DDMONTHYYYY')
------------------------------------------------------------
Vendredi:07 Décembre  2001
When numbers are spelled in words using the TO_CHAR function, the English spelling is always used. For example, enter the following SELECT statement:
NLS_TIMESTAMP_FORMAT
NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF'
SQL> SELECT TO_TIMESTAMP('11-nov-2000 01:00:00.336', 'dd-mon-yyyy hh:mi:ss.ff')
           FROM DUAL;

TO_TIMESTAMP('11-NOV-200001:00:00.336','DD-MON-YYYYHH:MI:SS.FF')
---------------------------------------------------------------------------
2000-11-11 01:00:00.336000000
Setting NLS_TIMESTAMP_TZ_FORMAT
SQL>Alter Session set NLS_TIMESTAMP_TZ_FORMAT  = 'YYYY-MM-DD
HH:MI:SS.FF TZH:TZM'
SQL> SELECT TO_TIMESTAMP_TZ('2000-08-20, 05:00:00.55 America/Los_Angeles', 'yyyy-mm-dd hh:mi:ss.ff TZR') FROM DUAL;
TO_TIMESTAMP_TZ('2000-08-20,05:00:00.55AMERICA/LOS_ANGELES','YYYY-MM-DDHH:M
---------------------------------------------------------
2000-08-20 05:00:00.550000000 -07:00
Calendar Formats
Oracle supports six calendar systems in addition to Gregorian, the default:
NLS_CALENDAR='English Hijrah'
SQL> ALTER SESSION SET NLS_CALENDAR='English Hijrah';
SQL>SELECT SYSDATE FROM DUAL;
SYSDATE
24 Ramadan     1422
Setting NLS_NUMERIC_CHARACTERS
SQL>ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ",.";
SQL>SELECT TO_CHAR(4000, '9G999D99') FROM DUAL;
TO_CHAR(4
4.000,00
SQL> ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ",.";
NLS_CURRENCY
SQL> ALTER SESSION SET NLS_CURRENCY='KR';
SQL> SELECT TO_CHAR(order_total, 'L099G999D99') "total" FROM orders  WHERE order_id > 2450;

kr078,279.60
kr006,653.40
NLS_ISO_CURRENCY
SQL>ALTER SESSION SET NLS_ISO_CURRENCY = FRANCE;
SQL> SELECT TO_CHAR(order_total, 'C099G999D99') "TOTAL" FROM orders WHERE customer_id = 146;
TOTAL
------------------
EUR017,848.20
EUR027,455.30
EUR029,249.10
NLS_DUAL_CURRENCY
SQL> ALTER SESSION SET NLS_DUAL_CURRENCY=EURO
SQL>SELECT TO_CHAR(order_total, 'C099G999D99') "TOTAL" FROM orders  WHERE customer_id=116;
TOTAL
EUR006,394.80
EUR011,097.40
NLS_LANG must also be set on the client for NLS_CURRENCY or NLS_DUAL_CURRENCY to take effect.
NLS Data Dictionary Views
Data Dictionary
Views
DATABASE_PROPERTIES
V$NLS_VALID_VALUES
NLS_SESSION_PARAMETERS
V$TIMEZONE_NAMES
NLS_INSTANCE_PARAMETERS
V$NLS_PARAMETERS
NLS_DATABASE_PARAMETERS
SYS.PROPS$;

0 comments:

Post a Comment