Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> 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: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
> --
> 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).
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremy Pulcifer INET: Jeremy.Pulcifer_at_kadiri.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).Received on Mon Dec 09 2002 - 17:09:11 CST