DBMS_PIPE package seems to be your only choice then. DMBS_ALERT might
look compelling, but it requires alerter to commit for waiter to receive
the signal, and this is not possible in your situation. With DBMS_PIPE, you
can create one session listening on the pipe and inserting a row into
log table each time it receives a request, and another session will pipe in
a message on failure. Oracle pipes are transaction-independent and one
of their uses is your case.
--
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.0206180533.68ac8a7d_at_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 - 11:33:33 CDT
