Autonomous transactions in distributed database


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Autonomous transactions in distributed database

  1. #1
    Razvan Guest

    Autonomous transactions in distributed database


    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.

  2. #2
    Boris Milrud Guest

    Re: Autonomous transactions in distributed database


    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.



  3. #3
    Razvan Guest

    Re: Autonomous transactions in distributed database


    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.

    >



  4. #4
    Boris Milrud Guest

    Re: Autonomous transactions in distributed database


    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.

    >>

    >



  5. #5
    Razvan Guest

    Re: Autonomous transactions in distributed database


    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.
    >>>

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center