RE: a PL/SQL design question.

Hi,

The other option that you have is to use Advance Queuing. You can insert the row and a message on a queue within the same transaction. Your procedure will be a consumer of the messages in the queue.

If the transaction that does the insert is rolled back then the message is never placed on the queue and your procedure is never executed. The dequeue of the message is also transactional so if your procedure fails the message will be left on the queue and redelivered. You need to handle the redelivery case is a sensible manner - that is, you do not want the message to be redelivered continually if it is going to fail all the time.

Cheers,
Craig.

-----Original Message-----
Sent: Friday, 29 November 2002 5:04 AM
To: Multiple recipients of list ORACLE-L

Andrey Bronfin wrote:
> Dear gurus!
> I'm looking for a solution to the following problem:
> I need a way to run a certain stored procedure as soon as a record is
> inserted into a certain table.
> A trigger is not feasible for this, since I do not want the execution of
the
> procedure to be a part of the transaction that inserts a row into the
table.
> I want the insertion to be visible to all the users (i.e. committed) as
soon
> as the insertion is done, and then, as a separate transaction of its own,
to
> run the stored procedure.
> Suggestions , please ?
> Thanks a lot !

Keyword = AUTONOMOUS TRANSACTION

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Craig Munday
  INET: Craig.Munday_at_ecard.com.au

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).