This is part 2 of series of blogs entitled Things in APEX I've used for years, but not fully understood how they work.

Part 1 : Sorting in APEX: Classic Reports and Card Regions Explained

Since I didn't fully understand how this works, I'm self-taught on this, so if you spot any errors or omissions, please let me know & I'll correct it.

A little Story

I was chatting to my good friend Alex T about the latest disappointment at Elland road and I was reminded of an earlier discussion we had about a Forum post he made:

The truth is... I didn't know enough about Globalization at the time to give him a decent reply and rather than saying I don't know, I took a swipe at something I wasn't very experienced in. I'm sorry Alex 🙏

So I'm back to avenge past sins with a thorough walk-through of the Globalization settings.

I'm going to finally answer Alex's question, but in traditional Matt style, I'm going to take you on the long route to get the answer.

A Demonstration Application

First, I'll create a Primary English (United Kingdom) (en-gb) application called UK (192).

Mildly interesting but... your Primary Language Defaults to your Builder Language, or whatever you pass as the p_lang parameter. I can prove this... click this link, and sign in, try to create an APEX application and it will default to Welsh. Its amusing, but it works. (Mwy am y Gymraeg i ddod yn fuan)

In Shared Components > Globalization Attributes, I'll change the Application Language Derived From to Session.

In Shared Components > Application Translation > Define application languages > Create, I'll create a Polish Language Application

I then follow this blog to create a Stylized Language Selector.

I then create an automatic seed and publisher by following this guide.

We now have our app up and running.

If you want to learn more about translating APEX applications, I encourage you to read this blog as this blog will not cover the translation process.

A word of warning... If you didn't already know, changes you make in the Primary Application will not be reflected in the Translated Application unless you Seed & Publish (this is why I created the automation so you don't forget). However after creating this blog the problem is wider than I thought and can be expanded to... changes you make in the Primary Application will not be instantly reflected in the Primary Application if any Translated Applications share in the same Language as the Primary Application. You want proof? well my Primary Application is English (United Kingdom) and if I create a translated application in English (Trinidad), then I have to constantly Seed & Publish to see any changes I make in the primary application.

Language Choices

My demonstration APEX application selects the language by Session (i.e user click a URL with a special p_lang argument) however there are are other ways of deriving the language.

If Browser Detection is used, a browser set in a locale e.g Spanish (Venezuelan) will not default the to Spanish (es) on the APEX application, it must match exactly or be selected by the user. If you are unsure what language your browser is set to type this in console.

navigator.language

The language list that is emitted by apex_lang.get_language_selector_list is exactly that, just languages, not locales. Here is proof.. in this example I have Translated Applications as en-gb, es, es-ve and pl, the user cannot distinguish between locales of Spanish (i.e Español appears twice).

The locales e.g. Venezuelan (es-ve), as we'll find out later, control number and date formatting.

The list of APEX supported Language codes are here. I hesitate to say "full list" as this would be incorrect. For example Welsh (cy) & Montenerin (me) are supported by APEX (plus others) however they are undocumented. Whereas Serbian (sr) is documented, yet instead makes an alternative appearance in APEX as two codes, sr-cyrl & sr-latn.

In the picture above, my Primary Application is English (United Kingdom) (en-gb), however there are 2 locales of Spanish shown. There are actually 20 locales of Spanish, e.g Chile, Peru, Mexico, etc. Supporting all these languages from a Developer point of view is... just ridiculous.

I looked for advice for Language choice in the documentation and I couldn't find any. Therefore I'm going to provide my own advice

For both Application Primary Language & Translated Applications, chose the locale where the majority of users reside, otherwise chose a base language code en, es, pl, etc

For example:

An application for users in Argentina, with a translated application for Brazilian users, use es-ar & pt-br
An English Language application for users in Netherlands, use en
apex.oracle.com for users spread across the world, chose en for the Primary Language and translated applications for de, es, fr, it, pt-br, zh-cn, zh-tw, ja, ko
Can't decide? chose a base language code en, es, pl, etc

