Toad World® Forums

Asynchronous Trigger of a Procedure

Hi All,

Can anyone please let me know how to trigger multiple procedures in parallel from a master procedure. The second child procedures should not wait for the completion of the first child procedure.

I can do this by creating two scheduled job, but I don’t want to do it using jobs.I tried the below code but the second child procedure always waits for the first procedure to complete and then only it starts. So please let me know if there are any other method to achieve this.

For example: There is a master procedure as MASTER_PROCEDURE and two child packages are triggered in parallel from the master procedure.

CREATE OR REPLACE PROCEDURE MASTER_PROCEDURE

BEGIN

First_ChildProc( 123);

Second_ChildProc( 123);

END;

Thanks in advance.

Regards

Sanjeev

Evening Sanjeev,

Tom Kyte does this sort of thing in the past with a dbms_job.submit(…) then when all the parallel jobs are submitted, a commit will activate them, a rollback will cancel. I’ve never needed to do this sort of thing myself but as far as I remember, you font need to specify a next or interval etc. Just the job name and what to run.

Depending on your oracle version, the dms_scheduler package will most likely have ome thing similar.

If you are on windows, hack, spit, then you could have a batch file , master.cmd, containing all the child code you want to run, wrapped up in “start” commands, like:

Start “first child” /d . /normal sqlplus username/password@alias @child.1.sql

Start “second child” /d . /normal sqlplus username/password@alias @child.2.sql

Then in the child.n.sql files, you would have something like:

Begin

First_childproc ( … );

End;

/

Exit

In Unix you would have similar scripts and the master.sh script would be like:

./child.1.sh &

./child. 2.sh &

Each of those would be similar to:

Sqlplus username/password@alias << EOF

begin

First_childproc ( … );

End:

/

Exit

EOF

There are probably other or easier ways to do this, maybe getting OEM to submit the various scripts etc, but the bove should give you a decent starting point.

HTH

Cheers,

Norm.

Sent from my Android device with K-9 Mail. Please excuse my brevity.