Number and date format : altering NLS_SESSION_PARAMETER does not work?

Question

Oracle 11.2.0.3.0, APEX 4.1.1.00.23.

We need to display numbers in our application with the format FM999999999990.000 and dates with the English format DD-MON-YYYY.

Even if the application language is going to change (french, spain, etc.), we always need this format for numbers (no space or comma for group separator, and a point for decimal separator, ie. -1254.010) and date (3 first letters from the English month name ie. 12-FEB-2012).

Here are globalization attributes we are using (Application Builder -> Application -> Edit Globalization Attributes) :

• Application Primary Language: French (France) (fr)
• Application Language Derived From: Session
• Application Date Format: DD-MON-YYYY

I cannot manage to make it work as expected... I still get numbers like -1254,01 and dates like 12-FÉVR.-2012 instead of -1254.010 and 12-FEB-2012. It seems APEX ignore any call to alter session...

I have tried to enter the following code in the "Initialization PL/SQL Code" attribute (Application Builder -> Application -> Edit Security Attributes) but without any success :

BEGIN
   EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS= ''.,'' ';
   EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_LANGUAGE = ''AMERICAN'' ';
   EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE = ''AMERICAN'' ';
END;

I have a report with the following query to see if parameters are changing :

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;

Result:

As you see ALTER SESSION calls don't change anything...

When I try ALTER SESSION calls in a "Before Header" application process, session seems to be altered (report show modified values), but I still get wrong date and number format in my reports and items... I have tried the "Format Mask" attribute of the "Number Field" items but it seems it does not change anything too...

The only way I can get it to work is to call ALTER SESSION in each PL/SQL function I call from APEX. And for reports I can use the "Number / Date Format" column attribute.

QUESTION: Is there any way I can alter number and date parameters for the session for the whole application ?

---

EDIT :

When I run the following Before Header process on each page :

BEGIN
   APEX_UTIL.SET_SESSION_LANG('fr');
   EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS= ''.,'' ';
   EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_LANGUAGE = ''AMERICAN'' ';
END;

the report shows that the session has been altered :

Then I have created a new test page with :

1. The above Before Header process.
2. A report to show NLS parameters values.
3. A report that show date and numbers from one of my tables.
4. A textfield items with a Source Type as "SQL Query" to get a date.
5. A Number field item with a Source Type as "SQL Query" to get a number.
6. A button bound with JQuery that calls an application process that calls a PL/SQL procedure that htp.prn() a date from a table, then fill my textfield item.
7. A button bound with JQuery that do an insert (using application process, etc.) of the two fields in a table.

Once the page is loaded, report shows that the session has been altered coorectly, and I got right number and date formats, in the items and in the report. The "insert" button do the insertion without any problem.

When I click the "getDate" button to get a date from the database using an ajax call, I get the date in the French format! And then the "insert" button fails (invalid date).

Do you have any idea on why getting values from JavaScript (making an ajax call to an On Demand Application Process that calls a PL/SQL function in a package) causes the problem ?

And it is still strange as in my other existing pages, I get wrong format in the report even if I use the same Before Header process. We have to look more into this with my collegue, maybe we have a "hidden" piece of code somewhere which breaks all.

I have looked at the debug message data for the page but nothing strange for me.

Answer 1

Are you working with this in an Apex application? If so, then part of the application creation process is specifying NLS related values. Apex will always override your settings with the application settings.

I always create applications with fixed NLS values so I am sure that dates and numbers will be shown correctly.

You can have Apex switch NLS settings (application preference, item preference). Those work just fine.

But you need to do it on the application level and not database level.

Answer 2

Each call from apex-client to server is the separate oracle session. It answers why you are getting wrong format by js callback.

Have you tried this?

The Application Express session language can be set via either the APEX_UTIL.SET_SESSION_LANG procedure or via the P_LANG parameter of the F procedure in the URL.

Also you can try put initialization code here: Security Attributes -> Database Session -> Initialization PL/SQL Code. But I'm not sure about callbacks.