How to execute procedure parallel inside package in plsql

Question

I have package as given below:

CREATE OR REPLACE PACKAGE OT.PK_TEST IS

PROCEDURE A;

Procedure B;
Procedure C;
Procedure D;

END PK_TEST;

Body...

CREATE OR REPLACE PACKAGE BODY  oT.PK_TEST IS
PROCEDURE A 
IS
BEGIN
--creating all required tables
B;
c;
D;
END A;

Procedure B
IS 
BEGIN
...codes
END;

Procedure C
IS 
BEGIN
...codes
END;

Procedure D
IS 
BEGIN
...codes
END;
END PK_TEST;

I execute Procedure A and procedure B,C,D get call from procedure.

EXEC OT.PK_TEST.A;

What happens is that A is the first procedure executed in package. All the required tables are made inside procedure A. After this, procedures B,C,D execute. But it is that Proceudre B,C,D is independent to each other. So in my package procedure B runs first, procedure C runs then after and procedure D runs at last. This took me lot of time to run the package. I want to execute the Procedure B,C,D in parallel after all tables are created in procedure A.

How can I execute all the procedures parallelly? I am learning about scheduling jobs. Is using scheduling jobs a good method or is there any other option?

Answer 1

Here's an example which uses DBMS_JOB; it is quite simple and works OK. For advanced cases search for DBMS_SCHEDULER. The idea is simple: I have a table and two procedures which will insert a row into that table. The main procedure will call them simultaneously.

SQL> create table test (proc varchar2(10), datum date);

Table created.

SQL> create or replace procedure p1 as
  2  begin
  3    dbms_lock.sleep(1);
  4    insert into test (proc, datum) values ('p1', sysdate);
  5  end;
  6  /

Procedure created.

SQL> create or replace procedure p2 as
  2  begin
  3    dbms_lock.sleep(15);
  4    insert into test (proc, datum) values ('p2', sysdate);
  5  end;
  6  /

Procedure created.

SQL> create or replace procedure p_main as
  2    l_job number;
  3  begin
  4    dbms_job.submit(l_job, 'p1;', sysdate, null);
  5    dbms_job.submit(l_job, 'p2;', sysdate, null);
  6    commit;
  7  end;
  8  /

Procedure created.

Testing:

SQL> select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') right_now from dual;

RIGHT_NOW
-------------------
05.02.2020 19:29:50

SQL> exec p_main;

PL/SQL procedure successfully completed.

SQL> select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') right_now from dual;

RIGHT_NOW
-------------------
05.02.2020 19:29:50            --> as you can see, RIGHT NOW is equal to the one
                               --  fetched before calling the P_MAIN procedure,
                               --  although both P1 and P2 procedures are "waiting"
                               --  some time

SQL> select job, next_date, what from user_jobs;

       JOB NEXT_DATE           WHAT
---------- ------------------- --------------------
        76 05.02.2020 19:29:50 p2;         --> both procedures are scheduled to run
        75 05.02.2020 19:29:50 p1;         --  at the same time

SQL>

A little bit later (~20 seconds later):

SQL> select proc, to_char(datum, 'dd.mm.yyyy hh24:mi:ss') datum From test;

PROC       DATUM
---------- -------------------
p2         05.02.2020 19:30:09        --> both procedures performed INSERT, each
p1         05.02.2020 19:29:55        --  on its own time

SQL> select job, next_date, what from user_jobs;

no rows selected                      --> after procedures have completed, there's none
                                      --  in the queue as they have been scheduled to run
                                      --  only once

SQL>