title: Running PL/SQL calls in parallel and wait for execution to finish (fork and join)
body: <p>In a legacy system there is some PL/SQL procedure that calls the another procedure mutliple times with different parameters. The procedure contains a lot of PL/SQL logic (if, then, else).</p>
  
  <p>As the execution of this procedure takes very long, we thought about using concurrency to speed things up without even touching the actual logic.</p>
  
  <p>I understand that there are several ways of running (PL/)SQL in parallel on oracle (see bellow).</p>
  
  <p><strong>However, I wasn't able to find a way to pass different arguments/parameters to a PL/SQL procedure, execute them in parallel and wait until all procedures are finished executing</strong> (i.e. I'm looking for mechanism to <a href="http://pubs.opengroup.org/onlinepubs/7908799/xsh/pthread_join.html" rel="nofollow noreferrer">join</a> all threads or for a <a href="http://en.wikipedia.org/wiki/Barrier_%28computer_science%29" rel="nofollow noreferrer">barrier</a> mechanism in oracle).</p>
  
  <p>Let's use the following simplified example on the SCOTT Schema:</p>
  
  <pre><code>DECLARE
  
  PROCEDURE DELETE_BONUS(
      in_job IN VARCHAR2)
  IS
  BEGIN
    -- Imagine a lot of IF, ELSEIF, ELSE statements here
    DELETE FROM BONUS WHERE JOB=in_job;
  END;
  
  BEGIN 
   INSERT into BONUS(ENAME, JOB) SELECT ROWNUM, 'A' FROM DUAL CONNECT BY LEVEL &lt;= 1000000;
   INSERT into BONUS(ENAME, JOB) SELECT ROWNUM, 'B' FROM DUAL CONNECT BY LEVEL &lt;= 1000000;
   INSERT into BONUS(ENAME, JOB) SELECT ROWNUM, 'C' FROM DUAL CONNECT BY LEVEL &lt;= 1000000;
  
   -- TODO execute those in parallel
   DELETE_BONUS('A');
   DELETE_BONUS('B');
   DELETE_BONUS('C');
   -- TODO wait for all procedures to finish
  
  EXCEPTION
  WHEN OTHERS THEN
    RAISE;
  END;
  /
  </code></pre>
  
  <p>Here's what I found so far:</p>
  
  <ol>
  <li><a href="https://stackoverflow.com/a/577035/1845976">DBMS_JOB</a> (deprecated)</li>
  <li><a href="https://stackoverflow.com/a/14530955/1845976">DBMS_SCHEDULER</a> (how to wait for jobs to finish? <a href="https://stackoverflow.com/a/15297125/1845976">LOCKS</a>?)</li>
  <li><a href="http://docs.oracle.com/cd/B19306_01/server.102/b14231/schedadmin.htm#BAJHFHCD" rel="nofollow noreferrer">DBMS_SCHEDULER CHAINS</a> (<a href="https://community.oracle.com/thread/2241060?tstart=0" rel="nofollow noreferrer">passing parameters/arguments is not really possible</a>?!)</li>
  <li><a href="http://www.oracle-base.com/articles/11g/dbms_parallel_execute_11gR2.php" rel="nofollow noreferrer">DBMS_PARALLEL_EXECUTE</a> (can be used to run SQL queries in parallel but not PL/SQL procedures)</li>
  </ol>
  
  <p>Can one of these approaches be used to fork and join the procedure calls? Or is there yet another approach that can?</p>