Implementing multithreading in Oracle procedures

Question

I am working on Oracle 10gR2.

And here is my problem -

I have a procedure, let's call it * proc_parent * (inside the package), which should call another procedure, lets call it * user_creation * . I have to call * user_creation * inside a loop that reads some columns from the table, and these column values ​​are passed as parameters of the * user_creation * procedure.

The code is as follows:

FOR i IN (SELECT community_id, password, username FROM customer WHERE community_id IS NOT NULL AND created_by = 'SRC_GLOB' ) LOOP user_creation (i.community_id,i.password,i.username); END LOOP; COMMIT; 

The user_Creation procedure calls the web service for some business logic, and then updates the table based on the response.

I need to find a way by which I can use multithreading here so that I can run multiple instances of this procedure to speed things up. I know that I can use * DBMS_SCHEDULER * and possibly * DBMS_ALERT * , but I cannot figure out how to use them inside a loop.

Can someone lead me in the right direction?

Thanks Ankur

Answer 1

I would like to close this question. DBMS_SCHEDULER, as well as DBMS_JOB (although preferably DBMS_SCHEDULER), can be used inside the loop to send and execute the job.

For example, here is an example of code using DBMS_JOB that can be called inside a loop:

 ... FOR i IN (SELECT community_id, password, username FROM customer WHERE community_id IS NOT NULL AND created_by = 'SRC_GLOB' ) LOOP DBMS_JOB.SUBMIT(JOB => jobnum, WHAT => 'BEGIN user_creation (i.community_id,i.password,i.username); END;' COMMIT; END LOOP; 

Using commit after SUBMIT will run the task (and therefore the procedure) in parallel.

Answer 2

what you can do represents many tasks at the same time. See Example 28-2 Creating a Lightweight Job Set in a Single Transaction

This populates the pl / sql table with all the jobs you want to send in one tx at the same time. As soon as they are sent (turned on), they will begin to work, how much the system can process, or as much as allowed by the resource manager plan.

The overhead that the light jobs have is very ... minimal / light.