Today I had to suffer on a real APEX 5 bug.
When you upload a file the NLS settings in your current APEX session will be set to AMERICAN.
I check for issues like that and found a forum post where someone else was experiencing the same problems as I did:
File browse changes some NLS settings - possible bug
My purpose was to upload a CSV file and transforming it to table data. Now I couldn't handle NUMBER values anymore. I always experienced the following error:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
Info: In Germany we handle the dot and comma logic the opposite around.
The workaround was to create an application process which was executed
"On Submit: After Page Submission":
Code:
To be able to find out which NLS settings was set. I just added a simple process and a textarea item on my file upload page:
When you upload a file the NLS settings in your current APEX session will be set to AMERICAN.
I check for issues like that and found a forum post where someone else was experiencing the same problems as I did:
File browse changes some NLS settings - possible bug
My purpose was to upload a CSV file and transforming it to table data. Now I couldn't handle NUMBER values anymore. I always experienced the following error:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
Info: In Germany we handle the dot and comma logic the opposite around.
The workaround was to create an application process which was executed
"On Submit: After Page Submission":
Code:
EXECUTE IMMEDIATE 'alter session set nls_language=GERMAN';
EXECUTE IMMEDIATE 'ALTER SESSION SET nls_numeric_characters='',.''';
APEX_UTIL.SET_SESSION_LANG('de');
To be able to find out which NLS settings was set. I just added a simple process and a textarea item on my file upload page:
-- ITEM: P1_TEXT_AREA -- Process code: select listagg(parameter || ':'|| value, CHR(10)) WITHIN GROUP (ORDER BY parameter) into :P1_TEXT_AREA from nls_session_parameters; -- Result including a file during the submit NLS_CALENDAR:GREGORIAN NLS_COMP:BINARY NLS_CURRENCY:$ NLS_DATE_FORMAT:DD.MM.YYYY NLS_DATE_LANGUAGE:AMERICAN NLS_DUAL_CURRENCY:$ NLS_ISO_CURRENCY:AMERICA NLS_LANGUAGE:AMERICAN NLS_LENGTH_SEMANTICS:BYTE NLS_NCHAR_CONV_EXCP:FALSE NLS_NUMERIC_CHARACTERS:., NLS_SORT:BINARY NLS_TERRITORY:AMERICA NLS_TIMESTAMP_FORMAT:DD-MON-RR HH.MI.SSXFF AM NLS_TIMESTAMP_TZ_FORMAT:DD-MON-RR HH.MI.SSXFF AM TZR NLS_TIME_FORMAT:HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT:HH.MI.SSXFF AM TZR -- Result including NO file during the submit: NLS_CALENDAR:GREGORIAN NLS_COMP:BINARY NLS_CURRENCY:€ NLS_DATE_FORMAT:DD.MM.YYYY NLS_DATE_LANGUAGE:GERMAN NLS_DUAL_CURRENCY:€ NLS_ISO_CURRENCY:GERMANY NLS_LANGUAGE:GERMAN NLS_LENGTH_SEMANTICS:BYTE NLS_NCHAR_CONV_EXCP:FALSE NLS_NUMERIC_CHARACTERS:,. NLS_SORT:GERMAN NLS_TERRITORY:GERMANY NLS_TIMESTAMP_FORMAT:DD.MM.RR HH24:MI:SSXFF NLS_TIMESTAMP_TZ_FORMAT:DD.MM.RR HH24:MI:SSXFF TZR NLS_TIME_FORMAT:HH24:MI:SSXFF NLS_TIME_TZ_FORMAT:HH24:MI:SSXFF TZR
