Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Commit on DB-link

Re: Commit on DB-link

From: Alex Vilner <alex_at_sinoma.com>
Date: 18 Jun 2002 06:33:32 -0700
Message-ID: <22e9f6e0.0206180533.68ac8a7d@posting.google.com>


PLS-00127: Pragma AUTONOMOUS_TRANSACTION is not a supported pragma

We are running on Oracle 8.0.5.0 ....
Any other suggestions?

Thank you in advance!

Daniel Morgan <dmorgan_at_exesolutions.com> wrote in message news:<3D0E396B.E6BD69C8_at_exesolutions.com>...
> "Vladimir M. Zakharychev" wrote:
>
> > Well, what you actually said is 'you can commit in triggers using
> > autonomous transactions'. :) Having separate logging procedure
> > with autonomous transaction is more practical, especially if you
> > only want to log errors, which are expected to be rare, but you still
> > can commit right from the trigger itself if it is declared with autonomous
> > transaction pragma.
> >
> > A practical solution might look like this:
> >
> > create table log_table(
> > user_name varchar2(32),
> > err_date date,
> > err_msg varchar2(2000)
> > )
> > /
> >
> > create or replace procedure log_error(err_msg varchar2)
> > as
> > PRAGMA AUTONOMOUS_TRANSACTION;
> > begin
> > insert into log_table values (USER, sysdate, substr(err_msg,1,2000));
> > COMMIT;
> > end;
> > /
> >
> > create or replace trigger some_trigger
> > <before|after>whatever on some_table
> > <for each row>
> > begin
> > update some_other_table_at_dblink set some_column = :NEW.some_column
> > where <your conditions>;
> > exception
> > when others then
> > -- although this procedure commits, it is safe to call it from trigger because
> > -- it runs in an autonomous transaction and commits only its own work
> > log_error('Failed to update some_table.some_column with '||:NEW.some_column);
> > raise; -- re-raise the error
> > end;
> > /
> >
> > This will trap the error in trigger, log it in an autonomous transaction
> > and re-raise the error so that it can be caught up the call chain and the
> > triggering transaction can be rolled back.
> >
> > --
> > 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.
> >
> > "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> > news:3D0E0EB8.8D5D16A9_at_exesolutions.com...
> > > Alex Vilner wrote:
> > >
> > > > Hello!
> > > >
> > > > We have 2 database instances, with data being moved from one into
> > > > another through the use of triggers, operating on DB-link (Primary
> > > > server has DB-link connection to Secondary. Tables on the Primary
> > > > server have triggers that insert/update data into Secondary). Simple,
> > > > straightforward scenario :)
> > > >
> > > > Here is one issue, though: when the update or insert fails from the
> > > > Primary to the Secondary, we want the transaction to roll back, but to
> > > > have an entry created in the table on the primary server, which serves
> > > > as the log. Now, since these are the triggers that do the data moves,
> > > > I am not sure we can use COMMIT and ROLLBACK, to commit the data to
> > > > the log, while rolling back the actual transaction...
> > > >
> > > > What can you suggest as a way of dealing with this situation? Oracle
> > > > Queue has been suggested, but this makes it not "real-time" anymore --
> > > > enqueueing and dequeueing is obviously delayed, and is dependent on
> > > > the queue fill-up and general availability.
> > > >
> > > > Thank you in advance!
> > > >
> > > > --Alex
> > >
> > > You can not do commit in triggers. But you can write a procedure that is
> > > an autonomous transaction that is called by the exception block and
> > > contains a commit.
> > >
> > > Daniel Morgan
> > >
>
> Well I actually didn't say it as I didn't want to write too complex a response ... but
> you are, of course, correct.
>
> Daniel Morgan
Received on Tue Jun 18 2002 - 08:33:32 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US