This discussion is archived
3 Replies Latest reply: Nov 9, 2013 8:06 AM by rp0428 RSS

Pausing a PL SQL procedure execution.

jSeven Newbie
Currently Being Moderated

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

  • 1. Re: Pausing a PL SQL procedure execution.
    Justin Cave Oracle ACE
    Currently Being Moderated

    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?

     

    Justin

  • 2. Re: Pausing a PL SQL procedure execution.
    jSeven Newbie
    Currently Being Moderated

    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.

  • 3. Re: Pausing a PL SQL procedure execution.
    rp0428 Guru
    Currently Being Moderated

    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

     

    Using Chains

    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:

    TaskSee...
    1. Create a chain objectCreating Chains
    2. Define the steps in the chainDefining Chain Steps
    3. Add rulesAdding Rules to a Chain
    4. Enable the chainEnabling Chains
    5. Create a job (the "chain job") that points to the chainCreating Jobs for Chains

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points