4

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;
vicsz
  • 9,552
  • 16
  • 69
  • 101

2 Answers2

12

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.

Adam Hawkes
  • 7,218
  • 30
  • 57
  • +1, nice I didn't know about chaining ability in dbms_scheduler ... though I'm still not sure how I would wait for the last 2 steps (run in parallel) to finish before my block my finishes. – vicsz Jan 05 '11 at 16:44
5

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.

Mike Meyers
  • 2,885
  • 1
  • 20
  • 26
  • An equally good solution if you're still using `DBMS_JOB` instead of `DBMS_SCHEDULER`! – Adam Hawkes Jan 05 '11 at 14:10
  • +1, nice idea .. any ideas on how I would wait for the 2 jobs to finish ( like you mentioned there will be a probability that a job will finish before the waitany step is invoked). One solution that I could think of is to have both jobs throw the same event, and then catch it twice ... alternatives? – vicsz Jan 05 '11 at 16:45
  • @vicjugador: The short answer is that when creating the jobs we add an identifier (the identifier is created using `DBMS_RANDOM`) to a collection. Then when the job signals back, we remove it from the collection. Once the collection is empty we move on. There is probably a better way to handle this though. You could just assume that you've created 2 jobs and got 2 signals back so you're done. – Mike Meyers Jan 05 '11 at 17:08