Re: Using DBMS_JOB and DBMS_PIPE to mimic multithreaded PL/SQL application

Hi!

I might have missed something about why you want to use DBMS_PIPE but you could parallel the batch like this:

Start up to 36 parallel job processes (up to 1000 in 9i)

dbms_job.submit( l_job, 'process(p_batchnumber );' );
dbms_job.submit( l_job, 'process(p_batchnumber );' );
dbms_job.submit( l_job, 'process(p_batchnumber );' );

loop

	if(any available job processes AND any more batches left to be run)
		start up x number of new jobs;
	elsif (all batches are processed)
		exit;
	end if,

	dbms_lock.sleep(sleep for a minute or so)

end loop;

To check for available batches to run while in the loop: when the procedure process starts it updates a parameter table that that batchid is running
when the procedure process is done it updates a parameter table that the batchid has finished.

Regards,
Janne!

Paul Jones wrote:

>>BTW, why are you trying to do this?
>>