I am creating jobs on fly by looping through a table as shown below. In the looping process, I am checking if jobs that share the same class are already running and if they are, then skip the loop. For some reason, the view 'user_scheduler_jobs' or 'user_scheduler_running_jobs' always get '0' until the fourth loop where it shows that the three jobs are running. Is what I am doing right way of creating and monitoring the jobs?
for v_loop in (select distinct job_name, job_class, program_name
from job_log
where job_status = 'PENDING' and
job_log_id is null and
job_name not in (select job_name from user_scheduler_jobs where state ='RUNNING')
)
LOOP
select count(*), LISTAGG(job_name, ',') within group (order by null) as job_running
into v_job_cnt, v_job_names
from user_scheduler_jobs
where state = 'RUNNING' and job_class='TEST_JOB_CLASS';
select count(*) into v_job_cnt2
from user_scheduler_running_jobs
where job_name in (select job_name from job_log where job_class='TEST_JOB_CLASS');
dbms_output.put_line('---------CURRENT JOB NAME ' || v_loop.job_name || ' ------------------------------');
dbms_output.put_line('job count 1' || v_job_cnt || ', running job view cnt: ' || v_job_cnt2 || ', all job_names : ' || v_job_names);
dbms_output.new_line();
IF v_job_class='TEST_JOB_CLASS' THEN
IF v_job_cnt2 > 0 THEN
dbms_output.put_line('TEST_JOB_CLASS skipping loop');
CONTINUE;
END IF;
END IF;
BEGIN
sys.dbms_scheduler.create_job(job_name => v_loop.job_name,
program_name => v_loop.program_name,
start_date => to_date(null),
repeat_interval => '',
end_date => to_date(null),
job_class => v_loop.job_class,
enabled => false,
auto_drop => false,
comments => '');
sys.dbms_scheduler.set_job_argument_value(job_name => v_loop.job_name,
argument_name => 'P_JOB_NAME',
argument_value => v_loop.job_name);
sys.dbms_scheduler.set_attribute(name => v_loop.job_name, attribute => 'raise_events', value => sys.dbms_scheduler.job_succeeded + sys.dbms_scheduler.job_failed);
sys.dbms_scheduler.run_job(job_name=>v_loop.job_name, use_current_session => false);
END;
END LOOP;