dbms_scheduler jobs inside the same package

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?

Related questions 1 Is it possible to create a job from within a DBMS_SCHEDULER job? 0 Associating job with job class 0 Oracle: Error launching job through dbms_scheduler Related questions 1 Is it possible to create a job from within a DBMS_SCHEDULER job? 0 Associating job with job class 0 Oracle: Error launching job through dbms_scheduler 3 Oracle Scheduler – Creating a complex schedule with a single job 0 Schedule Oracle Jobs 2 Creating DBMS_SCHEDULER job for oracle 3 Creating a dbms_scheduler.create_job with arguments 0 Create oracle scheduler job 0 Oracle: execute a Job dbms_scheduler 3 DBMS_SCHEDULER in apex oracle Load 7 more related questions Show fewer related questions

Know someone who can answer? Share a link to this question via email, Twitter, or Facebook.