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

Question

I'm looking for the equivalent of Java's thread.join() in PL/SQL. I.e. I want to kick off a number of jobs (threads), and then wait for them to finish.

How is this possible in PL/SQL?

I'm thinking of using dbms_job.submit (I know it's deprecated). dbms_scheduler is also an alternative.

My code:

DECLARE
  jobno1 number;
  jobno2 number;
BEGIN

  dbms_job.submit(jobno1,'begin dbms_lock.sleep(10); dbms_output.put_line(''job 1 exit'');end;');
  dbms_job.submit(jobno2,'begin dbms_lock.sleep(10); dbms_output.put_line(''job 2 exit'');end;');

  dbms_job.run(jobno1);
  dbms_job.run(jobno2);

  //Need code to Wait for jobno1 to finish
  //Need code to Wait for jobno2 to finish

END;

Answer 1

This is accomplished using Chains in the DBMS_SCHEDULER job scheduling. The Oracle Documentation has examples which accomplish what you want. Basically, you define steps, small portions of code, with an execution structure. In your example, your first step would be a start step (which would typically do initialization), then you'd have two steps which run in parallel (jobno1 and jobno2), and then a final step which would activate once both parallel jobs complete.

Answer 2

I like the solution from Adam Hawkes using DBMS_SCHEDULER chains. Didn't know about that since I'm still using DBMS_JOB and not having rewritten code yet.

Anyway... the solution I currently use for this is a combination of DBMS_JOB (although you should probably use DBMS_SCHEDULER since DBMS_JOB is deprecated as you noted) and DBMS_ALERT.

Jobs are created using DBMS_JOB. Then we wait for the jobs to complete using dbms_alert.register and dbms_alert.waitany. Each job when it completes uses dbms_alert.signal. There may be a problem if the job completes and signals before the parent is ready but I'm sure you could work around that.

I'm guessing that the DBMS_SCHEDULER chains are probably the way you should do this now but am just adding my answer for completeness.