OraQA

This script creates a procedure to run in parallel others procedures and continue with serial procedures.

Create syncronize table

create table PARALLEL_EXECUTIONS
(
  JOBID                              NUMBER(13, 0)  ,
  PROCESSID                          NUMBER(13, 0)  ,
  PARENT_NAME                        VARCHAR2(50)
)
/
-- only for testing the procedure bellow
grant execute on dbms_lock to ...
/

Create procedure

create or replace procedure aggregate_in_parallel
as
  v_job number;
  v_parent_name parallel_executions.parent_name%type:='LOCK_SLEEP';
  v_cnt number;
begin

 dbms_job.submit( v_job, 'dbms_lock.sleep(30);' );
 insert into parallel_executions(JOBID,PROCESSID,PARENT_NAME)
  values (v_job,1,v_parent_name);
 commit;

 dbms_job.submit( v_job, 'dbms_lock.sleep(20);' );
  insert into parallel_executions(JOBID,PROCESSID,PARENT_NAME)
   values (v_job,2,v_parent_name);
 commit;

 dbms_job.submit( v_job, 'dbms_lock.sleep(10);' );
   insert into parallel_executions(JOBID,PROCESSID,PARENT_NAME)
    values (v_job,3,v_parent_name);
 commit;

 loop
  dbms_lock.sleep(20);
  delete from parallel_executions pe where not exists
 (select 1 from user_jobs uj where uj.job=pe.jobid)
   and PARENT_NAME=v_parent_name;
  commit;
  select count(*) into v_cnt from parallel_executions
  where PARENT_NAME=v_parent_name;
  exit when (v_cnt = 0);
 end loop;

end;
/

Test it

set serveroutput on
begin
aggregate_in_parallel;
dbms_output.put_line(
  'Finish .... continue with others serial procedures ...');
end;
/

Filed under PL/SQL. Follow any responses to this entry through this RSS 2.0 feed.