2 Replies Latest reply: Apr 9, 2013 3:44 PM by Sergiusz Wolicki-Oracle RSS

    How to get Arabic dates in SQL*Plus?

    user12240205
      I want to do a very simple thing.

      I want to type in Arabic and Display dates in Arabic.

      Instead i get ????? ??????? ????? ??????? ? ? ???????, ????? for this?

      Why is this?

      I then changed my Windows XP "Regional and Language Options" all to Arabic. Now, I get AR (Arabic) in my language selection bar at the bottom of the Desktop. When I choose AR I can type Arabic in Notepad. ُ
      Even HERE I can type in Arabic: فغحث غخعق ةثسسشلث اثقث

      But in Sql*PLus I get ????? ??????? ????? ??????? ? ? ???????, ?????

      If I choose AR in SQL Plus and type in Arabic I get garbage characters?

      How to get Arabic in Sql*Plus?
      SQL> SHOW USER
      USER is "ARABDTEST"
      SQL> SELECT * FROM v$version;
      
      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
      PL/SQL Release 11.1.0.6.0 - Production
      CORE    11.1.0.6.0      Production
      TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
      NLSRTL Version 11.1.0.6.0 - Production
      
      SQL> 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
      
      17 rows selected.
      
      SQL> ALTER SESSION set NLS_DATE_LANGUAGE = 'ARABIC';
      
      Session altered.
      
      SQL> SELECT TO_CHAR(SYSDATE, 'RRRR/MM/DD YEAR MONTH DAY MON DY AM PM DL') FROM dual;
      
      TO_CHAR(SYSDATE,'RRRR/MM/DDYEARMONTHDAYMONDYAMPMDL')
      ----------------------------------------------------------------------------------------------------
      2013/04/08 TWENTY THIRTEEN ?????  ???????  ?????  ???????  ? ? ???????, ????? 08, 2013
      
      SQL> ALTER SESSION set NLS_DATE_LANGUAGE = 'AMERICAN';
      
      Session altered.
      
      SQL>  SELECT TO_CHAR(SYSDATE, 'RRRR/MM/DD YEAR MONTH DAY MON DY AM PM DL') FROM dual;
      
      TO_CHAR(SYSDATE,'RRRR/MM/DDYEARMONTHDAYMONDYAMPMDL')
      ----------------------------------------------------------------------------------------------------
      2013/04/08 TWENTY THIRTEEN APRIL     MONDAY    APR MON PM PM Monday, April 08, 2013
      
      SQL> SELECT * FROM nls_database_parameters;
      
      PARAMETER                      VALUE
      ------------------------------ ----------------------------------------
      NLS_LANGUAGE                   AMERICAN
      NLS_TERRITORY                  AMERICA
      NLS_CURRENCY                   $
      NLS_ISO_CURRENCY               AMERICA
      NLS_NUMERIC_CHARACTERS         .,
      NLS_CHARACTERSET               WE8MSWIN1252
      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
      NLS_NCHAR_CHARACTERSET         AL16UTF16
      NLS_RDBMS_VERSION              11.1.0.6.0
      
      20 rows selected.
        • 1. Re: How to get Arabic dates in SQL*Plus?
          P.Forstmann
          I am not sure SQL*Plus in character mode can display Arabic characters on Windows. OTN NLS_LANG FAQ http://www.oracle.com/technetwork/products/globalization/nls-lang-099431.html#_Toc105389288 says:

          >
          if you are testing with "special" characters please DO use the GUI and not the "DOS box" sqlplus.exe !
          >

          SQL*Plus GUI is not available with 11G: you should use SQL Developer.
          • 2. Re: How to get Arabic dates in SQL*Plus?
            Sergiusz Wolicki-Oracle
            NLS_CHARACTERSET               WE8MSWIN1252
            No Arabic is supported with a Western European database character set.

            Moreover, console (Command Prompt) programs do not support Arabic in general (http://www.microsoft.com/middleeast/msdn/arabicsupp.aspx#12). As mentioned above, try SQL Developer.


            -- Sergiusz