multithreadingoracleplsqloracle10gdbms-scheduler

Implementing Multithreading in Oracle Procedures


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

  • 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.