Environment: Oracle 12.2 64-bit under Linux.
Job_queue_processes = 4000
Aq_tm_processes = 1
I wrote a package, that has a three procedures inside, say, MYPKG. First procedure is for client requests from web application, say ProcWeb. This procedure creates two jobs and waits for their finish inside loop. Both jobs are disposable and will be stopped and dropped from MYPKG.ProcWeb after usage. The procedures for these jobs are also inside package – ProcTop and ProcBottom. This is how it’s declared inside MYPKG.ProcWeb:
l_jobtop := dbms_scheduler.generate_job_name('TOP_JOB_');
l_jobbottom := dbms_scheduler.generate_job_name('BOTTOM_JOB_');
dbms_scheduler.create_job(job_name => l_jobtop,
job_type => 'STORED_PROCEDURE',job_action => 'MYPKG.PROCTOP');
dbms_scheduler.create_job(job_name => l_jobbottom,
job_type => 'STORED_PROCEDURE',job_action => 'MYPKG.PROCBOTTOM');
…
dbms_scheduler.run_job(l_jobtop, use_current_session=>false);
dbms_scheduler.run_job(l_jobbottom, use_current_session=>false);
During the first ten days after the package was installed on the database everything was fine. Then the weird things begin - one job is starting, but another – never, or with huge delay. So I wrote a standalone procedures ProcTop and ProcBottom and re-declared creation of jobs:
dbms_scheduler.create_job(job_name => l_jobtop,
job_type => 'STORED_PROCEDURE',job_action => 'PROCTOP');
dbms_scheduler.create_job(job_name => l_jobbottom,
job_type => 'STORED_PROCEDURE',job_action => 'PROCBOTTOM');
It’s hard to explain… but observation shows that calling of standalone procedures instead of calling procedures from package is much more stable. Both jobs start with no problem. What is the hidden problem to have executable block of job inside the package that creates the job?
