I am running a PL SQL procedure which calls a bunch of other procedures in a sequence. Within the sequence, there are some procedures that submit jobs. What I want to do is to keep the procedure that submitted the jobs, to wait until all the jobs are complete, before letting the next procedure execute. For that, I wrote a while loop quite similar to the following:
WHERE job IN (v_job_id1,v_job_id2);
EXIT WHEN v_running_job_cnt = 0;
The problem is that as soon as the procedure goes for the sleep, the next procedure is called which I need to prevent. Any pointers here on how to do so? Please share.
First, I'm not sure that I understand the question. In what session is "the next procedure called"? If the code you posted is accurate, the code you posted would still be in the loop so it could not be calling any other code. Perhaps you mean that some other session has called the next procedure?
Taking a step back, it appears that you are trying to reinvent the wheel of scheduler chains that are provided by the DBMS_SCHEDULER package. Is that intentional? Is there some reason that you can't use the DBMS_SCHEDULER package and Oracle's implementation of chains?
You can say that there is a home procedure calling the others in a sequence, all in a single session, all running on the same instance. I'll explain.
I call the main procedure (one time task). The main procedure calls a procedure which submits jobs. It is this procedure that has the while loop I mentioned.
But contrary to what I'm expecting, the control in the main procedure advances to the next procedure call (in the main procedure), which I don't want to happen until all the jobs submitted by the previous procedure have completed.
I'm not using DBMS_SCHEDULER because I cannot determine the amount of time a procedure will take to complete, and I don't want too much gap between the execution of the procedures. It is further complicated by the fact that the procedures have quite a dependency between them and I have no way of removing the dependency. I can't share the whole code (copyright issues). I hope this information will suffice.
I'm not using DBMS_SCHEDULER because I cannot determine the amount of time a procedure will take to complete, and I don't want too much gap between the execution of the procedures.
You cant' have it both ways. If you want the procedure to wait there you are GUARANTEED to have a 'gap' between the procedures. That is the very definition of WAIT.
It is further complicated by the fact that the procedures have quite a dependency between them and I have no way of removing the dependency. I can't share the whole code (copyright issues). I hope this information will suffice.
And that is EXACTLY what the CHAINS of the DBMS_SCHEDULER package are designed to do; execute the jobs based on the dependency between them.
RTFM - See also the 'Using Chains' section of the DBA guide
A chain is a named series of tasks that are linked together for a combined objective. Chains are the means by which you can implement dependency based scheduling, in which jobs are started depending on the outcomes of one or more previous jobs.
To create and use a chain, you complete these tasks in order:
|1. Create a chain object||Creating Chains|
|2. Define the steps in the chain||Defining Chain Steps|
|3. Add rules||Adding Rules to a Chain|
|4. Enable the chain||Enabling Chains|
|5. Create a job (the "chain job") that points to the chain||Creating Jobs for Chains|