nls_numeric_characters causing error in translated app APEX 4.0
847571Mar 15 2011 — edited Mar 18 2011Hi,
I currently have an app with the default language of en-gb, this has been translated to Dutch (nl) and published. Switching between the 2 is done via the Item Preference method.
The translation worked fine for the most part but I was getting errors when calling to_number (character to number conversion error) in packages, processes etc.. After a bit of digging I found that this was because of the nls_numeric_characters settings for en-gb is '.,' and nl is ',.'
After searching through the forums I found multiple threads, most saying to set the nls_numeric_characters in the VPD area of 'Edit Security Attributes', which I did:
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS=''.,''';
Thinking that this would change the nl numeric characters to the en-gb characters and all would be well....
Now, the interesting thing is that without adding this setting to the VPD the application (when set to Dutch) translates fine apart from the to_number error described above, but when I do add the setting to the VPD and try to view the Dutch version I get the following error:
ORA-20867: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "APEX_040000.WWV_FLOW";, line 2081
ORA-06512: at "APEX_040000.WWV_FLOW";, line 11641
ORA-06512: at "APEX_040000.F";, line 267
ORA-06512: at "APEX_040000.F";, line 294
ORA-06512: at line 30
DAD name: apex
PROCEDURE : f
URL : http://XDB HTTP Server:8080/apex/f?p=109:115:4219773893308802::NO:::
PARAMETERS :
===========
p:
109:115:4219773893308802::NO:::
ENVIRONMENT:
============
PLSQL_GATEWAY=WebDb
GATEWAY_IVERSION=2
SERVER_SOFTWARE=Oracle Embedded PL/SQL Gateway/10.2.0.1.0
GATEWAY_INTERFACE=CGI/1.1
SERVER_PORT=8080
SERVER_NAME=XDB HTTP Server
REQUEST_METHOD=GET
QUERY_STRING=p=109:115:4219773893308802::NO:::
PATH_INFO=/f
SCRIPT_NAME=/apex
REMOTE_HOST=
REMOTE_ADDR=127.0.0.1
SERVER_PROTOCOL=HTTP/1.1
REQUEST_PROTOCOL=tcp
..
..
..
When this error appears I have to press back in the browser to return to the application.
Any help would be much appreciated.
Many Thanks
Dave