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).