title: The NLS_DATE_FORMAT parameter is not being set using dynamic SQL
body: <p>I have a stored procedure with dynamic SQL that creates a table and calls another procedure to insert some values into the table. The stored procedure also sets some session settings, one of which is the <code>NLS_DATE_FORMAT</code>.</p>
  
  <p>This parameter is not working when the procedure runs. I've tried running the procedure under <code>SYSTEM</code> (the owner) and under a user <code>ADMIN</code> with DBA, Connect, and Resource privileges, but it always shows up in the format <code>DD-MON-RR</code> instead of the desired <code>MM/DD/YYYY</code>. In SQL Developer, under DBA->Database Configuration->Current Database Properties, <code>NLS_DATE_FORMAT</code> also appears in the format <code>DD-MON-RR</code>.</p>
  
  <p>Is there any way to change this for the entire database? I found and tried the following, but it didn't work at all (this includes stopping and restarting the database):</p>
  
  <pre><code>ALTER SYSTEM SET NLS_DATE_FORMAT = 'MM/DD/YYYY' SCOPE=SPFILE;
  </code></pre>
  
  <p>I've looked into the SPFILE and init.ora for answers, but the best I found was a trigger that would set the value after logon for each user. I'm not opposed to this, but I would like to find out if there's a more global way to set the parameter one time without having to create a trigger that sets the following session parameter:</p>
  
  <pre><code>ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY';
  </code></pre>
  
  <p>And here's the relevant part of my stored procedure:</p>
  
  <pre><code>CREATE OR REPLACE
  PROCEDURE DATE_DIM_PROCEDURE(P_DATE DATE)
      AUTHID CURRENT_USER            --Not sure why this is needed since my only two users, but it throws an error without it
  IS
      V_START_DATE DATE := '01/01/1950';
      V_CURRENT_DATE DATE := V_START_DATE;
      V_END_DATE DATE := '12/31/2099';
  BEGIN
      EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE = ''AMERICAN'' ';
      EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TERRITORY = ''AMERICA'' ';
      EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_CALENDAR = ''GREGORIAN'' ';
      EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = ''MM/DD/YYYY'' ';
  
      EXECUTE IMMEDIATE 'CREATE TABLE (...)';
  
      WHILE V_CURRENT_DATE &lt;= V_END_DATE
          NEW_DATE(P_DATE);          --Procedure call that inserts values into table
      LOOP;
  END DATE_DIM_PROCEDURE;
  </code></pre>
  
  <p>Once the procedure runs (and calls the subsequent procedure to insert values), when I open the table, for any user, the <code>NLS_DATE_FORMAT</code> hasn't changed (i.e. it's still in the format <code>DD-MON-RR</code> instead of <code>MM/DD/YYYY</code>).</p>
  
  <p>Thanks.</p>