Autonomous transactions in distributed database

[Razvan]

Hi all,

Can somebody tell me if it is posible to use autonomous transactions in a
distributed database in Oracle 8i?
Actually, what I am trying to do is to start an autonomous transaction from
a PL/SQL stored procedure (considered as the main transaction) in order to
retrive all the errors which may occur and commit them independently, whether
or not the main transaction comits.
This works fine on local. But if I call the stored procedure remotely from
another database, via a DB link, this doesn't work anymore.
Do I have an alternative to this?

Thank you for any answer.

[Boris Milrud]

Razvan,

Unfortunately, autonomous transactions are not supported in 8i as part of
distributed transactions.

Alternative: how about accumulating all error information in PL/SQL table
inside the package, and later persist it to the database table after commit
or rollback occurs in your main transaction.

Boris Milrud.

"Razvan" <razvani@hotmail.com> wrote:
>
>Hi all,
>
>Can somebody tell me if it is posible to use autonomous transactions in
a
>distributed database in Oracle 8i?
>Actually, what I am trying to do is to start an autonomous transaction from
>a PL/SQL stored procedure (considered as the main transaction) in order
to
>retrive all the errors which may occur and commit them independently, whether
>or not the main transaction comits.
>This works fine on local. But if I call the stored procedure remotely from
>another database, via a DB link, this doesn't work anymore.
>Do I have an alternative to this?
>
>Thank you for any answer.

[Razvan]

First I want to thank you for your answer.

This was also the alternative I considered first. But the problem is that
the error record is quite a long one (aprox. 2200 characters). I am afraid
not to encounter an overflow in case of too many errors, as the PL/SQL tables
are kept in the memory.
I was wondering whether it is possible to start another session (an independent
one) from inside the actual session, only to record the errors, something
similar as an autonomous transaction can be started from a main transaction.

Thank you again.
Razvan IONESCU

"Boris Milrud" <milrud@hotmail.com> wrote:
>
>Razvan,
>
>Unfortunately, autonomous transactions are not supported in 8i as part of
>distributed transactions.
>
>Alternative: how about accumulating all error information in PL/SQL table
>inside the package, and later persist it to the database table after commit
>or rollback occurs in your main transaction.
>
>Boris Milrud.
>
>"Razvan" <razvani@hotmail.com> wrote:
>>
>>Hi all,
>>
>>Can somebody tell me if it is posible to use autonomous transactions in
>a
>>distributed database in Oracle 8i?
>>Actually, what I am trying to do is to start an autonomous transaction
from
>>a PL/SQL stored procedure (considered as the main transaction) in order
>to
>>retrive all the errors which may occur and commit them independently, whether
>>or not the main transaction comits.
>>This works fine on local. But if I call the stored procedure remotely from
>>another database, via a DB link, this doesn't work anymore.
>>Do I have an alternative to this?
>>
>>Thank you for any answer.
>

[Boris Milrud]

Razvan,

I don't think that your solution is possible. However, I've got another 2
solutions for you:

1. You can use autonomous transaction inside the trigger in remote database.
Then your trigger code would save an error and commit it regardless of the
results of main transaction.

2. You could use DBMS_PIPE package to send errors to another session via
pipe, and that session would commit it: in a way, that session would be acting
as autonomous transaction.

What do you think? Let me know how it will work for you.

Boris Milrud.

