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.