Re: pipes

From: Quinton McCombs <>
Date: 1997/05/12
Message-ID: <>#1/1

Business Information Services wrote:

> Hi there,
> I want to set up an error log. I have a table called log and a table
> called transaction. When a transaction comes in I do all this stuff
> that
> I won't bore you with, but basically it updates various other tables
> in
> the database.
> If any of it goes wrong I want to rollback the changes to the
> database
> but I do not want to roll back the entries that have been written to
> the
> log table.
> Currently what happens is that all changes to the database including
> the
> LOG table are rolled back.
> I have done some reading and have found a package called DBMS_PIPE
> which
> the writer says is useful for this situation. Unfortunately he
> doesn't
> say how.
> Does anyone know how or can help with other suggestions?
> Regards,
> Rowan McCammon.
> --
> Business Information Services
> (For more information call Brad Deveson)
> Tel: (02) 9387-2509 (Australia)
> Fax: (02) 9369-3840 (Australia)

 It will actually be very easy for you to use the pipes to accomplish what you are trying to do. The procedures you will need to use are DBMS_PIPE.SEND_MESSAGE and DMBS_PIPE.RECEIVE_MESSAGE. The syntax for these procedures can be found in the Application Developer's guide.

Pipes are going to be created within the schema of the user calling DBMS_PIPE.SEND_MESSAGE. An easy way to handle this "problem" is to create a schema for the pipes. You will then create a package within the new schema with a send and receive procedure. Finally, create a public synonym for the package and give ececute rights to the users that need to call it. You will now have an easy way to send and receive messages without worring about the schema of the user sending the message.

The next step will be to create a procedure to monitor one of more named pipes and log the messages to various tables. The procedure should be designed to run in an endless loop. Next start a copy of SQLPLUS to execute your procedure. If possible, setup a script to be run at database startup to start SQLPLUS and run your script.

Email me if you need any further explaination. Received on Mon May 12 1997 - 00:00:00 CEST

Original text of this message