0

I want to execute a procedure which performs updates on a set of given tables. In this case 11 tables. An associative array is used here only because the name of the column used for the join with the table located in the procedure differs amongst the concerned tables:

Declare
  TYPE associative_array IS TABLE OF VARCHAR(100) INDEX BY VARCHAR2(100);
   table_array associative_array;
   l_key VARCHAR2(100);
Begin
  table_array('TABLE1'):='MY_ID1';  --  1
  table_array('TABLE2'):='MY_ID2';  --  2
  table_array('TABLE3'):='MY_ID1';  --  3
  table_array('TABLE4'):='MY_ID1';  --  4
  table_array('TABLE5'):='MY_ID1';  --  5
  table_array('TABLE6'):='MY_ID1';  --  6
  table_array('TABLE7'):='MY_ID2';  --  7
  table_array('TABLE8'):='MY_ID2';  --  8
  table_array('TABLE9'):='MY_ID2';  --  9
  table_array('TABLE10'):='MY_ID1'; -- 10 
  table_array('TABLE11'):='MY_ID1'; -- 11 

   l_key := table_array.first;
   while (l_key is not null) loop
      execute immediate 'MY_UPDATE_PROCEDURE(''' || l_key || ',' || table_array(l_key)||''')'; -- calling a procedure which performes updates on tables
      l_key := table_array.next(l_key);
   end loop;
end;

Now, for performance reasons, I want to execute 4 procedures parallelly, so DBMS_SCHEDULER would probably be the way to go. The tricky thing: Assume 4 procedures are running. When at some point 1 or more instances finish I immediately want another instance to be executed, so that always the maximum number of instances is running (e.g. 4 due to own definition) until all tables have been processed.

How could this kind of queuing be realised?

royskatt
  • 1,190
  • 2
  • 15
  • 35

1 Answers1

1

This may be a way to go: monitoring job state It looks like you should be able to set up a procedure to schedule jobs, monitor them, and use job state events to track how many are running and schedule new ones as old ones complete

ishando
  • 306
  • 3
  • 7