Unanswered: Parallel Processing in Oracle Using Pragma/DBMS_JOB?
I want to execute 3 procedures in parallel , which are called from a master procedure and at the same time the control should return to the master procedure after the call.
For example, from Proc A , 3 procs B,C,D are called sequentially, but before the proc B completes it execution , the control should return to proc A and the execution of Proc C should be initiated.
I believe,this can be achieved through DBMS_JOB package, but can it be done through a different/better way(using Pragma Autonomous transaction)? And , what are the advantages and disadvantages of DBMS_JOB over others?
I can't see or figure how pragma autonomous transaction will help you there, since all it does is to isolate transactions making the one you want "autonomous", i.e. independent of the parent transaction, but still, it is a sequential process.
DBMS_JOB is what you want (or dbms_scheduler in 10g). You will call Procs B, C, and D from A, with dbms_job.submit. You just make sure you have enough job queue processes (determined by job_queue_processes parameter) so they can all run in parallel and dont have to wait for each other.