Home » SQL & PL/SQL » SQL & PL/SQL » Persian datepicker
Persian datepicker [message #322638] Sun, 25 May 2008 10:47 Go to next message
sara110
Messages: 17
Registered: October 2007
Junior Member
Hi all,

I am working on Persian Calendar for Oracle Form. May I have the correct NLS setup in order for me to display a Persian Calendar. These are the current setup that I set on my environment :

NLS_LANG=ARABIC_SAUDI ARABIA.UTF8
NLS_CALENDAR=PERSIAN

With these setup, I manage to display correct month name and year on calendar. Unfortunately, the day name is incorrect as it show Hijr day name as below:
al-jum'ah (friday),
as-sabt(saturday),
al-ahad(sunday),
al-ithniyn(monday),
ath-thulatha(tuesday),
al-arba'aa(wednesday),
al-khamees(thursday).

How do i can get it to display Persian day name on the calendar like below:
jomeh(friday),
shanbe(saturday),
yekshanbe(sunday),
doshanbe(monday),
seshanbe(tuesday),
chaharshanbe(wednesday),
panjshanbe(thursday).

The reason why I choose ARABIC language and UTF8 characterset is because Oracle do not yet have the PERSIAN language value supported so far. Is it true?

All kind of help are highly appreciated ...

Thank you.
Re: Persian datepicker [message #322642 is a reply to message #322638] Sun, 25 May 2008 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
The names come from your language (arabic), I don't know if it exists a language for farsi/persian that you can set your NLS_DATE_LANGUAGE parameter. Have a look at valid values using:
select value from v$NLS_VALID_VALUES where parameter='LANGUAGE' order by 1;

Otherwise have at the following site, maybe it can help you (I'm unable to read it):
http://www.parspardaz.com/oracle/

Regards
Michel
Re: Persian datepicker [message #322673 is a reply to message #322638] Sun, 25 May 2008 21:59 Go to previous messageGo to next message
sara110
Messages: 17
Registered: October 2007
Junior Member
Dear Michel

Unfortunately oracle doesn’t support Persian.
In this case, can I conclude priority is with NLS_LANG not NLS_CALENDAR?

How about using locale builder?

Thanks in advance
Re: Persian datepicker [message #322710 is a reply to message #322673] Mon, 26 May 2008 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
There are 2 things here.
Calendar is not determined by NLS_LANG, it is hard-coded to Gregorian if you don't set NLS_CALENDAR.
Calendar means how years, months and days are computed, it does not say anything about their names which are dependent on NLS_DATE_LANGUAGE (NLS_LANGUAGE or NLS_LANG, if you don't set the previous it).
For instance:
SQL> select to_char(sysdate+level-1,'Day','nls_date_language=english') english,
  2          to_char(sysdate+level-1,'Day','nls_date_language=french') french,
  3          to_char(sysdate+level-1,'Day','nls_date_language=italian') italian,
  4          to_char(sysdate+level-1,'Day','nls_date_language=spanish') spanish,
  5          to_char(sysdate+level-1,'Day','nls_date_language=german') german,
  6          to_char(sysdate+level-1,'Day','nls_date_language=portuguese') portuguese
  7  from dual
  8  connect by level <= 7
  9  /
ENGLISH   FRENCH   ITALIAN   SPANISH   GERMAN     PORTUGUESE
--------- -------- --------- --------- ---------- -------------
Monday    Lundi    Lunedì    Lunes     Montag     Segunda-Feira
Tuesday   Mardi    Martedì   Martes    Dienstag   Terça-Feira
Wednesday Mercredi Mercoledì Miércoles Mittwoch   Quarta-Feira
Thursday  Jeudi    Giovedì   Jueves    Donnerstag Quinta-Feira
Friday    Vendredi Venerdì   Viernes   Freitag    Sexta-Feira
Saturday  Samedi   Sabato    Sábado    Samstag    Sábado
Sunday    Dimanche Domenica  Domingo   Sonntag    Domingo

7 rows selected.

SQL> select to_char(add_months(sysdate,level-1),'Month','nls_date_language=english') english,
  2          to_char(add_months(sysdate,level-1),'Month','nls_date_language=french') french,
  3          to_char(add_months(sysdate,level-1),'Month','nls_date_language=italian') italian,
  4          to_char(add_months(sysdate,level-1),'Month','nls_date_language=spanish') spanish,
  5          to_char(add_months(sysdate,level-1),'Month','nls_date_language=german') german,
  6          to_char(add_months(sysdate,level-1),'Month','nls_date_language=portuguese') portuguese
  7  from dual
  8  connect by level <= 12
  9  /
ENGLISH   FRENCH    ITALIAN   SPANISH    GERMAN    PORTUGUES
--------- --------- --------- ---------- --------- ---------
May       Mai       Maggio    Mayo       Mai       Maio
June      Juin      Giugno    Junio      Juni      Junho
July      Juillet   Luglio    Julio      Juli      Julho
August    Août      Agosto    Agosto     August    Agosto
September Septembre Settembre Septiembre September Setembro
October   Octobre   Ottobre   Octubre    Oktober   Outubro
November  Novembre  Novembre  Noviembre  November  Novembro
December  Décembre  Dicembre  Diciembre  Dezember  Dezembro
January   Janvier   Gennaio   Enero      Januar    Janeiro
February  Février   Febbraio  Febrero    Februar   Fevereiro
March     Mars      Marzo     Marzo      März      Março
April     Avril     Aprile    Abril      April     Abril

12 rows selected.

But if different calendar months are supported, it seems this is not the case for week day and you always have Gregorian ones:
SQL> alter session set nls_calendar=persian;

Session altered.

SQL> select to_char(sysdate+level-1,'Day','nls_date_language=english') english,
  2          to_char(sysdate+level-1,'Day','nls_date_language=french') french,
  3          to_char(sysdate+level-1,'Day','nls_date_language=italian') italian,
  4          to_char(sysdate+level-1,'Day','nls_date_language=spanish') spanish,
  5          to_char(sysdate+level-1,'Day','nls_date_language=german') german,
  6          to_char(sysdate+level-1,'Day','nls_date_language=portuguese') portuguese
  7  from dual
  8  connect by level <= 7
  9  /
ENGLISH   FRENCH   ITALIAN   SPANISH   GERMAN     PORTUGUESE
--------- -------- --------- --------- ---------- -------------
Monday    Lundi    Lunedì    Lunes     Montag     Segunda-Feira
Tuesday   Mardi    Martedì   Martes    Dienstag   Terça-Feira
Wednesday Mercredi Mercoledì Miércoles Mittwoch   Quarta-Feira
Thursday  Jeudi    Giovedì   Jueves    Donnerstag Quinta-Feira
Friday    Vendredi Venerdì   Viernes   Freitag    Sexta-Feira
Saturday  Samedi   Sabato    Sábado    Samstag    Sábado
Sunday    Dimanche Domenica  Domingo   Sonntag    Domingo

7 rows selected.

SQL> select to_char(add_months(sysdate,level-1),'Month','nls_date_language=english') english,
  2          to_char(add_months(sysdate,level-1),'Month','nls_date_language=french') french,
  3          to_char(add_months(sysdate,level-1),'Month','nls_date_language=italian') italian,
  4          to_char(add_months(sysdate,level-1),'Month','nls_date_language=spanish') spanish,
  5          to_char(add_months(sysdate,level-1),'Month','nls_date_language=german') german,
  6          to_char(add_months(sysdate,level-1),'Month','nls_date_language=portuguese') portuguese
  7  from dual
  8  connect by level <= 12
  9  /
ENGLISH    FRENCH     ITALIAN    SPANISH    GERMAN     PORTUGUESE
---------- ---------- ---------- ---------- ---------- ----------
Khordad    Khordad    Khordad    Khordad    Khordad    Khordad
Te         Te         Te         Te         Te         Te
Amordad    Amordad    Amordad    Amordad    Amordad    Amordad
Shahruoar  Shahruoar  Shahruoar  Shahruoar  Shahruoar  Shahruoar
Mehr       Mehr       Mehr       Mehr       Mehr       Mehr
Aban       Aban       Aban       Aban       Aban       Aban
Athar      Athar      Athar      Athar      Athar      Athar
Dei        Dei        Dei        Dei        Dei        Dei
Bahman     Bahman     Bahman     Bahman     Bahman     Bahman
Esfand     Esfand     Esfand     Esfand     Esfand     Esfand
Farwarden  Farwarden  Farwarden  Farwarden  Farwarden  Farwarden
Aordebhsht Aordebhsht Aordebhsht Aordebhsht Aordebhsht Aordebhsht

12 rows selected.

SQL> alter session set nls_calendar='Japanese Imperial';

Session altered.

SQL> select to_char(sysdate+level-1,'Day','nls_date_language=english') english,
  2          to_char(sysdate+level-1,'Day','nls_date_language=french') french,
  3          to_char(sysdate+level-1,'Day','nls_date_language=italian') italian,
  4          to_char(sysdate+level-1,'Day','nls_date_language=spanish') spanish,
  5          to_char(sysdate+level-1,'Day','nls_date_language=german') german,
  6          to_char(sysdate+level-1,'Day','nls_date_language=portuguese') portuguese
  7  from dual
  8  connect by level <= 7
  9  /
ENGLISH   FRENCH   ITALIAN   SPANISH   GERMAN     PORTUGUESE
--------- -------- --------- --------- ---------- -------------
Monday    Lundi    Lunedì    Lunes     Montag     Segunda-Feira
Tuesday   Mardi    Martedì   Martes    Dienstag   Terça-Feira
Wednesday Mercredi Mercoledì Miércoles Mittwoch   Quarta-Feira
Thursday  Jeudi    Giovedì   Jueves    Donnerstag Quinta-Feira
Friday    Vendredi Venerdì   Viernes   Freitag    Sexta-Feira
Saturday  Samedi   Sabato    Sábado    Samstag    Sábado
Sunday    Dimanche Domenica  Domingo   Sonntag    Domingo

7 rows selected.

Now, what you can do is to built you own function for this:
SQL> create or replace package p as
  2    function myday (d integer) return varchar2;
  3  end p;
  4  /

Package created.

SQL> create or replace package body p as
  2    type tab is varray(7) of varchar2(30);
  3    days constant tab := tab ('shanbe', 'yekshanbe', 'doshanbe',
  4                              'seshanbe', 'chaharshanbe', 'panjshanbe', 'jomeh');
  5    function myday (d integer) return varchar2 is
  6    begin return days(d);
  7    end myday;
  8  end p;
  9  /

Package body created.

SQL> col persian format a15
SQL> select to_char(sysdate+level-1,'Day') english, 
  2          p.myday(to_char(sysdate+level-1,'D')) persian
  3  from dual
  4  connect by level <= 7
  5  /
ENGLISH   PERSIAN
--------- ---------------
Monday    doshanbe
Tuesday   seshanbe
Wednesday chaharshanbe
Thursday  panjshanbe
Friday    jomeh
Saturday  shanbe
Sunday    yekshanbe

7 rows selected.

Regards
Michel

Re: Persian datepicker [message #325131 is a reply to message #322710] Thu, 05 June 2008 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Sara,

Do you have any feedback on this?
Was you able to solve your problem?

Regards
Michel

Re: Persian datepicker [message #325350 is a reply to message #325131] Thu, 05 June 2008 23:07 Go to previous messageGo to next message
sara110
Messages: 17
Registered: October 2007
Junior Member
Dear Michel,

Thank you so much for your wonderful and helpful hints.
You were absolutely rite ..Days of week are identified by NLS_LANG and not NLS_CALENDAR...
So we had to build a new language called Persian using Locale builder.
How can I call procedure p inside my form (called date picker)?

Thanks again
Regards,
Sara
Re: Persian datepicker [message #325360 is a reply to message #325350] Fri, 06 June 2008 00:32 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
It's a very interesting issue but I'm sorry I have no experience on this and never met someone who has already use it.
Once you will solve this, I think you will be the expert on this subject and hope you will come back to explain us.

Regards
Michel
Previous Topic: pls correct this and it is not completed successfully (merged)
Next Topic: Use of Decode in Materialized View
Goto Forum:
  


Current Time: Thu Feb 06 23:16:04 CST 2025