"Razvan" <razvani@hotmail.com> wrote:
>
>First I want to thank you for your answer.
>
>This was also the alternative I considered first. But the problem is that
>the error record is quite a long one (aprox. 2200 characters). I am afraid
>not to encounter an overflow in case of too many errors, as the PL/SQL tables
>are kept in the memory.
>I was wondering whether it is possible to start another session (an independent
>one) from inside the actual session, only to record the errors, something
>similar as an autonomous transaction can be started from a main transaction.
>
>Thank you again.
>Razvan IONESCU
>
>"Boris Milrud" <milrud@hotmail.com> wrote:
>>
>>Razvan,
>>
>>Unfortunately, autonomous transactions are not supported in 8i as part
of
>>distributed transactions.
>>
>>Alternative: how about accumulating all error information in PL/SQL table
>>inside the package, and later persist it to the database table after commit
>>or rollback occurs in your main transaction.
>>
>>Boris Milrud.
>>
>>"Razvan" <razvani@hotmail.com> wrote:
>>>
>>>Hi all,
>>>
>>>Can somebody tell me if it is posible to use autonomous transactions in
>>a
>>>distributed database in Oracle 8i?
>>>Actually, what I am trying to do is to start an autonomous transaction
>from
>>>a PL/SQL stored procedure (considered as the main transaction) in order
>>to
>>>retrive all the errors which may occur and commit them independently,
whether
>>>or not the main transaction comits.
>>>This works fine on local. But if I call the stored procedure remotely
from
>>>another database, via a DB link, this doesn't work anymore.
>>>Do I have an alternative to this?
>>>
>>>Thank you for any answer.
>>
>

[Razvan]

Hello,

Actually, the solution I use for the moment is the one which involves the
pipe. But I don't like it very much because it involves a process running
all the time on the unix machine (I created, in fact, a demon on unix, a
kind of listener which waits for errors on the other end of the pipe). It
is permanently connected to the DB. The problem is that if the DB is shut
down, this process ends and it must be started again. I find this solution
less manageable.
For few weeks I have been trying to find an alternative to this, a real Oracle
solution. And it seems that you've just provided me with one.
I am going to try the solution with the triggers.
Thank you very much. I shall keep informed about how it works.

Razvan IONESCU.

"Boris Milrud" <milrud@hotmail.com> wrote:
>
>Razvan,
>
>I don't think that your solution is possible. However, I've got another
2
>solutions for you:
>
>1. You can use autonomous transaction inside the trigger in remote database.
>Then your trigger code would save an error and commit it regardless of the
>results of main transaction.
>
>2. You could use DBMS_PIPE package to send errors to another session via
>pipe, and that session would commit it: in a way, that session would be
acting
>as autonomous transaction.
>
>What do you think? Let me know how it will work for you.
>
>Boris Milrud.
>
>"Razvan" <razvani@hotmail.com> wrote:
>>
>>First I want to thank you for your answer.
>>
>>This was also the alternative I considered first. But the problem is that
>>the error record is quite a long one (aprox. 2200 characters). I am afraid
>>not to encounter an overflow in case of too many errors, as the PL/SQL
tables
>>are kept in the memory.
>>I was wondering whether it is possible to start another session (an independent
>>one) from inside the actual session, only to record the errors, something
>>similar as an autonomous transaction can be started from a main transaction.
>>
>>Thank you again.
>>Razvan IONESCU
>>
>>"Boris Milrud" <milrud@hotmail.com> wrote:
>>>
>>>Razvan,
>>>
>>>Unfortunately, autonomous transactions are not supported in 8i as part
>of
>>>distributed transactions.
>>>
>>>Alternative: how about accumulating all error information in PL/SQL table
>>>inside the package, and later persist it to the database table after commit
>>>or rollback occurs in your main transaction.
>>>
>>>Boris Milrud.
>>>
>>>"Razvan" <razvani@hotmail.com> wrote:
>>>>
>>>>Hi all,
>>>>
>>>>Can somebody tell me if it is posible to use autonomous transactions
in
>>>a
>>>>distributed database in Oracle 8i?
>>>>Actually, what I am trying to do is to start an autonomous transaction
>>from
>>>>a PL/SQL stored procedure (considered as the main transaction) in order
>>>to
>>>>retrive all the errors which may occur and commit them independently,
>whether
>>>>or not the main transaction comits.
>>>>This works fine on local. But if I call the stored procedure remotely
>from
>>>>another database, via a DB link, this doesn't work anymore.
>>>>Do I have an alternative to this?
>>>>
>>>>Thank you for any answer.
>>>
>>
>