This is the second part of a series of blog articles titled “Things in APEX I’ve used for years, but not fully understood how they work.” The first part was Sorting in APEX: A deep dive into Classic Reports and Card Regions, published a couple of weeks ago.

Since – by the series very definition – I didn’t fully understand how these features work, I learned as I delved into this. This means there may be some errors or omissions, so if you spot any, please let me know, and I’ll correct them.

Sources

The following sources were instrumental in writing this:

• 3.10.4 Using Built-in Substitution Strings
• Strange Item Formatting
• Format Models
• New Date Picker documentation
• 16.7 Managing Component Settings
• JSDoc – Namespace: date
• Where do you specify the Date Format Mask
• Unicode and Oracle SQLcl…on Windows
• What does ‘Skip NLS Settings’ really do?

Some background story

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

The truth is… back then, I didn’t know enough about Globalization to give Alex 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. 🙏 But now, I’m back to avenge past sins with a thorough walkthrough of the Globalization settings – and I’m going to answer not only Alex’s question (finally) but also a few more that you, as a reader of this article, might have. However, in traditional Matt style, the route to these answers will be quite long.

Demo application

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

Note that 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 (pl) language application.

I then follow one blog article to create a Stylized Language Selector and another one to create an automatic seed and publisher. With this, my app is up and running.

If you want to learn more about translating APEX applications, I encourage you to read this blog article by my colleague Rafał Grzegorczyk (I won’t cover the translation process here).

A word of warning: the 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 article, I noticed the problem is bigger than I thought – changes you make in the Primary Application will not be instantly reflected in the Primary Application if any Translated Applications share the same language as the Primary Application. You want proof? Well, my Primary Application is in English (United Kingdom), and if I create a translated application in English (Trinidad), 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 clicks a URL with a special p_lang argument). However, there are other ways of deriving the language.

If Browser Detection is used, a browser set in a locale, e.g., Spanish (Venezuelan), won’t default 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 the console:

navigator.language

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

However, as we’ll find out later, the locales – e.g., Venezuelan (es-ve) – control the number and date formatting.

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

In the picture above, my Primary Application is in English (United Kingdom) (en-gb). However, two Spanish locales are shown. There are actually twenty Spanish locales, e.g., Chile, Peru, Mexico, etc. From a developer’s point of view, supporting all these languages 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, choose the locale where most users reside. Otherwise, choose 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 and pt-br
• An English Language application for users in the Netherlands – use en
• The apex.oracle.com portal, designed for users spread across the world, uses en for the Primary Language and de, es, fr, it, pt-br, zh-cn, zh-tw, ja and ko for translated applications
• Can’t decide? Choose a base language code en, es, pl, etc.

Note that APEX applications want to be translated into languages, not into variants of that base language (I’m looking at you, Español 🇪🇸). If you choose several variants, languages will get repeated on the selection bar, as I’ve shown you above using Español as an example.

Also, before continuing, we have to take a step back to talk about how NLS (National Language Support) is handled at the Database level.

Various Oracle NLS parameters

NLS parameters are used to define the language, territory, and character set settings for a session or the entire database. To keep this dead simple, let’s say there are two sets of NLS Parameters, those at a Database level and those that can be changed at a Session level (and frequently are – like ~100% of the time).

Database level (nls_database_parameters)

These are the Database Parameters and cannot be changed without an ALTER DATABASE command. In other words, if you ever need to change these, you really need a “sit-down meeting” to discuss the changes (wow, I haven’t used that phrase for years – I must resurrect it).

Let’s see what they look like in my Autonomous Database:

SELECT PARAMETER, VALUE
  FROM nls_database_parameters
ORDER BY 1;

As you can see, it’s a very American-style configuration. Even though I am located in the UK, there is zero evidence of the 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 – 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 you 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 will refer to this a few times in this article 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, if you do this, 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? It’s something I sorely missed while writing this article.

Now, let’s look at this page in detail.

Globalization Date Settings

The DS Format mask stands for Date Short, and the documentation describes it with 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 baffling things in this statement:

• The locales AMERICAN_AMERICA and ENGLISH_UNITED_KINGDOM are not actually a thing. It’s a lazy way of specifying two parameters in one, and it confuses the reader into thinking there is a single command to set both Language and Territory. Instead, the reader has to run a double-command like this:

  ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN' NLS_TERRITORY = 'AMERICA';

• It’s saying it depends on two parameters? Now, let’s see about that 🧘‍♂️DS 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, nevermind 🤷

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 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. While it’s perfectly legit for users to type into Date Pickers, in my opinion, that’s awkward for the users.

