Re: DBMS_JOB, Distributed transactions and logging.

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Wed, 25 Sep 2002 17:43:18 +0400
Message-ID: <amsekk$8fu$1@babylon.agtel.net>

I even came up with a quick demo for this:

create table dtx_log (
  time_stamp date default sysdate
 ,logstr varchar2(1000)
 )
/

create or replace procedure dtx_log_send( v dtx_log.logstr%type) as
 l_stat integer;
 begin
  dbms_pipe.pack_message(v);

• we are using implicit pipe here, it will be created automatically
• for us. l_stat := dbms_pipe.send_message('dtx_log'); exception when others then null; end;
  /
  

create or replace procedure dtx_log_listen as
  l_v dtx_log.logstr%type;
  done boolean := false;
  l_stat integer;
begin
  while (not done) loop
   begin
    l_stat := dbms_pipe.receive_message('dtx_log');     if (l_stat = 0) then

• got something from the pipe, we don't check what data type we got
• as we do not expect anything but VARCHAR2 here. dbms_pipe.unpack_message(l_v); if (upper(l_v) = 'QUIT') then
  • end of logging signaled, terminate done := true; else
  • insert new log entry insert into dtx_log (logstr) values (l_v); commit; end if; end if; exception
    • ignore pipe errors when others then null; end; end loop; end;
      /
      

I then started dtx_log_listen at remote site and called dtx_log_send_at_remote('message') several times followed by dtx_log_send_at_remote('quit'). dtx_log_listen successfully terminated as I expected, and a quick peek at the log revealed that it worked as expected either:

SQL> select to_char(time_stamp,'HH24:MI:SS') timestamp, substr(logstr,1,60) logstr from dtx_log;

TIMESTAM LOGSTR

-------- ------------------------------------------------------------
17:43:07 remote log entry

17:43:15 another remote log entry
17:43:22 yet another remote log entry

So DBMS_PIPE seems like a solution for you...

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


"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message news:amsb9j$71n$1_at_babylon.agtel.net...
> Well then, take a look at DBMS_PIPE. I think it is possible to run a listening
> session on remote site that will pick up messages from the pipe which remote
> procedures (running locally on that site) will post and log them independently.
> Never tried this myself, but I have a gut feeling that it should work.
>
> --
> 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.0209241037.751328d4_at_posting.google.com...
> > Vladimir,
> >
> > Thank you for your reply (Spasibo!) -- the issue is that PRAGMA
> > AUTONOMOUS_TRANSACTION is not allowed on the remote side of the
> > distributed transaction, and this is precisely where we want to do the
> > logging...
> >
> > --Alex
> >
> > "Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message
> news:<altdj6$m0h$1_at_babylon.agtel.net>...
> > > 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
>

Received on Wed Sep 25 2002 - 08:43:18 CDT