11 Replies Latest reply: Dec 19, 2011 9:39 AM by Peeteba RSS

    Error ORA-20001 at Import of an APEX Application

    Martin1
      Hi,

      when i import an APEX application into the production environment i get the following error:

      Error     GET_BLOCK Error.
      ORA-20001: Execution of the statement was unsuccessful. ORA-02047: cannot join the distributed transaction in progress <pre>begin execute immediate 'begin dbms_session.set_nls( param =&gt; ''NLS_NUMERIC_CHARACTERS'', value =&gt; '''''''' || replace(wwv_flow_api.g_nls_numeric_chars,'''''''','''''''''''') || ''''''''); end;'; end; </pre>

      The application was before exported from the develepment environment.

      Both environments has the APEX version 4.1.0.00.32.

      When i drop the following PL/SQL statement at the end of the export file the import works - what's wrong?

      begin
      execute immediate 'begin dbms_session.set_nls( param => ''NLS_NUMERIC_CHARACTERS'', value => '''''''' || replace(wwv_flow_api.g_nls_numeric_chars,'''''''','''''''''''') || ''''''''); end;';
      end;
      /

      Has anybody an idea?

      Best Regards,
      Martin
        • 1. Re: Error ORA-20001 at Import of an APEX Application
          Peeteba
          Hi Martin,

          This question might sound completely off-topic but are you using heterogeneous services somewhere in apex application on that server?

          We had the same issue last week too.
          Here is what caused the problem and how we solved it:

          In one application, we had some views that selected data from an SQL server database over a database link using heterogeneous services.
          Because we selected data from this views in apex, we got this problem.

          What is the reason behind this? (I don't know every technical detail so I hope it's clear enough)
          Every session from the specific application opens the database link to SQL server but for some reason this db link never closes.
          Apex re-uses its sessions over applications over and over so after a while all apex sessions have an open database link, also those sessions which never requested data from SQL server.
          This technique is called session pooling (you will find a lot of things about this on Google and this forum).

          If you want to upload a file, an error is raised when a database link is still open.
          This might sound strange but there is a connection between uploading a file and the open database link (if someone can give this technical explanation, this would be helpful).

          What solved this issue in our case?
          We replaced all views by materialized views.
          Hereby, the data is collected from SQL server by another session than a session used by apex.
          This way, the session, and the database link, used to collect data from SQL server is closed immediately after collecting all data, which resolves the issue in apex.

          Hopefully this explanation is clear enough for you.
          Or maybe can someone else clarify the solution a little more.

          I'm not telling you are having the same problem too but the error is known to have to do something with database links and heterogeneous services.

          Regards,
          Bart
          • 2. Re: Error ORA-20001 at Import of an APEX Application
            Martin1
            Hi Bart,

            thanks for your reply.
            Yes we have DB Links. But what's it all about NLS_NUMERIC_CHARACTERS ... ?

            Regards,
            Martin
            • 3. Re: Error ORA-20001 at Import of an APEX Application
              Patrick Wolf-Oracle
              Hi Martin,

              if the session which is picked from the session pool has an open database link, all operations which change the context of the current session (like NLS parameters), have to be done on both databases. That's why you get the error.

              In APEX 4.1 your applications which are using DB Links should use something similar to the code below to close open db links of the session.
              Put the code into "Cleanup PL/SQL Code" (Edit Application Properties -> Security -> Database Session)
              begin
                  for l_db_link in ( select db_link as name
                                       from v$dblink )
                  loop
                      dbms_session.close_database_link(l_db_link.name);
                  end loop;
              end;
              Regards
              Patrick
              -----------
              My Blog: http://www.inside-oracle-apex.com
              APEX Plug-Ins: http://apex.oracle.com/plugins
              Twitter: http://www.twitter.com/patrickwolf
              • 4. Re: Error ORA-20001 at Import of an APEX Application
                Martin1
                Hi Patrick,

                thanks for your reply - i can imagine that this solves the problem.
                In the next days when i'm on site at my customer i will check it and give you feedback.

                Best Regards from West Austria
                Martin

                PS: I can't see 'Database Session' in 'Shared Components' --> 'Edit Security Attributes'. There i have only the following topics:
                Show All, Authentication, Authorization, Database Schema, Session Timeout, Session State Protection and Virtual Private Database. Database Session there is missing.

                Edited by: Martin1 on Dec 19, 2011 8:17 AM
                • 5. Re: Error ORA-20001 at Import of an APEX Application
                  Peeteba
                  Hi Patrick,

                  This "Database Session" section in the Security tab, is this something new in APEX 4.1?
                  I haven't seen this in earlier versions.
                  I don't find anything about this in the user guides either (or I'm just not looking hard enough).

                  Is it a good idea to open the DB link in the "Initialization PL/SQL Code" and close the link in "Cleanup PL/SQL Code"?
                  Or do I only need to close it after I used it?

                  I our application, I think this approach could work a lot easier too.

                  EDIT: Found it in the Application Builder User's Guide under "About Application Attributes"

                  Regards,
                  Bart

                  Edited by: Peeteba on Dec 19, 2011 10:22 AM
                  • 6. Re: Error ORA-20001 at Import of an APEX Application
                    Patrick Wolf-Oracle
                    Hi Bart,

                    yes "Database Session" is new in 4.1, before it was called Virtual Private Database (VPD) and it was only possible to set something during initialization of the session, but not to cleanup something as soon as the DB session is put back into the session pool.

                    Actually you don't have to open the DB links in "Initialization PL/SQL Code", it will automatically be done if for example a report is querying a table from a remote database. Keep also in mind that not every page will probably use the database link so it would be wast of resources if you open it all the time. It's just important to close open links in the case the current session has opened one.

                    Regards
                    Patrick
                    -----------
                    My Blog: http://www.inside-oracle-apex.com
                    APEX Plug-Ins: http://apex.oracle.com/plugins
                    Twitter: http://www.twitter.com/patrickwolf
                    • 7. Re: Error ORA-20001 at Import of an APEX Application
                      Peeteba
                      Patrick,

                      I tried your solution and it really works fine!

                      Only, I got an issue on pages where I don't use the database link.
                      I got the error 'ORA-02081: database link is not open'.

                      To check whether the DBlink is open or not, I created a function in SYS which performs a query on v$dblink.
                      I had to do this in SYS because my application scheme has insufficient privileges on this v$ view.
                      The function works fine and now the error is no longer in my application.

                      Except: our DBA is not so happy with this.
                      He does not want custom functions or procedures in the SYS schema.
                      For the development database it's no problem but he won't allow it in production.

                      Is there an Oracle provided package or function to check whether a database link is open or not?
                      DBMS_SESSION provides a close_database_link function but this doesn't perform a check first.
                      Maybe you have another approach for this?

                      Regards,
                      Bart
                      • 8. Re: Error ORA-20001 at Import of an APEX Application
                        Patrick Wolf-Oracle
                        Hi Bart,

                        instead of creating a custom function in sys I would suggest that you just grant the v$dblink view to your schema so that you can use it in your application.

                        I don't really understand anyway why this view isn't granted by default, because it will only show you the open database links for your own session.

                        Regards
                        Patrick
                        -----------
                        My Blog: http://www.inside-oracle-apex.com
                        APEX Plug-Ins: http://apex.oracle.com/plugins
                        Twitter: http://www.twitter.com/patrickwolf
                        • 9. Re: Error ORA-20001 at Import of an APEX Application
                          Peeteba
                          Hi Patrick,

                          I already tried to grant select on this view with SYS (as sysdba) but an error returns: ORA-02030: can only select from fixed tables/views.

                          There is a way to query v$dblink by another user than SYS but then the user needs the SELECT ANY TABLE privilege.
                          And this privilege is a little too powerful to grant just for querying 1 v$ view :-)
                          That's why I chose the way with the function in SYS.

                          Regards,
                          Bart
                          • 10. Re: Error ORA-20001 at Import of an APEX Application
                            Patrick Wolf-Oracle
                            Hi Bart,

                            have a look at http://coskan.wordpress.com/2007/03/07/how-to-grant-to-v-views/

                            Regards
                            Patrick
                            -----------
                            My Blog: http://www.inside-oracle-apex.com
                            APEX Plug-Ins: http://apex.oracle.com/plugins
                            Twitter: http://www.twitter.com/patrickwolf
                            • 11. Re: Error ORA-20001 at Import of an APEX Application
                              Peeteba
                              Thanks Patrick,

                              This makes it a lot easier!

                              Regards,
                              Bart