RE: a PL/SQL design question.

[Craig Munday]

Title: 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-----
From: Jeremy Pulcifer [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, 10 December 2002 10:09 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: a PL/SQL design question.

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:[EMAIL PROTECTED]]
> 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
> --
> Author: Stephane Faroult
>   INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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).
>