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:
WHILE TRUE
LOOP
SELECT COUNT(*)
INTO v_running_job_cnt
FROM dba_jobs_running
WHERE job IN (v_job_id1,v_job_id2);
EXIT WHEN v_running_job_cnt = 0;
dbms_lock.sleep(20);
END LOOP;
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.
Thanks,
jSeven