31145

How can I wait until all my scheduler jobs have finished?

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?

Answer1:

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>

Answer2:

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

Answer3:

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.

Answer4:

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;

Recommend

  • Getting an Error “ORA-06502: PL/SQL: numeric or value error: character string buffer too small”
  • Memory Leak with OracleCommand
  • Accessing types defined inside package from java
  • PL/SQL: re-write SELECT statement using IN parameter in stored procedure
  • SQL Procedures - Comparing values
  • DML and Exception Handling - Oracle
  • Redirect to Post Method/Action
  • Check all values in string[] for length?
  • How to access profile picture with Facebook API in Swift 3?
  • How to specify input and output paths from cmd.exe for a PowerShell script?
  • Filter strings with regex before casting to numeric
  • SAXReader not re-ecape characters
  • Ember.js model to be organised as a tree structure
  • Reading a file into a multidimensional array
  • Read text file that is not in the main package in a runnable jar
  • IE7 and TinyMCE with Plone
  • std::remove_copy_if_ valgrind bytes in block are possibly lost in loss record
  • Conversion from string “a” to type 'Boolean' is not valid
  • how to upload multiple files in c# windows application
  • Display java JPanel in a JFrame
  • Appending Character to Character Array In C
  • Unable to decode certificate at client new X509Certificate2()
  • OOP Javascript - Is “get property” method necessary?
  • Date Conversion from yyyy-mm-dd to dd-mm-yyyy
  • Passing 1 of 3 values to a Sub
  • How to test if a URL from an Eclipse bundle is a directory?
  • ActiveRecord query for a count of new users by day
  • Set the selected item in dropdownlist in MVC3
  • How to create a file in java without a extension
  • Spring Data JPA custom method causing PropertyReferenceException
  • Splitting given String into two variables - php
  • What is Eclipse's Declaration View used for?
  • Counter field in MS Access, how to generate?
  • Possible to stop flickering java tooltip in heavyweight mode?
  • sending/ receiving email in Java
  • How to set my toolbar fixed while scrolling android
  • AT Commands to Send SMS not working in Windows 8.1
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • apache spark aggregate function using min value
  • Sorting a 2D array using the second column C++