We've run across the same error and have traced it to the NLS_DATE_FORMAT parameter in the session.

Running select * from v$nls_parameters in SQL Workshop shows NLS_DATE FORMAT set to DS at the session level, though the database parameter shows DD-MON-RR.

After running alter session set NLS_DATE_FORMAT='DD-MON-RR' the database link no longer gave the date format error.

My question is where does Application Express pick up the NLS_DATE_FORMAT parameter value at the session level? We're using Oracle HTTP Server, but I've dug through all the configuration files and can't find anything. Also, the error causes any attempts to update a report that goes across the db link to fail while parsing.

I have to say we are getting exactly the same errors with db links after upgrading to APEX 4.0. Report regions are not working and it is also close to impossible to update them (queries can not be parsed). Any help?

Hey,

we have recently updated to Apex 4.0 from 3.2.1 on 11g and are using the embedded pl/sql gateway and have the same problem. We are running a query accross several db links for a select list page item and are not selecting any date fields but get this error.

When I run select * from v$nls_parameters in the SQL workshop I get a value of DS for NLS_DATE_FORMAT however get a value of DD-MON-RRRR when run via the database that apex is running on.

Any help will be greatly appreciated.

Thanks,
B.

I understand the issue. In Application Express 4.0, we changed the default date format in the development environment to be 'DS'. This is the locale-specific date format, and it will vary based upon the language setting of Application Express itself. Thus, you will now get dates formatted appropriately based upon your language preference instead of the DD-MON-RR Oracle default, which no one really uses.

Now the downside of this is that the NLS environment of the database session sets NLS_DATE_FORMAT to 'DS', but since the 'DS' date format wasn’t introduced until database 10gR1, a 9iR2 database (or earlier) doesn’t know anything about this DS date format. So a query to a table across a database link to a remote 9.2.0.5 database from SQL Commands or in the development environment will fail.

This shouldn't affect the execution of your application as long as your application doesn't use an application date format of 'DS'. But it does impact your use of the development environment against these pre-10gR1 databases.

Oracle Support has contacted me with a customer SR on this issue. I'll have them file a bug and then devise a work around or a way to back this change out in your respective development environments.

Joel

I am experiencing the exact same issue. I can't run queries in sql commands or embed the sql in an interactive report. Eager to know the workaround.

A patch which correct this issue is available on MetaLink via Patch Number 9925124. Please feel free to let me know if this does not resolve your issue (or if it creates new ones).

I apologize for the issues this has caused.

Joel

Joel,

will this patch work with version 11.2.0.1.0 of the database?

Thanks,
B.

Barney,

It will work with all supported database versions - 10.2.0.3 and later.

I realize the actual patch zip file is named p9925124_11106_GENERIC.zip, but unfortunately, we have to associate some database version with this one-off patch. Regardless, all one-off patches for Application Express are for all supported database versions. And in the case of Application Express 4.0, this is DB version 10.2.0.3 or higher.

Joel

Quick question:

I thought I saw in another thread that installing one of the patches caused data to be lost? Can you confirm (a) that's not a risk w/this patch, and (b) is it a risk w/other another patch, or it was not a risk, or the patch was patched?

Thanks very much. So far we love 4.0

Michael,

+>> I thought I saw in another thread that installing one of the patches caused data to be lost?+

Really? What thread? That doesn't sound right.


+>> Can you confirm (a) that's not a risk w/this patch,+

Not a risk whatsoever. Feel free to examine the patch script itself. It won't mean much to you, but it's a simple update of underlying meta data of the shipped APEX applications, like Application Builder and SQL Workshop.


+>> and (b) is it a risk w/other another patch, or it was not a risk, or the patch was patched?+

No risk applying this whatsoever. It won't collide with any other patch for APEX 4.0.



And for those who are wondering, these individual patch set exceptions will be rolled into the APEX 4.0.1 patch set - when that's released.

I hope this helps.

Joel