ALTER SESSION SET nls_date_format doesn't work in APEX. However works in SQL Developer

Question

I try to change nsl parametres with ALTER SESSION SET nls_date_format sentence in Oracle Application Express but parametres remain the same.

However in Oracle SQL Developer I am able to change them.

I would like understand why.

I have found this thread Number and date format : altering NLS_SESSION_PARAMETER does not work? in which Olafur Tryggvason comments something about "application settings" that override settings but I am not able to find the configuration. Should be in "Application Builder" but it is not.

IN APEX

Run

select * from nls_session_parameters;

Results

PARAMETER   VALUE

NLS_LANGUAGE    AMERICAN

NLS_TERRITORY   AMERICA

NLS_CURRENCY    $

NLS_ISO_CURRENCY    AMERICA

NLS_NUMERIC_CHARACTERS  .,

NLS_CALENDAR    GREGORIAN

NLS_DATE_FORMAT mm/dd/yyyy

NLS_DATE_LANGUAGE   AMERICAN

NLS_SORT    BINARY

NLS_TIME_FORMAT HH.MI.SSXFF AM

Run

ALTER SESSION SET nls_date_format ="dd/mm/yyyy";

Results

Statement processed.

0.00 seconds

Run

select * from nls_session_parameters;

Results

PARAMETER   VALUE

NLS_LANGUAGE    AMERICAN

NLS_TERRITORY   AMERICA

NLS_CURRENCY    $

NLS_ISO_CURRENCY    AMERICA

NLS_NUMERIC_CHARACTERS  .,

NLS_CALENDAR    GREGORIAN

NLS_DATE_FORMAT mm/dd/yyyy

NLS_DATE_LANGUAGE   AMERICAN

NLS_SORT    BINARY

NLS_TIME_FORMAT HH.MI.SSXFF AM

IN SQL DEVELOPER

Run

select * from nls_session_parameters;

Results

PARAMETER VALUE

NLS_LANGUAGE SPANISH

NLS_TERRITORY SPANISH

NLS_CURRENCY €

NLS_ISO_CURRENCY      SPAIN

NLS_NUMERIC_CHARACTERS  ,.

NLS_CALENDAR          GREGORIAN

NLS_DATE_FORMAT       mm/dd/yyyy

NLS_DATE_LANGUAGE     SPANISH

NLS_SORT          SPANISH

NLS_TIME_FORMAT       HH24.MI.SSXFF

NLS_TIME_TZ_FORMAT    HH24:MI:SSXFF TZR

NLS_DUAL_CURRENCY     €

NLS_COMP              BINARY

NLS_LENGTH_SEMANTICS  BYTE

NLS_NCHAR_CONV_EXCP   FALSE

Run

ALTER SESSION SET nls_date_format ="dd/mm/yyyy";

Results

1 fila insertadas.

Session alterado.

Run

select * from nls_session_parameters;

Results

PARAMETER   VALUE

NLS_LANGUAGE    SPANISH

NLS_TERRITORY   SPANISH

NLS_CURRENCY    €

NLS_ISO_CURRENCY    SPAIN

NLS_NUMERIC_CHARACTERS  ,.

NLS_CALENDAR    GREGORIAN

NLS_DATE_FORMAT dd/mm/yyyy

NLS_DATE_LANGUAGE   SPANISH

NLS_SORT    SPANISH

NLS_TIME_FORMAT HH24.MI.SSXFF

NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR

NLS_DUAL_CURRENCY €

NLS_COMP  BINARY

NLS_LENGTH_SEMANTICS BYTE

NLS_NCHAR_CONV_EXCP FALSE

Answer 1

Oracle APEX uses a shared connection pool to execute queries. If you're running this in the SQL Workshop, I wouldn't think you will get a result you expect.

If you were running this as part of any given transaction, or if it appears in the Initialisation code as Littfoot suggests, you should see the format change for that session. Once control goes back to the user in the browser, the database connection is released.

This is the same reason you can't use concepts such as package persistent variables, and global temporary tables in Oracle APEX.

If you need to change the settings for your application, the recommended place is the Globalisation Attributes in Shared Components. (The third tab in Application properties.)

Answer 2

Here's how (if I understood the problem correctly):

• login to Apex
• go to App Builder
• choose the application
• in the upper right corner you'll see the Edit Application Properties button
• it contains 4 tabs (Definition, Security, Globalization, User Interface)
• go to Security
• scroll down to "Database Session"

• put something like this into the "Initialization PL/SQL Code":

  begin
    execute immediate q'[alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss']';
  end;
  

• apply changes

That should do it.

---

Initialization code can contain other settings as well, for example NLS_NUMERIC_CHARACTERS:

execute immediate q'[alter session set nls_numeric_characters = ', ']';

Consider reverting those changes, if necessary - see "Cleanup PL/SQL Code" section on the same Security page, just below the "Initialization code".