RE: a PL/SQL design question.

Just so you know, you should be able to manually "acknowledge" the enqueue of the message on the queue which will make it available to the consumer before the transaction containing the insert is commited.    

-----Original Message-----
Sent: Tuesday, 10 December 2002 10:09 AM To: Multiple recipients of list ORACLE-L

An easy way to do this is using DBMS_JOB. That way you can get asynchronous execution, and it isn't tied to the transaction.

This is clipped from a fix I just did for a customer. It ain't complete, but hopefully you can follow the logic:

        v_variables_in_table INTEGER; 
        v_job_num INTEGER; 
        v_job_started INTEGER; 

BEGIN 
        BEGIN 
                SELECT job_num INTO v_job_num 
                        FROM job_number_storage 
                        WHERE job_name = '<stored_proc_name>'; 

                SELECT 1 INTO v_job_started 
                        FROM user_jobs 
                        WHERE job=v_job_num; 
        EXCEPTION 
                WHEN NO_DATA_FOUND THEN 
                        v_job_started := 0; 
        END; 

        IF v_job_started = 1 THEN 
                DBMS_JOB.REMOVE(v_job_num); 
        END IF; 
        DELETE FROM job_number_storage 
                WHERE job_name = '<stored_proc_name>'; 

        -- start the job 
        -- insert into jdp_temp values ('starting job here',sysdate); 
 
DBMS_JOB.SUBMIT(v_job_num,'<stored_proc_name>;',sysdate,'sysdate+1'); 
        INSERT INTO job_number_storage (job_num,job_name) 
                VALUES (v_job_num,'<stored_proc_name>'); 
        COMMIT; 

END;
/

> -----Original Message-----
> From: Craig Munday [ mailto:Craig.Munday_at_ecard.com.au
<mailto:Craig.Munday_at_ecard.com.au> ]
> Sent: Monday, December 09, 2002 2:09 PM
> To: Multiple recipients of list ORACLE-L
> Subject: 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
<http://www.orafaq.com>
> --
> Author: Stephane Faroult
> INET: sfaroult_at_oriole.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
<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
<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
<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).