The quote also provides two 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 a picture that’s worth a thousand words:

Four date formats

The help text is useful, but the documentation is even “useful-er.” You can read the quotes below 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 returns 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”.

Let’s summarize it all in a table:

APP_ parameters

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 later). For example, If we have the 4 Date Parameters set as below…

… this is how it influences 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 it’s 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? There are two options:

• 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 follows:

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

Why did APEX think it was 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: 

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.

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

However, I did some research, and apparently it’s correct.

Finally, its been confirmed that the NLS_TIME_FORMAT has been butchered to make it into an 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 a fact. Why do they do this? Are they trying to help? Does it really help? I guess so – it means that 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 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 the one you select when you enter APEX.

Whenever a locale is selected or defaulted, 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 it’s 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. Here’s proof: 👇

• SQL*Plus

This is permanently knackered on my machine, so let’s 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 that, 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 there are 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, 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. 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 the 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

Changing 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 it’s ENGLISH)
  • See the 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 uses , 
• 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 the first line of the picture below. Note: this isn’t the first line of the debug. It’s about 15 lines down.

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. What’s 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. Then, the sort order would be:

Ángel comes third because in Spanish, Á is treated as A, and the rest of the word follows standard 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 APEX

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

The top setting is the NLS_SORT, and the bottom one is 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 it wasn’t a performance concern).

Boost your time zone knowledge

