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?

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

<a href="https://stackoverflow.com/questions/4599630/waiting-for-a-submitted-job-to-finish-in-oracle-pl-sql/4600487#4600487" rel="nofollow">Waiting for a submitted job to finish in Oracle PL/SQL?</a>

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

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.

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;