How can I wait until all my scheduler jobs have finished?

Question

I'm my package, I'm enqueueing multiple jobs like this:

dbms_scheduler.create_job
  ( job_name     => p_job_name
  , job_type     => 'PLSQL_BLOCK'
  , job_action   => p_sql_code
  , start_date   => SYSDATE
  , enabled      => TRUE
  , comments     => 'Running batch jobs in parallel');

Once I've done this for as many parallel jobs that I wish to start, I need to block until all the jobs have completed.

Currently I've had to sleeping and polling the the tables ALL_SCHEDULER_JOB_RUN_DETAILS and ALL_SCHEDULER_JOBS and check the status of the jobs. It seems like a really ugly solution. Here's the SQL that i use:

PROCEDURE run_jobs
  ( p_jobs    StringTableType
  , p_sql     VARCHAR2(4000) )
IS

  l_jobs         StringTableType;
  l_status       sys_type.STRING;
  l_additional_info  sys_type.text;
  l_done         BOOLEAN;
  i              PLS_INTEGER;

BEGIN

  l_jobs := p_jobs;

  -- Submit jobs
  FOR i IN 1..l_jobs.COUNT LOOP

  dbms_scheduler.create_job
    ( job_name     => l_jobs(i)
    , job_type     => 'PLSQL_BLOCK'
    , job_action   => p_sql
    , start_date   => SYSDATE
    , enabled      => TRUE
    , comments     => 'Running batch jobs in parallel');

  END LOOP;

  -- now wait untile all jobs are finished
  l_done := FALSE;

  WHILE NOT l_done LOOP

    DBMS_LOCK.sleep(5);

    l_done := TRUE;

    i := l_jobs.FIRST;
    WHILE i IS NOT NULL LOOP

      WITH jobs_log
           AS (SELECT job_name, state status, '' additional_info
                 FROM all_scheduler_jobs
               UNION
               SELECT job_name, status, additional_info
                 FROM all_scheduler_job_run_details
               )
      SELECT status, additional_info
        INTO l_status, l_additional_info
        FROM jobs_log
       WHERE job_name = p_jobs (i);

      --Analyze job status
      CASE
        WHEN l_status = 'RUNNING' THEN
          l_done := FALSE;

        WHEN l_status = 'SUCCEEDED' THEN
          l_jobs.DELETE(i);

        WHEN l_status = 'FAILED' THEN
          l_jobs.DELETE(i);

        ELSE
          l_done := FALSE;

      END CASE;

      i := l_jobs.NEXT(i);

    END LOOP;

  END LOOP;

END run_jobs;

How can I block my code until all the jobs have finished? Can anyone give me an example if there's a better way to do this?

> How can I wait until all my scheduler jobs have finished? Take a nap ;^) — Eugene Pavlov, Mar 08 '13 at 14:35
See this [answer to a similar question](http://stackoverflow.com/a/4600487/119634), Adam Hawkes suggests creating a chain via dbms_scheduler. It looks like an elegant solution. — Vincent Malgrat, Mar 08 '13 at 14:52
@VincentMalgrat, a great resource. Thank you. It resolved the issue. — Mridang Agarwalla, Nov 27 '13 at 15:06
Hi @MridangAgarwalla have you ever solved your problem using the chain solution? Would you be so valiant to post it? — Francesco Pegoraro, Dec 17 '20 at 21:25

score 2 · Accepted Answer · edited May 23 '17 at 11:54

2

As someone posted a link from another StackOverflow answer, this can be done using chains from the DBMS_SCHEDULER.

Waiting for a submitted job to finish in Oracle PL/SQL?

edited May 23 '17 at 11:54

Community

1
1

answered Nov 27 '13 at 15:05

Mridang Agarwalla

43,201
71
221
382

Gusev Dmitry · Answer 2 · 2016-02-25T09:46:55.873

2

DECLARE
  cnt NUMBER:=1;
BEGIN
  WHILE cnt>=1
  LOOP
    SELECT count(1) INTO cnt FROM dba_scheduler_running_jobs srj
    WHERE srj.job_name IN ('TEST_JOB1','TEST_JOB2');
    IF cnt>0 THEN
      dbms_lock.sleep (5);
    END IF;
  END LOOP;
  dbms_output.put_line('ASASA');  
END;

edited Feb 25 '16 at 09:46

answered Feb 25 '16 at 09:45

Gusev Dmitry

61
4

Welcome to SO. Can you please explain, why this solves the OPs problem? – m00am Feb 25 '16 at 10:05

score 1 · Answer 3 · answered Mar 08 '13 at 15:10

1

Unfortunately, intersession communications in Oracle are not so convenient as interprocess communications in OS. But you can

lock table ... in exclusive mode

inside a job and perform waiting of job termination by

select ... for update

answered Mar 08 '13 at 15:10

Egor Skriptunoff

23,359
2
34
64

score 1 · Answer 4 · answered Mar 11 '13 at 01:23

1

If you don't have a lot of jobs, you can use the DBMS_LOCK package to create your own lock object. Create one per job with some well-known name. Have the jobs acquire the lock, and the master job wait on all the locks to be freed.

answered Mar 11 '13 at 01:23

OldProgrammer

12,050
4
24
45

How can I wait until all my scheduler jobs have finished?

4 Answers4

Linked