We have batch_table with column batch_id having values 1, 2, 3, 4, 5, 6, 7, 8, 9, 10.
Running sample code for demonstration.
PACKAGE XXX
PROCEDURE YYY
IS
variables...
BEGIN
FOR i IN (SELECT batch_id FROM batch_table)
LOOP
-- Function call
IF get_running_threads('XXEXTRACT_DATA') <= l_max_allowed_threads
THEN
l_request_id :=
fnd_request.submit_request (
application => g_application_name,
program => 'XXEXTRACT_DATA',
description => 'XXEXTRACT_DATA',
start_time => SYSDATE,
sub_request => FALSE,
argument1 => i.batch_id);
ELSE
BEGIN
-- WAIT LOGIC
-- Wait till previous concurrent program completes successfully.
-- Call API FND_CONCURRENT.WAIT_FOR_REQUEST
DBMS_LOCK.sleep(10);
END;
END IF;
END LOOP;
END YYY;
END XXX;
My ask is :
Batch_id 1, 2, 3 successfully processed by calling the concurrent program XXEXTRACT_DATA.
When FOR Loop starts processing batch_id 4, control comes to ELSE part. In the next iteration, this starts with batch_id 5. So batch_id 4 is never processed.
How can I process batch_id 4 ?
Is this possible to achieve without reopening the cursor more than one ?
Pls respond.
Would this work?
PACKAGE XXX
PROCEDURE YYY
IS
variables...
BEGIN
FOR i IN (SELECT batch_id FROM batch_table)
LOOP
-- Function call
IF get_running_threads('XXEXTRACT_DATA') > l_max_allowed_threads
THEN
BEGIN
-- WAIT LOGIC
-- Wait till previous concurrent program completes successfully.
-- Call API FND_CONCURRENT.WAIT_FOR_REQUEST
DBMS_LOCK.sleep(10);
END;
END IF;
--
-- we waited, so now we just go ahead and submit
--
l_request_id :=
fnd_request.submit_request (
application => g_application_name,
program => 'XXEXTRACT_DATA',
description => 'XXEXTRACT_DATA',
start_time => SYSDATE,
sub_request => FALSE,
argument1 => i.batch_id);
END LOOP;
END YYY;
END XXX;