Hi,

There is a SQL program whose job is to pick up data from multiple tables based on an application ID and
populate an MIS table. There are 25-30 queries written to fetch data from multiple tables for an application ID.
Data is pulled into collections and there is a bulk insert for every 5000 rows.
The operation is done in 2 parallel jobs in PLSQL program using dbms_job.

--------------
DECLARE
V_LOG_FLAG VARCHAR2(1) := 'Y';
vJob1 number := 1;
vJob2 number := 2;
BEGIN


dbms_job.submit(job => vJob1, what => 'CREDIT_CARD_MIS_DUMP_PKG.MAIN_PROC(1,NULL);');

dbms_output.put_line(' Job1: ' || TO_CHAR(vJob1));

dbms_job.submit(job => vJob2, what => 'CREDIT_CARD_MIS_DUMP_PKG.MAIN_PROC(2,NULL);');

dbms_output.put_line(' Job2: ' || TO_CHAR(vJob2));


dbms_job.run(vJob1,false);
dbms_job.run(vJob2,false);

END;
/
---------------
The above works fine for a data volume of 20-25K application ID`s.
When the load is above 40-50K Id`s , the PLSQL prompt would simply hang and not end even though
the data is backend tables would be correct.

What could be the reason for the above. Please suggest.

Thanks
Kunal

SQL> declare
  2     v_log_flag varchar2 ( 1 ) := 'Y';
  3     vjob1      number         := 1;
  4     vjob2      number         := 2;
  5  begin
  6     dbms_job.submit ( job => vjob1, what => '
  7  begin dbms_output.put_line(to_char(sysdate, ''hh24:mi:ss'')); dbms_lock.sleep(10); end;', next_date => null ) ;
  8     dbms_output.put_line ( ' Job1: ' || to_char ( vjob1 ) ) ;
  9     dbms_job.submit ( job => vjob2, what => '
 10  begin dbms_output.put_line(to_char(sysdate, ''hh24:mi:ss'')); dbms_lock.sleep(10); end;', next_date => null ) ;
 11     dbms_output.put_line ( ' Job2: ' || to_char ( vjob2 ) ) ;
 12
 13     dbms_job.run ( vjob1,false ) ;
 14     dbms_job.run ( vjob2,false ) ;
 15  end;
 16  /
 Job1: 17
 Job2: 18
02:28:19
02:28:29

PL/SQL procedure successfully completed.

Elapsed: 00:00:20.44