1 Reply Latest reply: Apr 9, 2013 6:21 AM by 650063 RSS

    somehow APEX changes the decimal separator to comma ","

    650063
      Hi,
      Below is section with "My system settings". I'm not sure if my settings are correct, or where the problem comes. The problem is, that somehow APEX changes the decimal separator to comma ",", which is wrong. I need APEX to understand that decimal symbol is dot ".", and in database i seems to have it as dot. I also need that APEX web site should stay in Estonian language only, and date format as "DD.MM.RRRR HH24:MI:SS".
      Where and what should i change to achieve that and to avoid that error:
      ORA-06502: PL/SQL: numeric or value error: character to number conversion error
      I get that error by to_number('20.2') because APEX thinks the dot is not a decimal separator symbol.


      ---
      My system settings:

      I have "Application Express 4.2.1.00.08".
      I have a region with type "Report" and the source of it is Sql with such value:
      SELECT
        a1.parameter as "Parameter",
        a1.value as "Database value",
        a2.value as "Instance value",
        a3.value as "Session value"
      FROM
        nls_database_parameters a1
        LEFT JOIN nls_instance_parameters a2 ON a1.parameter = a2.parameter
        LEFT JOIN nls_session_parameters a3 ON a1.parameter = a3.parameter
      ORDER BY
        a1.parameter asc;
      That outputs:
      Parameter     Database Value     Instance Value     Session Value
      NLS_CALENDAR     GREGORIAN     -      GREGORIAN
      NLS_CHARACTERSET     AL32UTF8     -      -
      NLS_COMP     BINARY     BINARY     BINARY
      NLS_CURRENCY     $     -      kr
      NLS_DATE_FORMAT     DD-MON-RR     -      DD.MM.RRRR
      NLS_DATE_LANGUAGE     AMERICAN     -      ESTONIAN
      NLS_DUAL_CURRENCY     $     -      kr
      NLS_ISO_CURRENCY     AMERICA     -      ESTONIA
      NLS_LANGUAGE     AMERICAN     AMERICAN     ESTONIAN
      NLS_LENGTH_SEMANTICS     BYTE     CHAR     CHAR
      NLS_NCHAR_CHARACTERSET     AL16UTF16     -      -
      NLS_NCHAR_CONV_EXCP     FALSE     FALSE     FALSE
      NLS_NUMERIC_CHARACTERS     .,     -      ,
      NLS_RDBMS_VERSION     11.2.0.3.0     -      -
      NLS_SORT     BINARY     -      ESTONIAN
      NLS_TERRITORY     AMERICA     AMERICA     ESTONIA
      NLS_TIME_FORMAT     HH.MI.SSXFF AM     -      HH24:MI:SSXFF
      NLS_TIMESTAMP_FORMAT     DD-MON-RR HH.MI.SSXFF AM     -      DD.MM.RRRR HH24:MI:SSXFF
      NLS_TIMESTAMP_TZ_FORMAT     DD-MON-RR HH.MI.SSXFF AM TZR     -      DD.MM.RRRR HH24:MI:SSXFF TZR
      NLS_TIME_TZ_FORMAT     HH.MI.SSXFF AM TZR     -      HH24:MI:SSXFF TZR
      And application properties page, tab "Globalization" has following settings:
      "Application Primary Language" = Estonian (et)
      "Application Language Derived From" = Application Primary Language
      Other parameters are empty.
      When i refresh my web page i see such Debug data below, where you see that NLS-settings are changed somehow magically and "NLS: Set Decimal separator" instruction changes it's value from dot to comma and finally my web page throws error when converting string to number by expecting the string decimal separator be comma but it's dot in my string, i need the dot to stay as a decimal separator:
      0.01213     0.00024     S H O W: application="100" page="8" workspace="" request="" session="30574863916278"     4     
      0
      0.01236     0.00086     Reset NLS settings     4     
      0
      0.01328     0.00060     alter session set NLS_LANGUAGE="AMERICAN"     4     
      0
      0.01383     0.00040     alter session set NLS_TERRITORY="AMERICA"     4     
      0
      0.01450     0.00080     alter session set NLS_CALENDAR="GREGORIAN"     4     
      0
      0.01502     0.00032     alter session set NLS_SORT="BINARY"     4     
      0
      0.01534     0.00029     alter session set NLS_COMP="BINARY"     4     
      0
      0.01563     0.00008     ...NLS: Set Decimal separator="."     4     
      0
      0.01571     0.00049     ...NLS: Set NLS Group separator=","     4     
      0
      0.01619     0.00029     ...NLS: Set g_nls_date_format="DD-MON-RR"     4     
      0
      0.01648     0.00022     ...NLS: Set g_nls_timestamp_format="DD-MON-RR HH.MI.SSXFF AM"     4     
      0
      0.01670     0.00145     ...NLS: Set g_nls_timestamp_tz_format="DD-MON-RR HH.MI.SSXFF AM TZR"     4     
      0
      0.01816     0.00601     ...Setting session time_zone to +03:00     4     
      1
      0.02416     0.00096     Language derived from: FLOW_PRIMARY_LANGUAGE, current browser language: et     4     
      0
      0.02512     0.00067     alter session set nls_language="ESTONIAN"     4     
      0
      0.02579     0.00008     alter session set nls_territory="ESTONIA"     4     
      0
      0.02586     0.00021     NLS: CSV charset=BLT8MSWIN1257     4     
      0
      0.02607     0.00005     ...NLS: Set Decimal separator=","     4     
      0
      0.02612     0.00022     ...NLS: Set NLS Group separator=" "     4     
      0
      0.02634     0.00040     ...NLS: Set g_nls_date_format="DD.MM.RRRR"     4     
      0
      0.02675     0.00020     ...NLS: Set g_nls_timestamp_format="DD.MM.RRRR HH24:MI:SSXFF"     4     
      0
      0.02694     0.00037     ...NLS: Set g_nls_timestamp_tz_format="DD.MM.RRRR HH24:MI:SSXFF TZR"     4     
      0
      0.02731     0.00214     NLS: Language=et     4     
      0
      0.02946     0.00498     Application 100, Authentication: PLUGIN, Page Template: 2615908910727936     4     
      1
      0.03443     0.00123     Authentication check: Application Express Authentication (NATIVE_APEX_ACCOUNTS)     4     
      0
      0.03566     0.00015     ...fetch session state from database     4     
      0
      0.03581     0.00185     fetch items (exact)     4
      .........
      .........
      0.65332     0.00035     report error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error     4
      I will get error with "to_number('20.2')". But i don't want to get error, i need the APEX to understand that dot is the decimal separator. Somehow APEX changes the decimal separator to comma, which is wrong.
        • 1. Re: somehow APEX changes the decimal separator to comma ","
          650063
          If i do so:

          On application properties page, tab "Globalization" settings:
          "Application Primary Language" = Estonian (et)
          "Application Language Derived From" = No NLS
          
          Application Date Format = DD.MM.RRRR
          Application Date Time Format = HH24:MI:SSXFF
          
          Other parameters are empty.
          Then " to_number('20.2')" won't give error message and APEX pages display date values as needed with pattern "DD.MM.RRRR". That solves my need.

          Seems i then answered/solved my question.