1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

NLS setting in Oracle 10g database

Discussion in 'Server Administration and Options' started by lucky, Aug 19, 2009.

  1. lucky

    lucky Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Hi All,

    I want to use Farsi language, while creating database i have selected the charterset as UTF8.
    Now am able to retrieve data in Farsi language. And i also want to use Arabic calendar instead of GREGORIAN,
    for that i have used the following settings

    alter session set nls_calendar = 'arabic hijrah';
    select to_char(sysdate,'day dd month yyyy','nls_calendar=''arabic hijrah''') from dual ;

    TO_CHAR(SYSDATE,'DAYDDMONTHYYY
    -------------------------------
    wednesday 27 ¿¿¿¿¿

    but am getting the out put like this?

    is the procedure what i used correct? if its wrong, pl guide me.

    Thanks in Advance,
    Lucky.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Check the value for NLS_DATE_LANGUAGE as reported by V$NLS_PARAMETERS; it may need to be changed.
     
  3. lucky

    lucky Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Hello,

    thank you for the quick response.

    NLS_DATE_LANGUAGE = AMERICAN,

    but now i have changed it to ARABIC,
    but still am getting the out put like :

    select to_char(sysdate,'day dd month yyyy','nls_calendar=''arabic hijrah''') from dual ;

    TO_CHAR(SYSDATE,'DAYDDMONTHYYY
    ------------------------------
    ¿¿¿¿¿¿ 28 ¿¿¿¿¿

    can u pl suggest me what can i do now?

    Thanks in Advance,
    Lucky
     
  4. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Arabic Hijrah Calendar - FAQ

    CONTENTS

    1.How to set Calendar to "Arabic Hijrah" ?
    2.How to display both Hijrah and Gregorian dates in the same select statement?
    3.What is Calendar Deviation?
    4.How to set deviation?
    5. Adding ONE day in the lxecal.nlt gives TWO days added. How to workaround?

    Questions & Answers

    1.How to set Calendar to "Arabic Hijrah" ?

    Answer

    Set NLS_CALENDAR parameter in one of the following ways:

    1. In the Current session :
    SQL> alter session set NLS_CALENDAR="Arabic Hijrah" ;

    2. All applications for a specific client:
    Set NLS_CALENDAR Variable in client OS environment.
    <e.g: On NT: set variable in the registry
    On Unix: set UNIX environment variable in .profile/.cshrc>

    3. Within SQL function:
    SQL> select to_char(sysdate,'day dd month yyyy','nls_calendar=''arabic hijrah''')
    from dual ;

    References

    Note 30772.1 Init.ora Parameter "NLS_CALENDAR" Reference Note

    2.How to display both Hijrah and Gregorian dates in the same select statement?

    Answer
    select
    to_char(sysdate,'day dd month yyyy','nls_calendar=''Arabic Hijrah'''),
    to_char(sysdate,'day dd month yyyy','nls_calendar=''English Hijrah'''),
    to_char(sysdate,'day dd month yyyy','nls_calendar=''gregorian''')
    from dual ;

    to make things a bit more clear:

    alter session set nls_calendar = 'Arabic Hijrah';
    gives the names of the Hijrah months in Arabic
    so you client needs to be configured to display Arabic. -> Note 179133.1

    alter session set nls_calendar = 'English Hijrah';
    gives the names of the Hijrah months in english.

    alter session set nls_calendar = 'gregorian';
    gives the names of the Gregorian months (jan-feb-etc).

    If you want the names of the Gregorian months in Arabic then please
    see Note 264280.1 Incorrect NLS_DATE_LANGUAGE for Arabian Countries

    References

    Bug 1302683

    3.What is Calendar Deviation?

    Answer
    Since Hijrah month is based on the moon complete revolution around the sun, Hijrah
    Calendar has different number of days in a year than Gregorian; therefore some manual
    deviation adjustement might be needed for the conversion to/from Gregorian. In other
    words HIJRAH calendar is Lunar based, corrections need to be made every so often.

    Note 130551.1 Conversion Algorithm of Gregorian Date to Arabic (Hijra) Date

    4.How to set deviation?

    Answer
    1. Shutdown the database

    2. Edit/create lxecal.nlt (lxecal.dat in Oracle7) in
    for Oracle 8 up to 9i $ORACLE_HOME/ocommon/nls
    for Oracle 10g and up $ORACLE_HOME/nls

    In 8i and 9i you a example file in the 'demo' directory $ORACLE_HOME/ocommon/nls/demo
    In 10g this demo file is not shipped, you can simply create on or copy the demo file from
    a 9i or 8i installation.

    This example makes the known adjustments needed between Gregorian
    years 2000 and 2004:

    DEFINE calendar

    calendar_name = "Arabic Hijrah"

    deviation_data = {
    <">:1
    <">:1
    <">:11
    <">:11
    <">:1
    <">:11
    <">:1
    <">:11
    <">:1
    <">:1
    <">:11
    <">:11
    <">:1
    <">:11
    <">:1
    <">:11
    <">:1
    <">:11
    <">:1
    <">:11
    <">:1
    <">:11
    <">:1
    <">:11
    <">:1
    <">:11
    <">:1
    <">:11
    }
    ENDDEFINE calendar_deviation

    ENDDEFINE calendar

    Notes: * Gregorian date specified in the deviation should correspond to one of
    29th, 30th or 31th days of the hijrah calendar.
    * Numbers greater than 10 are used to subtract days, e.g:
    11 --> subtract one day, 12 --> subtract two days ...etc

    3. Run the NLS Calendar Utility lxegen to register your modification to Oracle calendar
    system, this utility generates lxecalah.nlb file in the same directory mentioned above.

    4. startup the database.


    If you want to substract dates, please also see to
    Bug 2215017 SUBTRACTING DAYS FROM ARABIC HIJRA DATE DOES NOT WORK

    For 9i this bug is logged:
    Bug 2749165 NOT ABLE TO ADJUST THE HIJRAH CALENDAR ANYMORE IN 9.X

    You need Patch 9204 or higher to be able to adjust the calendar.

    There are also these backports:

    windows NT/2000 9.2.0.3 Patch2. / patch set nr 2973634
    HP-UX 64 bit 9.2.0.3 / Patch nr 2749165

    This is also fixed in 10g and up

    5. Adding ONE day in the lxecal.nlt gives TWO days added. How to workaround?

    Answer

    SQL> alter session set nls_calendar = 'Gregorian';

    SQL> select d from d;

    D
    07/06/05
    08/06/05

    SQL> alter session set nls_calendar = 'English Hijrah';

    SQL> select d from d;

    D
    29 Rabi' Thani 1426
    01 Jamada El Oula 1426

    07/06/05 needs to be 30 Rabi' Thani 1426
    so you add this to the lxecal.nlt

    DEFINE calendar

    calendar_name = "English Hijrah"

    DEFINE calendar_deviation
    deviation_data = {
    <">:1
    }
    ENDDEFINE calendar_deviation

    ENDDEFINE calendar

    and run lxegen

    SQL> select d from d;

    D
    01 Jamada El Oula 1426
    02 Jamada El Oula 1426

    * the date is now 2 days further!!! this is due rounding problems in the algorithm
    used for the calendar conversion a workaround is to substract FIRST one day in lxecal.nlt

    DEFINE calendar

    calendar_name = "English Hijrah"

    DEFINE calendar_deviation
    deviation_data = {
    <">:11
    <">:1
    }
    ENDDEFINE calendar_deviation

    ENDDEFINE calendar

    and run lxegen, this gives:

    SQL> select d from d;

    D
    30 Rabi' Thani 1426
    01 Jamada El Oula 1426

    make sure that you substract first (= ":11") and then add (= ":1")
    in the lxecal.nlt for the date

    References

    Oracle8i National Language Support Guide

    these 2 bugs, both closed as "not a bug":
    Bug 583924 NLS:HIJRAH CALENDAR DEVIATION DOES NOT WORK PROPERLY
    Bug 2215017 SUBTRACTING DAYS FROM ARABIC HIJRA DATE DOES NOT WORK

    Bug 2964132 TO_DATE GIVES ORA-01841 WITH HIJRAH CALENDAR
    will be fixed in 10gR2

    Note 130551.1 Conversion Algorithm of Gregorian Date to Arabic (Hijra) Date
    Note 264280.1 Incorrect NLS_DATE_LANGUAGE for Arabian Countries

    http://www.math.nus.edu.sg/aslaksen/calendar/islamic.html

    The Umm al-Qura Calendar of Saudi Arabia
    http://www.phys.uu.nl/~vgent/islam/mecca/ummalqura.htm

    Note 241047.1 The Priority of NLS Parameters Explained.
    Note 158577.1 NLS_LANG Explained (How does Client-Server Character Conversion Work?)
    Note 179133.1 The correct NLS_LANG in a Windows Environment

    For further NLS / Globalization information you may start here:
    Note 267942.1 Globalization Technology (NLS) Knowledge Browser