如何等待所有调度程序作业完成? [英] How can I wait until all my scheduler jobs have finished?

问题描述

我是我的包裹，正在排队多个工作，例如:

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.

当前，我不得不睡觉并轮询表ALL_SCHEDULER_JOB_RUN_DETAILS和ALL_SCHEDULER_JOBS并检查作业的状态.这似乎是一个非常丑陋的解决方案.这是我使用的SQL:

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?

推荐答案

当有人发布了另一个StackOverflow答案的链接时，可以使用DBMS_SCHEDULER中的链来完成.

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

正在等待已提交作业以在Oracle PL/SQL中完成?

这篇关于如何等待所有调度程序作业完成?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！