Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Pausing a PL SQL procedure execution.

SK-OracleNov 9 2013 — edited Nov 9 2013

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

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 7 2013
Added on Nov 9 2013
3 comments
1,631 views