The United Kingdom has only one time zone 🎉, so it’s been too easy for me to become complacent with my time zone 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 it’s set to UTC, which stands for Coordinated Universal Time (I expected it would be called CUT, but UTC is a historical 1960s compromise of English and 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.

Let’s 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 it’s the Europe/Paris time zone (which is the same 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, I get the following result:

What the heck? The Current Date and 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 reloading the page. Basically, it works out the time zone using getTimezoneOffset and then redirects the URL, passing the tz parameter to the page in the format of HOURS:MINS (see below).

Once it completes, it 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 it’s not.

As I’ve proven above, it isn’t 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 this – lang is also misleading, with the correct parameter being p_lang.

How to explain this?

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 enable Automatic Time Zone, then APEX grabs your clients’ (i.e. your Browsers’) time zone to work out where in the world you’d like your time zone presented. I can evidence this by observing APEX changing this in the debug (see the first line):

Question: is this the end of SYSDATE?

In a multi-timezone application, SYSDATE is not your friend, as it’s always the same as 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-timezone application, SYSDATE is fine. It will adapt to daylight saving time, too, as your server’s clock changes.

Question: any more bad news?

Brace yourself: it’s probably the end of DATE column types too. 🥀This is because, in APEX, when a user submits the page, they need to store user Dates/Times from all kinds of time zones. 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 the time zones, so you have to fool the display somehow.

Even if you wanted to store the dates along with the time zone, the DATE column types don’t support this…. however, a TIMESTAMP column type can!

Therein lies the answer 👉 TIMESTAMP WITH LOCAL TIME ZONE:

• It doesn’t 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 time 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

Example: TIMESTAMP WITH LOCAL TIME ZONE

Let’s 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
);

I add a record with a date in APEX:

Now, when I switch my PC’s time zone to Osaka…

… and sign out and back into APEX (BTW, the APEX time zone is set per session) and then reload the page – the same record has been resolved to Osaka time:

Important: APEX Automatic Timezone needs to be On for this to work.

Now, if I add another event, this time in Osaka time:

Then, in my SQLcl Warsaw client, I can see those Osaka dates/times as they relate 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, unless they have a Format Mask specified, Date Pickers always use the Application Date Format (APP_NLS_DATE_FORMAT).

But why don’t Date Pickers simply use the Application Date Time Format? Well, Date Pickers don’t always use the time element. Most Date Pickers just –  wait for it! – pick… the… date… (yes, the clue is in the name).

So, if we wanted to specify the time, we need the Show Time enabled (as shown above) and we have to supply a Format Mask.

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

Of course, we can use a hard-coded Format Mask for Date Pickers – however, we are at risk of losing the 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, let’s experiment with DS TS and see how it actually works:

TO_CHAR(SYSDATE, :APP_DATE_TIME_FORMAT );

Here are the results:

It’s pretty awesome because, with DS TS, we set it once and use it throughout our application and all translated apps. Let’s try it on a Date Picker with time shown.

Now, when we run the page:

Oh snap… and there goes my unbelievably amazing idea! What’s happening here? The Date Picker doesn’t support some elements, including TS. The documentation that explains which parts are supported is here, but you won’t find what you are looking for there – at least not exactly. So, what’s going on? I believe that the undocumented item.Datepicker.js library relies heavily on the apex.date library and that has some support shortcomings. 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.”

Eh! Unfortunately, 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, and the butcher-function kicks in. If we leave it blank, we cannot specify a custom Date Portion. This table summarizes this issue:

One absolutely huge problem we have with the APEX Globalization Attributes page is that once we move away from 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. However, don’t panic – I have a solution which I’ll show you in a moment.

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 Date Picker, which looks great for our American friends.

However, that time portion is a 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. And what do you see in the item Value? Seconds! The Format Mask is &APP_DATE_TIME_FORMAT., and it’s using the default globalization Attributes.

Is this wrong? Well, yes and no (which isn’t really an answer, I know). Yes, because the Format Mask contains seconds – and therefore, it should show them. But also no, because the Date Time Picker doesn’t allow the user to pick seconds – which means 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 seconds… but we know that 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? Well, they usually don’t… You wouldn’t really expect users to select a precise second, would you? But…

The locale-insensitive method

What if we had 1919 Date Pickers that did have seconds and 1818 ones that didn’t have them? And we now only have one 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, which 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 article, using the DS #TS_NO_SECONDS. Just keep reading – we’ll get there.

Using a custom APP_DATE_TIME_FORMAT 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 that:

“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 allowance for colons in the extended format.

Back to our example, let’s say the standard, across the whole world, is a time format of HH24:MI.

This seems to be the best that we can do (or HH24:MI:SS if we want seconds). Let’s try a Globalization setting of:

And use this with a Date Picker Format Mask of:

There we go. 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 Validation (returning Boolean).

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, we want the time delimited by dots”… Oh my gosh, then our beautiful multi-lingual, multi-globalized application will need a hack! 

Universal globalized format

Let’s expand on our requirements and add some more locales:

Things we already know

• There’s only one set of Globalization Attributes for all translated applications
• We can use the 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 hardcode 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. Let’s 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 create our own function that does the same thing as the butcher function – but better (or we can just ask ChatGPT to do it, lol).

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… and 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 add 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:

Let’s create a Region to see what it does for en-gb:

Nice – and now Poland (i.e. all other countries).

What happens here? It defaults 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 result of my new convert_timestamp_mask_to_date_mask function.

All done… here are my time pickers:

• en-gb

• th

• gd

• pl

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

Problem Solved. Right, what’s next? OMG, Alex…!

Currency symbols

What Alex said was

“When adding FML999G999G999G999G990D00 format on an item, it formats 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 it’s 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:

There, I fixed it. How? Well, based on all I’ve learnt, all Alex needed to do was to change this application’s language to en-gb.

We learnt 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, it has no effect on JavaScript-side events such as the Amount item.

The Amount item is an interesting case, as one may believe it’s bypassing the ALTER SESSION command. And, actually… it is! The formatting occurs on the client’s side. Once again, the magnificent Karel Ekema points out that it can be changed on the client’s side by using this 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:

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

So between the ALTER SESSION command and apex.locale.init hack, it kinda works. Let’s recap:

• 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 sets 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’s language to en-gb, and all our problems were solved.

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

• It’s fiddly for the users to type in
• Validations like this won’t work because you have to apply the Format Mask each time in comparisons

• The amounts get submitted into the 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 it’s in Polish złoty (zł) without any time of currency conversion

There isn’t a CURRENCY data type in Oracle that could store both the amount and the currency. Therefore, we have to keep them separate in two columns. This is, by far, the best approach if you don’t want to mix things up.

Conclusion

So many things to conclude, I just don’t know where to start… I hope you enjoyed this article and have gained a wrinkle or two while reading it. I also hope you learnt a thing or two about APEX Globalization settings, along with me. If you’re interested in more APEX-related content, check out some of the other articles on our blog:

1. The #TIMING# substitution variable problem in Oracle APEX and how to solve it with the Pretius Developer Tool
2. Build an OCI GenAI chatbot with PyCharm & RAG, and get ready for passing the Generative AI Professional Certification by Oracle
3. Sorting in APEX: A deep dive into Classic Reports and Card Regions
4. Oracle APEX deprecated APIs: How to spot them with a simple regex search
5. Biscuits+ChatGPT: Using AI to generate Oracle APEX Theme Roller Styles