APEX 5 - File Upload Bug

Von Tobias Arnhold 2.18.2016
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:
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

No Comment to " APEX 5 - File Upload Bug "