title: Number and date format : altering NLS_SESSION_PARAMETER does not work?
body: <p>Oracle 11.2.0.3.0, APEX 4.1.1.00.23.</p>
  
  <p>We need to display <strong>numbers</strong> in our application with the format <code>FM999999999990.000</code> and <strong>dates</strong> with the English format <code>DD-MON-YYYY</code>.</p>
  
  <p>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, <em>ie. -1254.010</em>) and date (3 first letters from the English month name <em>ie. 12-FEB-2012</em>).</p>
  
  <p>Here are <strong>globalization attributes</strong> we are using (<em>Application Builder -> Application -> Edit Globalization Attributes</em>) :</p>
  
  <ul>
  <li>Application Primary Language: <em>French (France) (fr)</em></li>
  <li>Application Language Derived From: <em>Session</em></li>
  <li>Application Date Format: <em>DD-MON-YYYY</em></li>
  </ul>
  
  <p>I cannot manage to make it work as expected...
  I still get numbers like <code>-1254,01</code> and dates like <code>12-FÉVR.-2012</code> instead of <code>-1254.010</code> and <code>12-FEB-2012</code>.
  It seems APEX ignore any call to alter session...</p>
  
  <p>I have tried to enter the following code in the "<strong>Initialization PL/SQL Code</strong>" attribute (<em>Application Builder -> Application -> Edit Security Attributes</em>) but without any success :</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>I have a report with the following query to see if parameters are changing :</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Result:</p>
  
  <p><img src="https://i.stack.imgur.com/N62M0.png" alt="report NLS parameters no changes"></p>
  
  <p>As you see <code>ALTER SESSION</code> calls don't change anything...</p>
  
  <p>When I try <code>ALTER SESSION</code> calls in a "<strong>Before Header</strong>" 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 "<strong>Format Mask</strong>" attribute of the "<strong>Number Field</strong>" items but it seems it does not change anything too...</p>
  
  <p>The only way I can get it to work is to call <code>ALTER SESSION</code> in each PL/SQL function I call from APEX. And for reports I can use the "Number / Date Format" column attribute.</p>
  
  <p><strong>QUESTION:</strong> Is there any way I can alter number and date parameters for the session for the whole application ?</p>
  
  <hr>
  
  <h2>EDIT :</h2>
  
  <p>When I run the following <strong>Before Header process</strong> on each page :</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>the report shows that the session has been altered :</p>
  
  <p><img src="https://i.stack.imgur.com/eV8XQ.png" alt="report NLS parameters"></p>
  
  <p>Then I have created a new test page with :</p>
  
  <ol>
  <li>The above Before Header process.</li>
  <li>A report to show NLS parameters values.</li>
  <li>A report that show date and numbers from one of my tables.</li>
  <li>A textfield items with a Source Type as "SQL Query" to get a date.</li>
  <li>A Number field item with a Source Type as "SQL Query" to get a number.</li>
  <li>A button bound with JQuery that calls an application process that calls a PL/SQL procedure that <code>htp.prn()</code> a date from a table, then fill my textfield item.</li>
  <li>A button bound with JQuery that do an insert (using application process, etc.) of the two fields in a table. </li>
  </ol>
  
  <p>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.</p>
  
  <p>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 (<code>invalid date</code>).</p>
  
  <p><strong>Do you have any idea on why getting values from JavaScript</strong> (making an ajax call to an On Demand Application Process that calls a PL/SQL function in a package) <strong>causes the problem ?</strong> </p>
  
  <p>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.</p>
  
  <p>I have looked at the debug message data for the page but nothing strange for me.</p>