call dbms_scheduler.create_job in a for loop
85286Sep 27 2007 — edited Oct 9 2007Hi,
I need to loop thru' a set of records in a cursor and kick off a dbms job (job_type = pl/sql block) for each record. The idea is to process the records parallelly.
Considering that processing each record consumes considerable amount of time & resources, is there a limit on the number of jobs that can be running at a time from database point of view or from a recommendation / best practice point of view?
If there is such a limit and say it is 5 and I need to process 8 records then I probably need to kick off 5 jobs & wait for them to finish. Does Oracle notify the creator of the job about its completion or should we be periodically polling for job status?
Can somebody point me to a good example of creating a job using dbms_scheduler, handling errors and doign the required clean up?
Thanks in advance.
S.