DBMS_JOB, Distributed transactions and logging.

From: Alex Vilner <alex_at_sinoma.com>
Date: 12 Sep 2002 06:44:44 -0700
Message-ID: <22e9f6e0.0209120544.7206e5a9@posting.google.com>

Hi,

We have a classic distributed transaction scenario. A local 8i (8.1.7) server initiates the transaction, does some processing, calls a remote procedure on another 8i server over the DB link... Both are running on Solaris.

We are aware of transaction control not working on the remote side in distributed transactions, but we have not much control over the local server processes. The task we are faced with is to log errors on the remote server, and keep them logged (in tables) even if the transaction rolls back.

The approach was to try and use DBMS_JOB in the exception block of the remote procedures. However, it seems that DBMS_JOB does not add a job to the queue unless there is a commit OR unless DBMS_JOB.RUN (implicit commit) is called. Is there a workaround this issue?

Or, alternatively, is there a way to have this work: LOCAL:
BEGIN
  remote_proc_at_db_link;
EXCEPTION
  WHEN OTHERS THEN

     ROLLBACK;
     RAISE;

END; REMOTE:
BEGIN
  <do something>
EXCEPTION
  WHEN OTHERS THEN
<log into a table>
<PRAGMA AUTONOMOUS_TRANSACTION doesnt work with distributed
transactions>

     RAISE; -- back to local
END; It seems as if it is such a commonly needed task... How do others do it?
Thank you in advance! Received on Thu Sep 12 2002 - 08:44:44 CDT