Before we continue our journey... just take a minute to take on board that APEX applications want to be translated into languages, not into variants of that base language (I'm looking at you Español 🇪🇸). Once you do that, languages get repeated on the selection bar.

Next, we have to to take a step back to talk about how NLS is handled at the Database level.

The Various Oracle NLS parameters

To keep this dead simple, let's say there are two sets of NLS Parameters, those at Database level and those that can be (and are frequently - like ~100% of the time) changed at Session level.

NLS typically stands for National Language Support

NLS parameters in Oracle are used to define the language, territory, and character set settings for a session or the entire database

Database Level ( nls_database_parameters )

These are the Database Parameter and cannot be changed without an ALTER DATABASE command. You really need a "sit-down meeting" to discuss if you ever need these changing. Wow, I haven't used that phrase for years - I must resurrect it again 🔥

Let's see what they look like on my Autonomous Database

SELECT PARAMETER, VALUE
  FROM nls_database_parameters
ORDER BY 1;

As you can see, a very American style configuration. Even though I myself am located in the UK, there is zero evidence of UK here.

Session Level ( nls_database_parameters )

Here we have our session parameters, which take preference over the DB Parameters which are often changed by the client.

SELECT PARAMETER, VALUE
  FROM v$nls_parameters
ORDER BY 1;

I'm only going to show 10 rows here, but you see the point that somehow, running this SQL in SQL Workshop, has changed the settings to a United Kingdom locale.

I'll return to this point later, but the spoiler is... SQL Workshop changes to the locale you selected when signed in to APEX.

Default Globalization Attributes

Back in APEX, if I visit the Shared Components > Globalization Attributes I see only one set of attributes that applies both to the default application (i.e en-gb) and all translated applications (i.e pl). Here it is:

All the values you see above (basically DS) are the default APEX settings and refer to all applications, regardless of their language.

I'm going to refer to this a few times in this blog as the default globalization settings.

Warning: There is a bug in APEX whereby the Application Primary Language can be changed to NULL and saved. In this case it appears to default to English (en)..

... however you have in fact broken all the Date Pickers in the Application.

To fix this, you have to change the Application Primary Language again (or maybe twice). Actually, don't try this, not even for fun - it's not fun.

If Oracle is reading this... After you fix this, could I suggest a Stay on page feature? as this was sorely missing writing this blog.

Now lets look at this page in detail...

Globalization Date Settings

The DS Format mask stands for Date Short and the documentation describes this as the following quote

DS returns a value in the short date format. Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format 'MM/DD/RRRR'. In the ENGLISH_UNITED_KINGDOM locale, it is equivalent to specifying the format 'DD/MM/RRRR'.

There are two things baffling me from this statement:

The locales AMERICAN_AMERICA & ENGLISH_UNITED_KINGDOM is not actually a thing. Its a lazy way of specifying two parameters in one and it confuses the reader in to thinking there is a single command to set both Language & Territory. Instead, the reader has two run a double-command like this:
```
  ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN' NLS_TERRITORY = 'AMERICA';
```
Its saying It depends on two parameters? lets see about that🧘‍♂️ ...

DS stands for Date Short and only returns numbers and delimiters but not words. I checked this out for various locales including Thailand, Japan, India and no words appear in all examples of DS that I can see. So, tell me Oracle, how can the DS date format depend on two parameters? surely its just one? ... Oh never-mind 🤷

Oracle, if you are amending the documentation, please also draw a box around DAY to align with all the other elements please

The DL (Date Long) format on the other-hand is another story... for example.. with the French (France) (Fr) locale it produces a date format like jeudi 17 octobre 1919. So for words, it does use both the NLS_TERRITORY and NLS_LANGUAGE parameters. But who wants to use words when returning a value from a APEX Data Picker? 🙋‍♂️ not me.

"not me" because its perfectly legit for users to keyboard-type into Date Pickers and in my opinion, that's awkward for the users.

The quote also provides 2 examples of a date format

MM/DD/RRRR (US)
DD/MM/RRRR (UK)

If you are wondering why RRRR is used instead of YYYY, it just concerns the handling of 2 digit years close to century changes.

Here's an picture that's worth a thousand words:

Why do we even have 4 Date formats?

Right... we have these 4 formats, the help text is useful, but the documentation is even more useful-er.

You can read these in full or skip to the summary table.

Application Date Format or APP_NLS_DATE_FORMAT

APP_NLS_DATE_FORMAT is the application date format of the database session. This value reflects the format specified in the Application Date Format attribute of the Globalization settings of the application. However, if the Application Date Format is not set, then APP_NLS_DATE_FORMAT returns the NLS_DATE_FORMAT value of the database session at the start of the request to the APEX engine.

Application Date Time Format or APP_DATE_TIME_FORMAT

Application Date Time Format attribute of the Globalization setting. If this attribute value is not specified, then a reference to APP_DATE_TIME_FORMAT will return the NLS database session date format and the NLS time format.

This attribute does not alter any NLS settings.

Application Timestamp Format or APP_NLS_TIMESTAMP_FORMAT

APP_NLS_TIMESTAMP_FORMAT is the application timestamp format of the database session. This value reflects the format specified in the Application Timestamp Format attribute of the Globalization settings of the application. However, if the Application Timestamp Format is not set, then APP_NLS_TIMESTAMP_FORMAT return the NLS_TIMESTAMP_FORMAT value of the database session at the start of the request to the APEX engine.

Application Timestamp Time Zone Format or APP_NLS_TIMESTAMP_TZ_FORMAT

APP_NLS_TIMESTAMP_TZ_FORMAT is the application timestamp time zone format of the database session. This value reflects the format specified in the Application Timestamp Time Zone Format attribute of the Globalization settings of an application. However, if the Application Timestamp Time Zone Format is not set, then APP_NLS_TIMESTAMP_TZ_FORMAT returns the NLS_TIMESTAMP_TZ_FORMAT value of the database session at the start of the request to the APEX engine.

Lets summarize in a table

Name	APP_ Parameters	Initial Value	Default	Used in APEX for
Application Date	APP_NLS_DATE_FORMAT	DS	NLS_DATE_FORMAT	Every Date Picker
Application Date Time Format	APP_DATE_TIME_FORMAT		NLS time format	Nothing, unless specifically stated.
Application Timestamp Format	APP_NLS_TIMESTAMP_FORMAT	DS	NLS_TIMESTAMP_FORMAT
Application Timestamp Time Zone Format	APP_NLS_TIMESTAMP_TZ_FORMAT	DS	NLS_TIMESTAMP_TZ_FORMAT

How do these APP_ Parameters apply in APEX

With en-gb being the Application Primary Language, APEX has used this to change NLS Session parameters NLS_LANGUAGE and NLS_TERRITORY which in turn change other parameters - more on this to come later.

If we have the 4 Date Parameters set as below...

...lets see how all this has influenced things

Ah so APP_DATE_TIME_FORMAT has been modified as documented 👇

If this attribute value is not specified, then a reference to APP_DATE_TIME_FORMAT will return the NLS database session date format and the NLS time format.

Presumably "NLS time format" is the NLS_TIME_FORMAT (it could've been clearer).

However its not the NLS_TIME_FORMAT because that's HH24.MI.SSXFF and not the HH24.MI.SS as seen in APP_DATE_TIME_FORMAT. So which parameter does it come from? its either...

Some sort of hidden time format I don't know of
NLS_TIME_FORMAT has somehow been butchered

I asked this question on the forums and as the magnificent Karel Ekema points out, it has indeed been butchered by APEX.

Karel translates this as

If NULL, it will default to (APP_)NLS_DATE_FORMAT. If that one is not having a time part (I guess mostly it won't), it will take the time format from NLS_TIME_FORMAT (from SYS.NLS_SESSION_PARAMETERS), stripping off any XFF portion indeed.

Why had APEX thought it necessary to remove XFF?

First, what is XFF? well the whole thing is a timestamp format mask, not a date one. I can prove this to you... watch...

The only way it'll work is like this (i.e using SYSTIMESTAMP) ...

XFF breaks down into this:

X is the local radix character
- What the chuff does that mean? The local radix character is the delimiter character, used in numbers in the current locale i.e usually a dot or a comma
FF displays the fractional seconds, and when combined with X, it adapts to the locale's decimal separator.

So, XFF in the format model makes sure that the fractional seconds are correctly formatted according to the locale's numeric settings. If you see the picture above, XFF represents the .749851 section.

Wait a minute (no pun intended)? The United Kingdom's time format is delimited by dots? Apparently so... No one told me 🤷 and I've been living here quite some time. I thought it was more like this legendary format

I did some research and apparently it's correct

Finally, its been confirmed that the NLS_TIME_FORMAT has been butchered to make it in to a APP_DATE_TIME_FORMAT that works with Dates, specifically with the TO_DATE function. Ta-da 🎉

Clients naturally change V$NLS_Parameters

Clients (i.e things that connect to the Database) are changing the Session Level NLS Parameters.

It's FACT. Why do they do this? are they trying to help? does it really help? - I guess, yes it does help - yes, thanks. It means a Polish Developer can read error messages in Polish and an Armenian in Armenian.

Remember from earlier, my Database settings are ENGLISH/AMERICAN but now I'll prove my clients are changing to en-gb.

SQL Developer

SQL Developer will change the NLS parameters by guessing on things based on your local machine - source: Jeff Smith

SQL Workshop

APEX SQL Workshop changes them - here is proof 👇

SQL Workshop changes the locale based on which one locale you select when you enter APEX

Whichever way its selected or defaulted, then APEX will, on login, write to cookie ORA_WWV_REMEMBER_LANG.

It's this value that's then used in determining your locale.

Proof is in the footer of many APEX pages, including SQL Workshop. Did you ever pay attention to this? It's showing the User, Workspace & Locale.

Deleting the cookie whilst in an APEX session does nothing as its baked into your session and the cookie is no longer accessed. Once you sign in again, the cookie gets recreated.

SQL Developer Web

SQL Developer Web does this too. It takes Preference > Region > Language setting. Since there is no English / United Kingdom 👊 (Oh man), it'll default to English / English. In the picture below, it actually changed the settings to AMERICAN, but it doesn't really look like it - since it was already AMERICAN. If you change Preference > Region > Language > Spanish - Español it definitely changes the NLS Parameters - proof 👇

SQL*Plus

This is permanently knackered on my machine, so lets not do this right now🥀

c:\sqlplus
Incorrect environment variable PLUS_DFLT
Program execution error

Press F to pay respects - or let me know how to fix it 🙏

SQLcl

This is a Java Application which, on connect, manipulates the NLS parameters based on the JAVA_TOOL_OPTIONS settings. It can be influenced like this:

set JAVA_TOOL_OPTIONS=-Duser.language=en -Duser.region=US -Dfile.encoding=UTF-8

If no JAVA_TOOL_OPTIONS it selects the locals from the local machine. You can test this in JAVA yourself:

import java.util.Locale;

public class LocaleTest {
    public static void main(String[] args) {
        // Get the default locale
        Locale defaultLocale = Locale.getDefault();

        // Get the default file encoding
        String fileEncoding = System.getProperty("file.encoding");

        // Output the default locale settings
        System.out.println("Default Language: " + defaultLocale.getLanguage());
        System.out.println("Default Country/Region: " + defaultLocale.getCountry());
        System.out.println("Default Locale: " + defaultLocale.toString());
        System.out.println("Default File Encoding: " + fileEncoding);

        // Output additional system properties related to locale
        System.out.println("System Property 'user.language': " + System.getProperty("user.language"));
        System.out.println("System Property 'user.region': " + System.getProperty("user.region"));
        System.out.println("System Property 'user.country': " + System.getProperty("user.country"));
    }
}

Compile it like this

javac LocaleTest.java

and run it like this

java -cp . LocaleTest

Here you can see the default settings from your local machine

c:\1>java -cp . LocaleTest
Default Language: en
Default Country/Region: GB
Default Locale: en_GB
Default File Encoding: UTF-8       
System Property 'user.language': en
System Property 'user.region': null
System Property 'user.country': GB

and here here you can see that JAVA is being influenced by JAVA_TOOL_OPTIONS

c:\1>set JAVA_TOOL_OPTIONS=-Duser.language=en -Duser.region=US -Dfile.encoding=UTF-8

c:\1>java -cp . LocaleTest
Picked up JAVA_TOOL_OPTIONS: -Duser.language=en -Duser.region=US -Dfile.encoding=UTF-8
Default Language: en
Default Country/Region: US
Default Locale: en_US
Default File Encoding: UTF-8       
System Property 'user.language': en
System Property 'user.region': US  
System Property 'user.country': GB

In this way SQLcl initiates a mandatory change to the NLS settings. Here is more proof 👇

Important: Windows users, to avoid Unicode Charset issues regarding currency symbols e.g NLS_CURRENCY ┬ú ... type this before using SQLcl (or you can set it by default : source: Jeff Smith)

chcp 65001

When in SQLcl, type

show nls

Or to get 0.01 seconds of your life back ⛵ use the following.

sho nls

Settings

DB_TIMEZONE +00:00
NLS_CALENDAR GREGORIAN
NLS_CHARACTERSET AL32UTF8
NLS_COMP BINARY
NLS_CURRENCY £
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_DUAL_CURRENCY €
NLS_ISO_CURRENCY UNITED KINGDOM
NLS_LANGUAGE AMERICAN
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS .,
NLS_SORT BINARY
NLS_TERRITORY UNITED KINGDOM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH24.MI.SSXFF
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH24.MI.SSXFF TZR
NLS_TIME_FORMAT HH24.MI.SSXFF
NLS_TIME_TZ_FORMAT HH24.MI.SSXFF TZR
SESSION_TIMEZONE Europe/Paris
SESSION_TIMEZONE_OFFSET +02:00

The above proves it changed my NLS Parameters to my local defaults. or if I run this

set JAVA_TOOL_OPTIONS=-Duser.language=en -Duser.region=US -Dfile.encoding=UTF-8

Then I can influence it to English / American

DB_TIMEZONE +00:00
NLS_CALENDAR GREGORIAN
NLS_CHARACTERSET AL32UTF8
NLS_COMP BINARY
NLS_CURRENCY $
NLS_DATE_FORMAT DD-MON-RR
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
SESSION_TIMEZONE Europe/Paris
SESSION_TIMEZONE_OFFSET +02:00

What happens when we change NLS_LANGUAGE and NLS_TERRITORY?

What happens if we alter the Language?

alter session set nls_language='SPANISH';

It sets the language related NLS parameters

NLS_LANGUAGE e.g to SPANISH
NLS_DATE_LANGUAGE e.g to SPANISH
NLS_SORT e.g SPANISH (or BINARY if its ENGLISH)
- See next section for an explanation of what NLS_SORT means

What happens when we alter the Territory?

alter session set nls_territory='UNITED KINGDOM';

It sets the format related NLS parameters

NLS_TERRITORY e.g UNITED KINGDOM
NLS_CURRENCY e.g £ ⭐Look Alex !
NLS_DUAL_CURRENCY e.g €
NLS_DATE_FORMAT e.g DD-MON-RR ⭐ SPAIN is DD/MM/RR
NLS_ISO_CURRENCY e.g UNITED KINGDOM
NLS_NUMERIC_CHARACTERS e.g ., ⭐POLAND is ,
NLS_TIMESTAMP_FORMAT e.g DD-MON-RR HH24.MI.SSXFF
NLS_TIMESTAMP_TZ_FORMAT e.g DD-MON-RR HH24.MI.SSXFF TZR
NLS_TIME_FORMAT e.g HH24.MI.SSXFF
NLS_TIME_TZ_FORMAT e.g HH24.MI.SSXFF TZR

So changing NLS_LANGUAGE & NLS_TERRITORY is all you need to globalize your connection? right? right? ...well it seems so, as APEX only changes these two settings on each DB event (see first line of the picture below. Note: This is not the first line of the debug, its about 15 lines down).

What are Character Comparison Conversions?

There are two Character Comparison Conversion parameters:

NLS_SORT
NLS_COMP

NLS_SORT

We learnt that altering the Language may alter the NLS_SORT. Whats the deal with this? Imagine you have a list of names in an Interactive Report:

Without NLS_SORT:

If NLS_SORT is set to a basic binary sort, i.e BINARY, the names will be sorted purely by their ASCII values, which would lead to an order like this:

Here, Ángel comes last because the accent on the Á has a higher ASCII value than the other characters.

With NLS_SORT:

If you set NLS_SORT to XSPANISH_AI (Spanish, accent-insensitive), the sort order would respect Spanish sorting rules, where accented characters like Á are considered equal to their unaccented counterparts A. The sort order would then be:

Ángel comes third, because in Spanish, Á is treated as A, and the rest of the word follows normal alphabetical rules.

When NLS_SORT is set to XSPANISH_AI, and you run a query that sorts or compares the FIRST_NAME column, the Oracle optimizer might avoid using an existing binary index on FIRST_NAME. This is because the Spanish sort order differs from the binary order of the data in the index. As a result, the optimizer might perform a full table scan instead, which could slow down the query.

Using NLS_COMP

If you also set NLS_COMP to LINGUISTIC, it ensures that the sorting and comparison rules of NLS_SORT are applied consistently in your queries.

Without LINGUISTIC we cannot retrieve results based on a non-accented Angel

With LINGUISTIC we can retrieve results based on a non-accented Angel

However, it might also further influence the optimizer’s decisions, potentially affecting performance.

Too much information: Sort it out for me please APEX

APEX has got your back. APEX won't play around with the settings unless you ask it to. These settings are also found on the Globalization Page

The top setting being the NLS_SORT and the bottom being the NLS_COMP. I've only seen one APEX App in my life that has had a non-default value. That value was BINARY (so not a performance concern).

Boost your Timezone knowledge

The United Kingdom has only one Timezone 🎉, so its been too easy for me to become complacent with my Timezone knowledge. Let's improve!

Starting with the Database... I discovered that my Database Timezone is 00:00

SQL> SELECT DBTIMEZONE FROM dual;

DBTIMEZONE
_____________
+00:00

This means its set to UTC. What is UTC? UTC stands for Coordinated Universal Time (I expected it would be called CUT, however UTC is a historical 1960's compromise of English & French phrases which you are free to research on your own).

Here are some advantages of using UTC

Universal Standard:
- UTC is used as the basis for timekeeping worldwide. It's the time standard that doesn't change with the seasons or locations.
Not Affected by Time Zones:
- Unlike local times, which are subject to time zones, daylight saving time, and regional variations, UTC remains consistent everywhere.
Usage in Technology:
- UTC is critical in fields like aviation, computing, telecommunications, and other global systems where precise timekeeping is essential. Many databases, servers, and global communications rely on UTC to avoid confusion across different time zones.

Lets do some testing

My Database is set to UTC
- proof (from SQLcl's show nls command) 🎩
```
  DB_TIMEZONE +00:00
```
My PC is set to Europe/Warsaw which is 2 hours ahead of UTC (1 hour in winter)
- proof (my laptop clock)✨
SQLcl thinks its Europe/Paris (same timezone as Europe/Warsaw). It worked this out from my machine (as I mentioned earlier)
- proof 🎩
```
  SESSION_TIMEZONE Europe/Paris
  SESSION_TIMEZONE_OFFSET +02:00
```

When I run this

SELECT
    TO_CHAR(SYSDATE, 'HH24:MI') AS SYSDATE_Time,
    TO_CHAR(SYSTIMESTAMP, 'HH24:MI') AS SYSTIMESTAMP_Time,
    TO_CHAR(CURRENT_DATE, 'HH24:MI') AS CURRENT_DATE_Time,
    TO_CHAR(CURRENT_TIMESTAMP, 'HH24:MI') AS CURRENT_TIMESTAMP_Time,
    TO_CHAR(FROM_TZ(CAST(SYSDATE AS TIMESTAMP), 'UTC') AT TIME ZONE 'Europe/Warsaw', 'HH24:MI') AS SYSDATE_Paris_Time
FROM
    dual;

I get this

SYSDATE_TIME    SYSTIMESTAMP_TIME    CURRENT_DATE_TIME    CURRENT_TIMESTAMP_TIME    SYSDATE_PARIS_TIME
_______________ ____________________ ____________________ _________________________ _____________________
08:21           08:21                10:21                10:21                     10:21

In APEX, if I do the same

What the juice? The Current Date & Current Timestamp have stopped working!

What if I slide this to enabled in the Globalization Attributes?

if you are quick (or your machine is slow), you'll now see this Set Time Zone flash up

Here is the detectTimeZone() code if you're interested

function detectTimeZone(){
var lGmtHours = -(new Date()).getTimezoneOffset();
var lHours = parseInt(lGmtHours/60);
var lMinutes = lGmtHours%60;
window.location.href='\u002Fords\u002Fr\u002Fwksp_x\u002Fuk\u002Flogin?session=130355475867289\u0026tz='+lHours+":"+(lMinutes<10?"0"+lMinutes:lMinutes);
};

I obtained this code by turning JavaScript off in Chrome and reloaded the page.

Basically it works out the Timezone using getTimezoneOffset and then redirects the URL passing the tz parameter to the page in the format of HOURS:MINS (see below)

Once complete, it then shows the correct dates.

Here is the documentation for tz

This is misleading to the reader as it suggests that timezone is a supported parameter. But its not

As proven above, it is not supported. Only tz is supported and it can be used to offset the current_date.

Note that territory is supported over the URL, but it does nothing as far as I can see. APEX ignores both valid & invalid territories

While we're at it (Oracle, if you are reading), lang is also misleading with the correct parameter being p_lang.

Explain this Matt.

I told you that SQLcl grabs a bunch of local machine settings when establishing a connection and then issues some NLS parameter changes. Well APEX (also a client) is no different, If you slide Automatic Time Zone across, then APEX grabs your clients' (i.e your Browsers') Timezone to work out where in the world you'll like your timezone presented in.

I can evidence this by observing APEX changing this in the debug (see first line)

Q. Is this the end of SYSDATE?

In a multi timezone application, SYSDATE is not your friend, as its always the server clock. If you ever want the server time, it'll always be there for you.

For a global application, you need to use

CURRENT_DATE
CURRENT_TIMESTAMP

For a single time-zone application, SYSDATE is fine & it will adapt to Daylight Saving Time too as your server's clock changes.

Q. Anymore bad news?

Brace yourself. Its probably the end of DATE column types for you too 🥀

This is because, in APEX, when a user submits the page, you need to store user Dates/Times from all kinds of time-zones don't you. What do you do, convert them to UTC to store them, then somehow convert them when displaying them? The trouble is, the date-picker is unaware of time-zones, so you have a to fudge the display somehow.

Even if you wanted to store the dates along with the time-zone, DATE column types do not support this.... however a TIMESTAMP column type can!

Therein lies the answer 👉 TIMESTAMP WITH LOCAL TIME ZONE is the answer to all this:

It does not store the time zone information; it adjusts timestamps to the session's time zone at retrieval time.
It converts timestamps to UTC for storage but displays in the session’s local time zone.
It's ideal for scenarios where you need to work with dates and times in the context of the local time zone of the user or session.

TIMESTAMP WITH LOCAL TIME ZONE Example

Lets do an example:

-- Create a table named INTERGALACTIC_EVENTS with event_id as an identity column
CREATE TABLE intergalactic_events (
    event_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- Identity column
    event_name VARCHAR2(100),                                  -- Name of the intergalactic event
    event_date TIMESTAMP WITH LOCAL TIME ZONE,                 -- Date and time of the event, adjusted to the local time zone
    galaxy VARCHAR2(50),                                      -- Galaxy where the event takes place
    alien_species VARCHAR2(50),                                -- Alien species attending the event
    event_description VARCHAR2(400)                            -- A fun description of the event
);

Add a record with a date in APEX

Now when I switch my PC timezone to Osaka

and sign out/back in to APEX (BTW: The APEX timezone is set per session) and reload the page and ...👀... the same record has been resolved to Osaka time 🇯🇵

Important that APEX Automatic Timezone needs to be on for this

If I add another event, this time in Osaka time

Then in my SQLcl Warsaw client, I can see those Osaka dates/times as it relates to Warsaw

SQL> select event_name, TO_CHAR(event_date, 'DD-MON-YYYY HH24:MI:SS TZR') FROM  INTERGALACTIC_EVENTS;

EVENT_NAME                     TO_CHAR(EVENT_DATE,'DD-MON-YYYYHH24:MI:SSTZR')
______________________________ _________________________________________________
Galactic Disco Extravaganza    30-AUG-2024 11:30:00 EUROPE/WARSAW
Nebula Ninja Training Camp     02-AUG-2024 13:30:00 EUROPE/WARSAW

SQL>

Amazing right?

Date Pickers

OK, so what about Date Pickers which show the time, what time format do they show?

Huh? So even with Show Time selected...

... clicking the Done button returns the Date but not the time portion. This is because the Date Pickers, unless they have a Format Mask specified, always use the Application Date Format (APP_NLS_DATE_FORMAT).

But why don't Date Pickers just use the Application Date Time Format? ... Well, Date Pickers don't always use the time element... most Date Pickers just.. pick.. the.. date.. (the clue is in the name). So that is why it doesn't default.

If we wanted to specify the time we need the Show Time enabled (as above) and supply a Format Mask.

We can do this as we know that we can leave the Application Date Time Format blank (in Globalization Attributes) and it will default the to the Application Date Format and the locale Time format.

We can of course use a hard coded Format Mask for Date Pickers however we are at risk of losing an application-wide standard format

Using a Custom Application Date Time in the Globalization Attribute

If we really wanted a non-null value we could try to set the Application Date Time Format to DS TS (Date Short Time Short)

Now lets experiment with what DS TS actually works

TO_CHAR(SYSDATE, :APP_DATE_TIME_FORMAT );

Here are the results

Locale	Example
en-gb	13/08/2024 15:35:57
en-us	8/13/2024 3:37:15 PM
ar-sa	13/08/2024 03:37:49 م

Kinda cool... and pretty awesome because with DS TS we set it once and use it throughout our application & all our translated applications. Lets try it on our Date Picker with Time shown.

Now when we run the page

Oh snap... there goes my unbelievably amazing idea. Whats happening is that the Data Picker doesn't support some elements, including TS. Where is the documentation what parts are supported and what are not? It's actually here if you must know, however you wont find exactly what you are looking for. I think that the undocumented item.Datepicker.js library relies heavily on the apex.date library and that has some shortcomings on what is supported. For example the documentation for the format function says..

Currently not supported Oracle specific formats are: SYEAR,SYYYY,IYYY,YEAR,IYY,SCC,TZD,TZH,TZM,TZR,AD,BC,CC,EE,FF,FX,IY,RM,TS,E,I,J,Q,X

.. Eeh! TS is on that list.

So in summary we cannot supply a Time Portion that adjusts to the translated application's locale. If we want one, we have to leave it blank & the butcher-function kicks in. If we leave it blank, we cannot specify a custom date portion.

This table summarizes the issue we have

Date Portion	Time Portion	Example Value
Custom	Custom	DD/MM/YYYY HH24:MI
Locale	Custom	DS HH24:MI
Custom	Locale	DD/MM/YYYY TS 👉 Not Supported
Locale	Locale	Leave Blank (because DS TS is not supported)

One absolutely huge problem we have with the APEX Globalization Attributes page, is that once we move away from a locale-sensitive format masks (e.g DS) and start using insensitive ones (e.g DD/MM/YYYY) we completely disrupt the locale usage in Translated Applications. Don't panic, I have a solution a few pages down.

By the way, using a format mask like this, with the PM portion...

...provides a meridian (i.e AM or PM) drop-down selector on our Data Picker which looks great for our American friends...

... however, that time portion is bit clumsy to use in the UK and other locales.

The issue with including Seconds in a Date Picker

Here I have picked a date with a Date Picker.

What did the user select? A time without Seconds 😏

What do you see in the item Value? Seconds! 😑

The Format Mask is &APP_DATE_TIME_FORMAT. and it using the default globalization Attributes.

Is this wrong? Yes and No 🥺

Yes the format mask contains seconds and therefore it should show the seconds. But also, No because the Date Time Picker doesn't allow the user to pick seconds & they never selected seconds in the first place.

As Developers, we are just itching to change the APP_DATE_TIME_FORMAT settings in the Globalization Settings to remove the seconds.. but we know if we do that, it'll change to a locale-insensitive setting and it'll cause disruption in our Translated Applications.

What if a Date Picker needs to use seconds too? hmm a Date Picker doesn't usually use seconds... I wouldn't really expect many users to select a precise second, would you?

The locale-insensitive method

What if we had 1919 Date Pickers that did have seconds and 1818 that didn't have seconds? and we now only have 1 Application Date Time Format to add our format. Fear not! we can always create a substitution string for this and assign it to those 1919 Date Pickers.

However this is insensitive to other locales who now all have this colon delimited time format.

The local-sensitive method

There is an approach in the "A Universal Globalized Format" section of this blog, using a DS #TS_NO_SECONDS approach. Just keep reading - we'll get there.

Using a Custom APP_DATE_TIME_FORMAT e.g DS HH21:MI for all Dates

Could we really settle on HH24:MI? Does it work for all locales? The thing about TS being really neat is that it adapts to all locales (however we proved that TS is not supported for Date Picker). Even for locales that don't use colons or dots for separators!!! Calm down.. I'm joking... here is proof... user2864740 tells us

ISO 8601 only allows for a colon (:) for separating time components in the extended format:

The basic format is [hh][mm][ss] and the extended format is [hh]:[mm]:[ss].

There is no provision for an alternate extended format.

The ISO standard tells us that dots/periods can be used as separators with the allowance for colons in the extended format.

Back to our example, lets say the standard, across the whole world, is a time format of

HH24:MI

This seems to be best we can do (or HH24:MI:SS if we want seconds). Lets' try a Globalization setting of

... and use this with a Date Picker Format Mask of ...

There we go. Job's a good'un. Here is the en-gb version

and here is the en-us version

and here is the ja (Japanese) version in case you were curious

We can even create a validation for these Date Pickers using a Function Body (returning Boolean) Validation.

DECLARE
    l_page_item_value_c         CONSTANT VARCHAR2(512) DEFAULT :P1_DATE_PICKER;
    l_page_item_format_mask_c   CONSTANT VARCHAR2(512) DEFAULT :APP_DATE_TIME_FORMAT;
    FUNCTION IS_DATE(
        p_date        IN VARCHAR2,
        p_format_mask IN VARCHAR2 DEFAULT NULL
    ) RETURN BOOLEAN IS
        v_date DATE;
    BEGIN
        -- Attempt to convert the string to a date using the provided format mask
        IF p_format_mask IS NOT NULL THEN
            v_date := TO_DATE(p_date, p_format_mask);
        ELSE
            -- If no format mask is provided, use the default NLS date format
            v_date := TO_DATE(p_date);
        END IF;
        RETURN TRUE;
    EXCEPTION
        -- If an exception is raised, the string is not a valid date
        WHEN OTHERS THEN
            RETURN FALSE;
    END IS_DATE;

BEGIN
  RETURN IS_DATE(   l_page_item_value_c, 
                    l_page_item_format_mask_c );
END;

However what if our Gaelic friends say "It's just not working for us, I want the time delimited by dots".. Oh my days!! our beautiful multi-lingual, multi-globalized application needs a hack. Read on to the next section...

A Universal Globalized Format

Lets expand on our requirement & add some more locales.

Locale	Example Current Locale Date Time Format	Example Desired Format	Rationale
en-gb (default)	25/09/2024 14.15.00	25/09/2024 14:15:00	General preference to standardize on a time of HH24:MI:SS
gd (Gaelic)	25/09/2024 14.15.00	25-SEP-24 14.15.00	Gaelic Locale / Dots for times
th (Thai)	25 ก.ย. 2024 14.15.00	09/06/2024 14:15:00	Wants USA format
All other countries (e.g Poland)	DS TS (Various)	DS 14:15:00	Take the Default Date (en-gb) format for the locale, but show times as HH24:MI:SS

Things we already know

There's only one set of Globalization Attributes for all translated applications.
We can use substitution strings in the Globalization Attributes
DS TS doesn't work as APEX Date Pickers cannot understand TS.
Leaving APP_DATE_TIME_FORMAT blank, trims the XFF from the NLS_DATE_FORMAT
We cannot hardcore the Time or any elements
The standard time format includes seconds

To meet this requirement, we'd like something flexible to control all Globalization settings.

Lets do it. Create this table

CREATE TABLE app_globalization_attributes (
    id NUMBER GENERATED ALWAYS AS IDENTITY,
    app_id NUMBER,
    app_language VARCHAR2(255) CHECK (LOWER(app_language) = app_language),
    date_format VARCHAR2(255) DEFAULT 'DS',
    date_time_format VARCHAR2(255),
    timestamp_format VARCHAR2(255) DEFAULT 'DS',
    timestamp_tz_format VARCHAR2(255) DEFAULT 'DS',
    nls_comp VARCHAR2(255),
    CONSTRAINT app_globalization_uk UNIQUE (app_id, app_language)
);

Add a default record for your APP ID (mine is 192)

INSERT INTO app_globalization_attributes( app_id ) VALUES ( 192 );

The requirement for en-au requires a format of DD/MM/YYYY TS but, as we know, TS isn't supported. So we'll (LOL - I mean ChatGPT will) create our own function that does the same as the butcher function (but better).

CREATE OR REPLACE FUNCTION convert_timestamp_mask_to_date_mask(
    p_timestamp_mask  IN VARCHAR2,
    p_trim_seconds    IN VARCHAR2 DEFAULT 'N'
) RETURN VARCHAR2 IS
    v_date_mask VARCHAR2(4000);
BEGIN
    -- Remove fractional seconds part ('.FF', '.FF1', '.FF2', ..., '.FF9')
    v_date_mask := REGEXP_REPLACE(p_timestamp_mask, '\.FF[0-9]*', '');

    -- Remove unnecessary timestamp elements like '.XFF' and 'XFF'
    v_date_mask := REGEXP_REPLACE(v_date_mask, '\.XFF|XFF', '');

    -- Remove time zone part ('TZR', 'TZD', 'TZH', 'TZM')
    v_date_mask := REGEXP_REPLACE(v_date_mask, 'TZ[RDHM]', '');

    -- Remove time zone abbreviation ('TZH:TZM')
    v_date_mask := REGEXP_REPLACE(v_date_mask, 'TZH:TZM', '');

    -- Conditionally remove seconds ('SS') along with the leading delimiter (e.g., ':', '.')
    IF p_trim_seconds = 'Y' THEN
        v_date_mask := REGEXP_REPLACE(v_date_mask, '([:|\.])SS', '');
    END IF;

    -- Remove any remaining fractional second dots and unwanted spaces
    v_date_mask := RTRIM(LTRIM(v_date_mask, ' '), '.');

    RETURN v_date_mask;
END convert_timestamp_mask_to_date_mask;
/

We can test it like this... wow it butchers the time format just like the real thing.

Now create a database procedure to assign the values per locale to application items - this is the magic.

create or replace PROCEDURE set_app_globalization_attributes (
    p_app_id          IN NUMBER DEFAULT TO_NUMBER(v('APP_ID')),
    p_app_language    IN VARCHAR2 DEFAULT apex_util.get_session_lang
) IS
    -- Define the rowtype to hold the returned values from the cursor
    TYPE app_glob_attr_rowtype IS RECORD (
        date_format          app_globalization_attributes.date_format%TYPE,
        date_time_format     app_globalization_attributes.date_time_format%TYPE,
        timestamp_format     app_globalization_attributes.timestamp_format%TYPE,
        timestamp_tz_format  app_globalization_attributes.timestamp_tz_format%TYPE,
        nls_comp             app_globalization_attributes.nls_comp%TYPE
    );

    -- Associative array to hold the name/value pairs for replacement
    TYPE t_name_value_array IS TABLE OF VARCHAR2(4000) INDEX BY VARCHAR2(100);
    l_name_value_array t_name_value_array;

    -- Local variables to hold the cursor's result
    l_glob_attr                   app_glob_attr_rowtype;
    l_nls_time_format             v$nls_parameters.value%TYPE DEFAULT NULL;
    l_nls_time_format_conv        v$nls_parameters.value%TYPE DEFAULT NULL;
    l_nls_time_format_no_ss_conv  v$nls_parameters.value%TYPE DEFAULT NULL;

    -- Local procedure to fetch globalization attributes using a cursor
    PROCEDURE fetch_globalization_attributes (
        p_app_id           IN NUMBER,
        p_app_language     IN VARCHAR2,
        p_glob_attr        OUT app_glob_attr_rowtype,
        p_called_with_null IN BOOLEAN DEFAULT FALSE  -- Track if it's already called with NULL language
    ) IS
        l_fetched BOOLEAN DEFAULT FALSE;
        CURSOR c_glob_attr IS
            SELECT date_format,
                   date_time_format,
                   timestamp_format,
                   timestamp_tz_format,
                   nls_comp
            FROM app_globalization_attributes
            WHERE app_id = p_app_id
              AND ((app_language = p_app_language) OR (p_app_language IS NULL AND app_language IS NULL));
    BEGIN
        OPEN c_glob_attr;
        FETCH c_glob_attr INTO p_glob_attr;
        l_fetched := c_glob_attr%FOUND;
        CLOSE c_glob_attr;

        IF NOT l_fetched THEN
            -- If no data is found, and it's the first time, call the procedure again with p_app_language = NULL
            IF p_app_language IS NOT NULL AND NOT p_called_with_null THEN
                fetch_globalization_attributes(p_app_id, NULL, p_glob_attr, TRUE);
            ELSE
                -- If already called with NULL or no data found after the recursive call, stop recursion and log
                p_glob_attr := NULL;
                apex_debug.message('No matching globalization attributes found for app_id = %s and app_language = %s. Falling back to default values', p_app_id, p_app_language);
                -- Hardcoded Defaults
                p_glob_attr.date_format := 'DS';
                p_glob_attr.timestamp_format := 'DS';
                p_glob_attr.timestamp_tz_format := 'DS';
            END IF;
        END IF;

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            p_glob_attr := NULL;
            apex_debug.message('No matching globalization attributes found for app_id = %s and app_language = %s', p_app_id, p_app_language);
    END fetch_globalization_attributes;

    -- Function to replace tags in a string using the name/value array
    FUNCTION replace_tags(
        p_string IN VARCHAR2
    ) RETURN VARCHAR2 IS
        l_modified_string VARCHAR2(4000);
        l_key VARCHAR2(100);
    BEGIN
        -- Initialize the modified string with the original string
        l_modified_string := p_string;

        -- Start with the last key in the name/value array
        l_key := l_name_value_array.LAST;

        -- Loop from the last key to the first
        WHILE l_key IS NOT NULL LOOP
            -- Replace the tag with the corresponding value
            l_modified_string := REPLACE(l_modified_string, '#' || l_key, l_name_value_array(l_key));

            -- Move to the previous key
            l_key := l_name_value_array.PRIOR(l_key);
        END LOOP;

        RETURN l_modified_string;
    END replace_tags;

     PROCEDURE set_session_state(
        p_item_name IN VARCHAR2,
        p_value     IN VARCHAR2,
        p_app_id    IN NUMBER DEFAULT TO_NUMBER(v('APP_ID'))
    ) IS
        l_count NUMBER;
    BEGIN
        -- Check if the application item exists in the current application
        SELECT count(*)
          INTO l_count
          FROM apex_application_items
         WHERE application_id = p_app_id
           AND item_name = p_item_name;

        -- If the item exists, set the session state
        IF l_count > 0 THEN
            apex_util.set_session_state(p_item_name, p_value);
        END IF;
    END set_session_state;

BEGIN
    -- Log Entry
    apex_debug.enter('set_app_globalization_attributes', 
        'p_app_id' , p_app_id, 
        'p_app_language', p_app_language ); 

    -- Fetch and convert the NLS_TIME Format
    SELECT value, 
           convert_timestamp_mask_to_date_mask( value, 'N' ) converted_value,
           convert_timestamp_mask_to_date_mask( value, 'Y' ) converted_no_seconds
      INTO l_nls_time_format,
           l_nls_time_format_conv,
           l_nls_time_format_no_ss_conv
      FROM v$nls_parameters 
     WHERE parameter = 'NLS_TIME_FORMAT'; 

    -- Store the name/value pair in the array    
    l_name_value_array('TS') := l_nls_time_format_conv;
    l_name_value_array('TS_NO_SECONDS') := l_nls_time_format_no_ss_conv;

    -- Call the local procedure to fetch globalization attributes
    fetch_globalization_attributes(p_app_id, p_app_language, l_glob_attr);

    -- Replace tags in l_glob_attr.date_time_format using the name/value array
    l_glob_attr.date_time_format := replace_tags(l_glob_attr.date_time_format);

    -- Set the session state using apex_util.set_session_state with CUSTOM_ prefix and uppercase
    set_session_state('CUSTOM_DATE_FORMAT', l_glob_attr.date_format);
    set_session_state('CUSTOM_DATE_TIME_FORMAT', NVL(l_glob_attr.date_time_format, l_glob_attr.date_format || ' ' || l_nls_time_format_conv));
    set_session_state('CUSTOM_TIMESTAMP_FORMAT', l_glob_attr.timestamp_format);
    set_session_state('CUSTOM_TIMESTAMP_TZ_FORMAT', l_glob_attr.timestamp_tz_format);
    set_session_state('CUSTOM_NLS_COMP', l_glob_attr.nls_comp);

    -- Information Only
    set_session_state('CUSTOM_TS', l_nls_time_format_conv);
    set_session_state('CUSTOM_TS_NO_SECONDS', l_nls_time_format_conv);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- Handle the case where no matching records are found
        apex_debug.message('No matching globalization attributes found for app_id = %s and app_language = %s', p_app_id, p_app_language);
    WHEN OTHERS THEN
        -- Handle any other exceptions
        apex_debug.message('Error in set_app_globalization_attributes: %s', SQLERRM);
END set_app_globalization_attributes;
/

Now add some Application Items

and an After Authentication Application Process to run the Procedure, to set them.

BEGIN  
    set_app_globalization_attributes;
END;

Like this

Change the Globalization Attributes to reference the Applications Items..

Lets create a Regions to see what its done for en-gb

Nice & now Poland (i.e all Other Countries).

What it's done here is to default to the en-gb settings.

Now, to meet the full requirement, I need to set up my table like this

Note that as TS is not directly supported, I created a tag that can be parsed out and replaced by the the result of my new convert_timestamp_mask_to_date_mask function.

All done.. here are my time pickers

en-gb

Going further, you can also use the #TS_NO_SECONDS to remove the seconds from any locale.

Problem Solved.

Right whats next? OMG, Alex...!

Currency Symbols

What Alex said was

When adding FML999G999G999G999G990D00 format on a item, it formats the it to use a $ i.e $1,2345.00 when it should be £1,2345.00.

By the way FML999G999G999G999G990D00 is a glorious format mask for Number formats as its recommended you use it.

FML: No leading spaces, with a minus sign for negative numbers.
999: Digits before the decimal point, allowing up to 15 digits.
G: Group separators (e.g., commas) for thousands.
D: Decimal separator (e.g., period or comma, based on locale).
00: Two decimal places, padded with zeros if necessary.

I'll try to recreate Alex's conundrum in another application.

I recreated it. Good. I observe that every time I enter a value, like 1234, I lose focus of the item and then it sticks the dollar sign in front of it. Let me just change something and...

There, I fixed it. How? well based all I've learnt, all Alex needed to do was to change this application Language to en-gb

We learnt, a few pages up that this setting affects the NLS_TERRITORY which affects the NLS_CURRENCY therefore changing it to the £ symbol.

If I put it back to English (en), I can recreate his Security > Initialization PL/SQL Code

This does have some success with server side code such as Processes and Classic Reports however has no affect on JavaScript side events such as the Amount item.

The Amount item is interesting, as one may believe its bypassing the ALTER SESSION command. Actually it is! the formatting occurs at client side.

Once again the magnificent Karel Ekema points out that it can be changed at client side by using the command

apex.locale.init({currency: {local: '£', dual: '£', iso: 'GBP'}});

However you'll have to run this on every page. Here is my setup running on Page zero.

apex.locale is documented by contains mostly get functions, however calls to init can set many locale configurations.

So between the ALTER SESSION command & apex.locale.init hack, it kinda works. Lets recap though:

Our APEX App is in English (en)
APEX sets the EN language and Territory for Database calls.
APEX set the EN language and Territory on the Client for formatting
Developer set Currency at DB Level right after APEX has already changed it
Developer sets the Client Formatting right after APEX has already changed it
So the App is English (en) but many settings are changed to resemble en-gb

Sounds ridiculous? Absolutely. All we needed to do was to change this application Language to en-gb and all our problems are solved.

Furthermore, currency format masks are great for Columns and Display Values, but not great for items. Let me evidence it.

Its fiddly for the users to type in
Validations like this wont work because you have to apply the format mask each time in comparisons
The amounts get submit in to session with the currency symbol, making it a string rather than a number
Now for the kicker... when I change to Polish, the value is fetched from the Database, but now its in Polish złoty (zł) without any time of currency conversion.

There isn't a CURRENCY datatype in Oracle where where you can store both the amount and the currency. Therefore we have to keep them both separate in 2 columns. This is by far the best approach as not to mix things up

Conclusion

So many things to conclude. I hope you enjoyed this blog and have gained a wrinkle.

Whats' the picture? It's Spofforth Castle, built in the 11th century by William de Percy and reputedly where rebel barons drafted the Magna Carta in 1215. Now in ruins, where me and my son run through playing tig.

Visit Yorkshire!