Implementing Multithreading in Oracle Procedures

Question

I am working on Oracle 10gR2.

And here is my problem -

I have a procedure, lets call it *proc_parent* (inside a package) which is supposed to call another procedure, lets call it *user_creation*. I have to call *user_creation* inside a loop, which is reading some columns from a table - and these column values are passed as parameters to the *user_creation* procedure.

The code is like this:

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;

user_Creation procedure is invoking a web service for some business logic, and then based on the response updates a table.

I need to find a way by which I can use multi-threading here, so that I can run multiple instances of this procedure to speed up things. I know I can use *DBMS_SCHEDULER* and probably *DBMS_ALERT* but I am not able to figure out, how to use them inside a loop.

Can someone guide me in the right direction?

Thanks, Ankur

Solution 1

I would like to close this question. DBMS_SCHEDULER as well as DBMS_JOB (though DBMS_SCHEDULER is preferred) can be used inside the loop to submit and execute the job.

For instance, here's a sample code, using DBMS_JOB which can be invoked 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 a commit after SUBMIT will kick off the job (and hence the procedure) in parallel.

Solution 2

what you can do is submit lots of jobs in the same time. See Example 28-2 Creating a Set of Lightweight Jobs in a Single Transaction

This fills a pl/sql table with all jobs you want to submit in one tx, all at the same time. As soon as they are submitted (enabled) they will start running, as many as the system can handle, or as many as are allowed by a resource manager plan.

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