Re: DBMS_JOB, Distributed transactions and logging.

Research autonomous transactions. You can write a generic error log procedure that you will call in exception handler, and this procedure will log error in an autonomous transaction and commit it (while the outer transaction may rollback at will). To declare a procedure transaction scope autonomous you use
PRAGMA AUTONOMOUS_TRANSACTION
in procedure declaration block.

hth.

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Alex Vilner" <alex_at_sinoma.com> wrote in message
news:22e9f6e0.0209120544.7206e5a9_at_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!