I could spawn a child process from a standalone pl/sql procedure using dbms_job.submit package. Session of calling procedure is ending as soon as it generates the child process. I want the calling procedure session to be alive and running until all the spawned child processes are alive. Hope it is much more clear now. My question here is how to lock the calling procedure session.


In the procedure below, dbms_jobs are being submitted and I see that the calling procedure is ending the session regardless of whether child processes are being finished or not. I want parent session to be locked until all the jobs are finished and at last it should run control_file_header procedure. Can we make it out?


Example:

--Calling Procedure

i:=0;
lv_batch_id:='115';

for lr in lc_distinct_records(lv_batch_id) loop

i:=i+1;

DBMS_OUTPUT.put_line('Submitting Child Process Number: ' || i);

-- Submitting Child procedure

DBMS_JOB.SUBMIT(i, 'XXGL_JVS_TRANSACTIONS_CONV_PKG.XXGL_VALIDATE_AND_LOAD('||''''||pi_oracle_company_code||''''||',' ||lv_batch_id||','||''''||lr.period_name||''''||','||''''||lr.period_name||''''||');', SYSDATE, NULL);


DBMS_OUTPUT.put_line('Submitted Job Number: ' || i);

-- Load data into base tables

end loop;

-- Generate Control file .
control_file_header (lv_from_period, lv_to_period, pi_oracle_company_code);




Thank you,
Pavan Ranga

-- load data into base tables;
end loop;
commit;

loop
   select count(*) into l_cnt from user_jobs 
    where what like 'XGL_JVS_TRANSACTIONS_CONV_PKG.XXGL_VALIDATE_AND_LOAD(';
   exit when l_cnt = 0;
   dbms_lock.sleep(N); -- you figure out how long you want to wait.
end loop;
-- Generate Control file .
    control_file_header (lv_from_period, lv_to_period, pi_oracle_company_code);