Skip to Main Content
  • Questions
  • Need help working with PL/SQL FOR LOOP

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: January 18, 2021 - 10:00 am UTC

Last updated: January 20, 2021 - 6:04 am UTC

Version: 12.1.0.1

Viewed 100+ times

You Asked

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.

and we said...

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;


More to Explore

Performance

Get all the information about database performance in the Database Performance guide.