Let’s see how to design concurrent program which spawns multiple child concurrent programs. The master program waits till all child programs are complete.Oracle provides built-in package FND_CONC_GLOBAL and FND_REQUEST to achieve parent-child program using PL/SQL.
- FND_CONC_GLOBAL.SET_REQ_GLOBALS – sets the value of the REQUEST_DATA global variable.
- conc_status – Status of concurrent program like ‘PAUSED’, ‘RUNNING’
- request_data – Value of the global variable
- conc_restart_time – Concurrent restart time
- release_sub_request – Release subrequest
- FND_CONC_GLOBAL.REQUEST_DATA – retrieves the value of the REQUEST_DATA global
- FND_REQUEST.SUBMIT_REQUEST – API to submit a concurrent program from PL/SQL.
- application – short name of the application associated with the concurrent request
- program – short name of the program
- description – description of the request that is displayed in the Concurrent Requests form. You can specify any valid description.
- start_time – when the request should start formatted as HH24: MI or HH24: MI: SS
- sub_request – TRUE – when the request is sub request/child required of calling program.
- argument1…100 – valid input parameter for the program
Let’s design two concurrent programs.
Master Concurrent Program
This is a master program. It does its processing and then launch child concurrent program using fnd_request.submit_request API. It goes in Paused state waiting response from child programs.
CREATE OR REPLACE PROCEDURE parent_prg( p_errbuf OUT VARCHAR2, p_retcode OUT VARCHAR2) IS l_req_data VARCHAR2(30); l_request_id NUMBER; BEGIN --Get value of global variable. It is null initially. l_req_data := fnd_conc_global.request_data; -- If equals to 'END', exit the program with return code '0'. IF l_req_data ='END' THEN RETURN; END IF; -- Set paranet program status as 'PAUSED' and set global variable value to 'END' fnd_conc_global.set_req_globals(conc_status => 'PAUSED' , request_data => 'END'); fnd_file.put_line(fnd_file.log,'Dear workers done my work. Please finish task assigned to you and acknowledge back'); FOR i IN 1..5 LOOP -- Submit child workers l_request_id := fnd_request.submit_request('XXINV' , 'CHILD_PRG' , 'I am child '|| i , NULL , TRUE ); IF l_request_id > 0 THEN fnd_file.put_line(fnd_file.log,'Successfully submitted request ' || l_request_id); ELSE fnd_file.put_line(fnd_file.log,'Error while submitting request ' || fnd_message.get); END IF; END LOOP; EXCEPTION WHEN OTHERS THEN fnd_file.put_line(fnd_file.log,'Unexpected error in procedure ' || SQLERRM); END; /
Child Concurrent Program
Master program spawns these program to complete a task.
CREATE OR REPLACE PROCEDURE child_prg( p_errbuf OUT VARCHAR2, p_retcode OUT VARCHAR2) IS l_req_data VARCHAR2(30); l_request_id NUMBER; BEGIN fnd_file.put_line(fnd_file.log,'Thanks for opportunity. Let me complete task and update you'); --Here you can write logic for child program. We will simply put wait dbms_lock.sleep(20); -- Wait for some time and exit fnd_file.put_line(fnd_file.log,'Complete my work. Passing control back to you'); EXCEPTION WHEN OTHERS THEN fnd_file.put_line(fnd_file.log,'Unexpected error in procedure ' || SQLERRM); END; /
This feature is very useful when you want to submit multiple concurrent programs in parallel. It is like Threading in JAVA.
This is how it submits concurrent programs.