CODEWITHSUNDEEP
oracle-databasestored-procedures
dang
dang

Reputation: 2412

Run stored procedures parallely

I have a master stored procedure:

MASTER();

In this stored procedure, I call 3 other stored procedures:

SP1();
SP2();
SP3();

Right now, it's running serially, i.e. one after the other. I want to run it in parallel and once all the 3 stored procedures are completely executed, run next part of MASTER() stored procedure.

I am using Oracle Standard One Edition 11.2. How can I achieve this?

Upvotes: 2

Views: 666

Answers (2)

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

There is other way to achieve this.

Set up envirement: 

create table t_procedure(id number, procedures varchar2(200));  

create type l_char is table of varchar2(100);

create procedure goSleeep(p_sec number)is 
begin 
 dbms_lock.sleep( p_sec );
end;

Package: 

create or replace package goParallel is
 TYPE t_referential_cursor IS REF CURSOR RETURN t_procedure%ROWTYPE;

 function runParallel(p_cursor     t_referential_cursor)
  return l_char pipelined      
      parallel_enable(partition p_cursor BY HASH(id));
end;  

create or replace package body goParallel is

 function runParallel(p_cursor     t_referential_cursor)
  return l_char pipelined      
      parallel_enable(partition p_cursor BY HASH(id))
is 
 v_start date := sysdate; 
 v_end date;
 vid number;
 p_proc varchar2(200);
begin 
 loop
  fetch p_cursor into vid, p_proc;
      exit when p_cursor%notfound;  
      execute immediate p_proc;
      v_end := sysdate;
       pipe row( vid||' --- '||to_char(v_start,'HH24:MI:SS')||' - '|| to_char(v_end,'HH24:MI:SS'));
  end loop;
  return;
end; 

end;

Insert some procedures to run. 

insert into t_procedure values (1, 'begin goSleeep(5); end;');
insert into t_procedure values (2, 'begin goSleeep(8); end;');
insert into t_procedure values (3, 'begin goSleeep(9); end;');
commit;

And run it with strange way.

    select * from table(goParallel.runParallel(cursor(select /*+ PARALLEL(a 8) */ * from t_procedure a)));
--    result: id - start - end
        1 --- 12:15:54 - 12:15:59
        2 --- 12:15:54 - 12:16:02
        3 --- 12:15:54 - 12:16:03

Upvotes: 0

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36977

One way to archive that is to use DBMS_JOB or DBMS_SCHEDULER to launch the procedures in parallel and DBMS_ALERT to notify the master procedure when they are finished.

Upvotes: 1

Related Questions