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

605749

Anyone?

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

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

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

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

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

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?

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.

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

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