ORA-0164 in Oracle 8.1.7

I have gotten a real problem for wich I lack a cure, so I hope that some of the experts here may have some advice.

Our system needs to be able to log the progress of things to table even though the transaction has to be rolled back. This was previously done by writing to a pipe, using the dbms_pipe package, and having another proces reading from the pipe, inserting into the table and committing all in one go.

Now with Oracle 8i we have replaced this with autonomous transactions to simplify the setup and improve performance.

But unfortunately we have a number of setups where we use database links to replicate select information from a master server to a small laptop configuration. This laptop can then be detached from the network and do work in the field, and when it comes back on the net, the new data can be replicated back (under program control) to the master server.

In this proces there is logging implied, with the old setup using database pipes that was no problem but now we get ORA-0164 errors all the time.
(cannot mix autonomous and distributed transactiions).

One nit here is that the Oracle 8.1.7 documentation does not even mention this error code, its not in the manual befor Oracle 9.

But the real problem is that I dont see why its generating that error message.
I do understand why Oracle does not want to support distributed autonomous transactions. But why does it not allow a purely local or remote autonomous transaction within a distributed transaction ?

Is this a bug in the 8.1.7 server ? (we use Enterprise edition on Windows NT).

Is there a fix for this problem ?

Is it gone in the 9.2 release ?
(I cannot test that yet, i do not have two installations of 9.2)

I have tried to revert to the old logging method on the laptop, which did not help, now I may have to try for the master Server but that will not be popular, neither with the customer nor with management.

Can any of you come up with an alternative ?

The laptop solution is an add on for the big solution and testing of that on Oracle8i did not take place before a number of customer installations were perfomed using the autonomous transaction logging.

And when I started testing it I had to put in a lot of commits before logging to get rid of the problem in the test setup. Unfortunately that test setup did not use autonomous transaction logging on the master server, so I did not see that problem until the laptop was shipped to the customer.

I sure would have apreciated the Oracle documentation to include a warning about not combining autonomous transactions and distributed transactions, insted of finding out that the server emits undocumented errors.

There is an awfull lot that cannot be done via database links, most of it undocumented, but I had hoped that for every new release the list of cannots would decrease, not increase.

Thank you for any comments.

Greatings from a frustrated developer in Denmark. Jesper Wolf Jespersen

PS. If you want to reply in mail remove the spam from my email address :-) Received on Fri Jul 12 2002 - 15:46:28 CDT