SQL & PL/SQL

Hi All,

I am working on Oracle 11g. I have to implement multithreading/asynchronous processing in one of my applications.

There is a table, tab1 which contains two columns - ID and SQL. The SQL column is of CLOB datatype and contains a Dynamic SQL statement. This table has around 100 records and can contain upto 1000 (not more than that). The intent of these Dynamic SQLs is to update one of the tables and the dynamic sql is quite big (creates temp tables, business logic application, data is merged and finally updates are done, again based on rules). As of now, the application was executing these queries in sequential manner, through a simple loop.

I want to explore how can I introduce multithreading here, so that I can run the sqls in multiple threads. I know, I can use DBMS_SCHEDULER, but I am struggling at - how to break those 100 records into say 10 sessions at a time? I can track the IDs which have been processed by updating an update_date column in the above mentioned table.

Any suggestion will be highly appreciated which can put me in right direction.

If the problem statement is not clear, do let me know.