Forum Stats

  • 3,814,511 Users
  • 2,258,880 Discussions
  • 7,892,761 Comments

Discussions

Problem with transaction handling in 4.0.1? ORA-14450

605749
605749 Member Posts: 29
edited Jul 3, 2013 7:53PM in APEX Discussions
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
«1

Answers

  • Patrick Wolf-Oracle
    Patrick Wolf-Oracle Consulting Member of Technical Staff Posts: 2,455 Employee
    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
  • 605749
    605749 Member Posts: 29
    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
  • 810918
    810918 Member Posts: 2
    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
  • 810918
    810918 Member Posts: 2
    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
  • Patrick Wolf-Oracle
    Patrick Wolf-Oracle Consulting Member of Technical Staff Posts: 2,455 Employee
    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
  • GChierico
    GChierico Member Posts: 22
    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?
  • Dietmar Aust
    Dietmar Aust Member Posts: 1,939 Bronze Badge
    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.
  • Kev Molyneux
    Kev Molyneux Member Posts: 2 Blue Ribbon
    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
  • Tuur
    Tuur Member Posts: 23
    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
This discussion has been closed.