Parallel Execute Stored Procedures Oracle

DonnieBrasco

Hi,

Can someone point me in the the right direction of how i would go about executing stored procedures in parallel.

For example:
If i have 3 stored procedures, 2 that simply select from different tables and 1 procedure that executes both of these

i.e.
SP1 contains SELECT * FROM TABLEA
and
SP2 contains SELECT * FROM TABLEB

and SP3 simply calls both of the above eg
begin
Exec SP1;
Exec SP2;
end;

Is it possible to get sp3 to NOT wait for SP1 to finish before executing SP2. i.e execute SP1 then immediately execute SP2 almost
like spawning in separate processes.
I suppose a manual way of achieving this would be to open SQL Plus and execute SP1 then open a second sql plus session and execute
sp2 creating the situation were both are executing in parallel.
I need to find a way of performing this within SP3.

Cheers, DB

Tom Dunne

Off the top of my head, no, not the way you are doing it.

One way is to have sp1 and sp2 in two separate schemas. I doubt that is what you want.

To the best of my knowledge, pl/SQL does not do threads or parallel execution.

DublinWriter

Try the asktom website.

bonkey

Have a look at dbms_job.submit

DonnieBrasco

thanks all.

dbms_job.submit - excellent!
In my sample case above, this is the usage.

create sp1 and sp2 to do whatever.
then

CREATE OR REPLACE PROCEDURE SP3
AS

v_JobNum NUMBER := 1;

BEGIN


BEGIN
DBMS_JOB.SUBMIT(v_JobNum,'SP1;',sysdate,'sysdate +1');

v_JobNum := v_JobNum + 1;

DBMS_JOB.SUBMIT(v_JobNum,'SP2;',sysdate,'sysdate +1');

COMMIT;
END;

END SP3;