ApEx 4.0: ORA-001821 randomly showing up

This week, I had a problem with ApEx 4.0 where ORA-001821 was randomly showing up, not only in the applications but also in the ApEx Builder.

ORA-1821

The problem was that we queried a table across a database link but to a database version prior to 10gR1. Please note that other applications will also suffer from this one application where you used this database link.

Oracle describes the problem as:

In Application Express 4.0, the internal applications were modified to have an application date format of ‘DS’. The ‘DS’ date format was first introduced in database version 10gR1.

With the application date format of ‘DS’, each page view has the database session parameter NLS_DATE_FORMAT set to ‘DS’ by the APEX engine. Then, when a query is performed against the remote database (9iR2 or earlier), the remote database is unable to handle the request and says that ‘DS’ is an invalid value for NLS_DATE_FORMAT.

You can already download a patch that solves the problem, in Oracle support search for “note 1155453.1 – ORA-1821 When Querying a Table Across A Database Link After 4.0 Upgrade