Waiting for a submitted job in Oracle PL / SQL?

Question

I am looking for the Java equivalent of thread.join () in PL / SQL. That is, I want to start several tasks (flows), and then wait for their completion.

How is this possible in PL / SQL?

I am thinking of using dbms_job.submit (I know that it is deprecated). dbms_scheduler is also an alternative.

My code is:

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

I like the solution from Adam Hawkes using DBMS_SCHEDULER chains. Did not know about this since I am still using DBMS_JOB and have not rewritten the code yet.

In any case ... the solution that I am currently using for this is a combination of DBMS_JOB (although you should probably use DBMS_SCHEDULER since DBMS_JOB is deprecated as you noticed) and DBMS_ALERT.

Jobs are created using DBMS_JOB. Then we wait for the completion of work using dbms_alert.register and dbms_alert.waitany. Each job, when it is completed, uses dbms_alert.signal. A problem may arise if the task completes and signals that the parent is ready, but I'm sure you can get around this.

I assume DBMS_SCHEDULER chains are probably the way you should do it now, but just adding my answer for completeness.

Answer 2

This is done using chains in job scheduling DBMS_SCHEDULER . The Oracle documentation contains examples that do what you want. Basically, you define steps, small pieces of code, with execution. In your example, the first step will be the initial step (usually it will be initialization), then you will have two steps that are executed in parallel (jobno1 and jobno2), and then the last step, which is activated after the completion of both parallel tasks.