12 Replies Latest reply: Jul 3, 2013 6:53 PM by Steve Stowers - SkillBuilders RSS

    Problem with transaction handling in 4.0.1? ORA-14450

    605749
      Hi,

      sometimes i get a strange behavior in Apex 4.0.1 when i'm using STATIC2-LOV's in item context. In this case it happens from time to time that the following error is being raised:

      Rendering of page item Pxx_xxxx raised the following error: ORA-14450: attempt to access a transactional temp table already in use

      For instance, i create a checkbox item and define the LOV values with the item (STATIC2:;Y), not with the shared LOV's. In this particulare constellation the error occurs. But when i use a dynamic declaration instead (SELECT null, 'Y' FROM dual) everything works just fine.

      But this is not the only time i saw this error. In one of my projects i use global temporary table over database links. This works fine in 3.2.1 but in 4.0.1 i also get the ORA-14450 error. It seems that the remote session always remains open. I can reproduce the error on sqlplus level when i set the procedure call as an autonomous transcation.

      I suppose the transaction handling has changed in APEX 4.0.1? Seems that Apex runs every request in an autonomous transaction now?

      Jens
        • 2. Re: Problem with transaction handling in 4.0.1? ORA-14450
          Patrick Wolf-Oracle
          Hi Jens,

          sounds very strange, because we are not using any autonomous transactions when we populate our temporary template for static inline LOV values.

          You wrote that in another project you use global temporary tables over database links and you get the ORA-14450 as well. Is that reproducible? Does it occur all the time?
          When does this error exactly occur? Does it occur when you populate the global temporary table in a process of type "PL/SQL" or somewhere else?

          Can you create a test case in 4.0.1 where you can reproduce the above problem?

          Can you give use more information about your used database versions (local and the remote)?

          Thanks
          Patrick
          -----------
          My Blog: http://www.inside-oracle-apex.com
          APEX 4.0 Plug-Ins: http://apex.oracle.com/plugins
          Twitter: http://www.twitter.com/patrickwolf
          • 3. Re: Problem with transaction handling in 4.0.1? ORA-14450
            605749
            Hi Patrick,

            yes, the problem is reproducible and it occurs permanently. It seems that the remote session remains open and is blocking the temp table for other dml operations.
            In this particular case the local and remote machine are both XE Universal. But i'm going to build a test environment with a local XE and a remote 10G SE in the next days. Let's see whether the error occurs or not!

            Regards,
            Jens
            • 4. Re: Problem with transaction handling in 4.0.1? ORA-14450, Static LOVs
              810918
              We're having what seems to be the same problem across multiple applications in our department. The problem has been intermittent for us so it's hard to trace. We have recently migrated our applications from 3 to 4.01.

              We get the "ORA-14450: attempt to access a transactional temp table already in use" message when a page tries to access a static list of values.

              I just tried the convert static LOV to shared LOV task for my item in Application Builder and received the same error message, this time generated from Application Builder itself.

              Any advice on which APEX table holds the static LOVs and what might be preventing access to the table? Again, we haven't experienced this problem until we upgraded to 4.

              Thanks,

              Silas Mathes
              • 5. Re: Problem with transaction handling in 4.0.1? ORA-14450, Static LOVs
                810918
                As a workaround, we're converting all of our page level static LOVs to shared static LOVs. Based on a few days of use in production, removing page level static LOVs appears to keep the ORA-14450 error message from appearing.

                We'd still really like to understand what's causing the error.

                We're running Apex 4.0.1.00.03 with Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit on a Solaris installation.

                Silas Mathes
                • 6. Re: Problem with transaction handling in 4.0.1? ORA-14450
                  Patrick Wolf-Oracle
                  Hi Jens,

                  have you been able to reproduce and create a test case for for the ORA-14450?

                  Regards
                  Patrick
                  -----------
                  My Blog: http://www.inside-oracle-apex.com
                  APEX 4.0 Plug-Ins: http://apex.oracle.com/plugins
                  Twitter: http://www.twitter.com/patrickwolf
                  • 7. Re: Problem with transaction handling in 4.0.1? ORA-14450
                    GChierico
                    Hello, we just upgraded to apex 4.0.2 (on 10g) and hit this problem. It's intermittent and we haven't been able to track down what triggers it, but we have never experienced this before while we were on apex 3.

                    the full message is:

                    ErrorRendering of page item X raised the following error: ORA-14450: attempt to access a transactional temp table already in use

                    any ideas?
                    • 8. Re: Problem with transaction handling in 4.0.1? ORA-14450
                      Dietmar Aust
                      Hi Patrick,

                      do you have any suggestions what might be causing this behaviour?

                      We have seen it 5 times on our development instance running APEX 4.0.2 on Oracle 10.2.0.4 (Windows 32 bit).

                      Killing all APEX related database sessions gets rid of the problem for a while. Strange.

                      Cheers,
                      Dietmar.
                      • 9. Re: Problem with transaction handling in 4.0.1? ORA-14450
                        Kev Molyneux
                        Hi,

                        We have recently encountered the same issue

                        "ErrorRendering of page item X raised the following error: ORA-14450: attempt to access a transactional temp table already in use"

                        Page item X is always a checkbox (STATIC:Yes;1)

                        Like Dietmar found, killing all APEX related database sessions temporarily resolves the error however currently it is reoccurring once every month.

                        Was any reason for this error found, is there any pre-emptive fix available to stop users encountering this error?

                        We are running Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Solaris SPARC and ApEx 4.0.1.00.03

                        Cheers
                        Kev
                        • 10. Re: Problem with transaction handling in 4.0.1? ORA-14450
                          Tuur
                          Hi,

                          Recently I'm encoutering the same issue as mentioned in previous posts.
                          The error doesn't occur permanently, but from time to time.

                          Has anyone came up with a solution yet?
                          We are running an Oracle Database 10g Release 10.2.0.4.0 - 64bit installation en Application Express 4.0.2.00.06.

                          Regards,
                          Tuur
                          • 11. Re: Problem with transaction handling in 4.0.1? ORA-14450
                            483969
                            Hi Patrick,

                            until now I tough this problem is somehow related to 10gR2 database - all previous post were mentioning 10.2.x
                            However now I have customer experiencing the same problem with 11gR1 database:

                            APEX throws error:
                            Rendering of page item P301_COPY_MAILING_FROM_HOME raised the following error: ORA-14450: attempts to access a transactional temp table already in use

                            P301_COPY_MAILING_FROM_HOME is a Check box item defined as: STATIC2:Same as Home Address;1

                            SQL trace: the only SQL statement that is inserting into a temporary table is this one ...
                            INSERT INTO WWV_FLOW_LOV_TEMP(DISP,VAL,INSERT_ORDER)
                            VALUES
                            (:B3 , :B2 ,:B1 )
                            Bind values were "Same as Home Address", 1, 1

                            Application Express 4.0.2.00.07
                            Oracle Database 11g Release 11.1.0.7.0 - 64bit installation (Windows)

                            Jan
                            • 12. Re: Problem with transaction handling in 4.0.1? ORA-14450
                              Steve Stowers - SkillBuilders

                              Hi;

                               

                              We're experiencing similar problems.  Running 4.01 on 11.2 and starting to see ORA-14450 error on multiple pages.  The exact error is as follows:

                                 

                              ORA-14450: attempt
                                to access a transactional temp table already in use

                               

                              error_performing_validations

                               

                              Not sure how long this has been happening.  A user actually received the following msg and is the only reason we know:

                               

                              Error

                              Rendering of page item P300_ANNUAL_ACTIVE_INACTIVE raised the
                                following error: ORA-14450: attempt to access a transactional temp table
                                already in use

                               

                              The page item in the error message is a static2 select list.  We also receive this error on several other pages which contain combinations of text fields, radio buttons and text areas.

                               

                              We are not using any global temporary tables in the app.  Any advice, etc. greatly appreciated.