12 Replies Latest reply on Jul 21, 2010 4:37 PM by joelkallman-Oracle

    4.0 Database Link error

    rw
      I get an error when setting up a database link in XE and Apex 4.0 (this works fine in 3.2).

      Error Testing Database Link.
      ORA-01821: date format not recognized ORA-02063: preceding line from

      The test SQL is: select 1 from dual@

      Has anyone else seen this problem?

      Thanks
      -Arnold
        • 1. Re: 4.0 Database Link error
          783388
          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.
          • 2. Re: 4.0 Database Link error
            rw
            Thanks for the reply. These are my settings...

            SELECT * from NLS_instance_PARAMETERS;

            NLS_DATE_FORMAT DD-MON-RR


            SELECT * from NLS_database_PARAMETERS;

            NLS_DATE_FORMAT DD-MON-RR


            SELECT * from NLS_session_PARAMETERS;

            NLS_DATE_FORMAT DS

            alter session set NLS_DATE_FORMAT='DD-MON-RR'

            No error from running the alter sql but in my set up this does not actually change the NLS_DATE_FORMAT value which is still set to DS. Am I missing some priviledges or is there another way to change this at the session level?

            Even if I manage to change it to DD-MON-RR at the session level does this mean the change would only be kept for the current login?

            The strange thing is that creating a database link to a different database works fine.

            The database link that fails is to the Oracle eBusiness HR system.

            Any other ideas to solve this V4.0 bug?

            Thanks
            • 3. Re: 4.0 Database Link error
              tine77
              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?
              • 4. Re: 4.0 Database Link error
                774932
                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.
                • 5. Re: 4.0 Database Link error
                  joelkallman-Oracle
                  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
                  • 6. Re: 4.0 Database Link error
                    michaelgb
                    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.
                    • 7. Re: 4.0 Database Link error
                      joelkallman-Oracle
                      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
                      • 8. Re: 4.0 Database Link error
                        774932
                        Joel,

                        will this patch work with version 11.2.0.1.0 of the database?

                        Thanks,
                        B.
                        • 9. Re: 4.0 Database Link error
                          joelkallman-Oracle
                          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
                          • 10. Re: 4.0 Database Link error
                            rw
                            Thanks Joel

                            Applied the patch and it worked.

                            Regards
                            Arnold
                            • 11. Re: 4.0 Database Link error
                              michaelgb
                              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
                              • 12. Re: 4.0 Database Link error
                                joelkallman-Oracle
                                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