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.


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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

About Olivier Dupont