I've some script for running bat file windows using DBMS_SCHEDULER. But there is some error when there is looping with several data.
CREATE OR REPLACE procedure SEAT.ss_print_fundoshi_run_weld_tst (ptype varchar2)
is
cursor c_data is
select * from ss_print_fundoshi_tmp_file
where vdesc = ptype
order by vno;
vname varchar2(100);
v_dir varchar2(200) := 'SEATMAP';
fhandle UTL_FILE.FILE_TYPE;
BEGIN
for i in c_data loop
vname := 'RUNPRINT_WELD';-;
dbms_scheduler.drop_job (vname);
dbms_scheduler.create_job(
job_name => vname,
job_type => 'EXECUTABLE',
job_action => 'C:\Windows\System32\cmd.exe',
job_class => 'DEFAULT_JOB_CLASS',
--comments => 'test job',
auto_drop => false,
number_of_arguments => 3,
enabled => FALSE);
dbms_scheduler.set_job_argument_value( job_name => vname, argument_position => 1, argument_value => '/q');
dbms_scheduler.set_job_argument_value( job_name => vname, argument_position => 2, argument_value => '/c');
dbms_scheduler.set_job_argument_value( job_name => vname, argument_position => 3, argument_value => '"'||i.vfile||'"');
dbms_scheduler.enable( vname);
dbms_lock.sleep(5);
end loop;
END;
But because there is several data then job keep saying that job with job_name RUNPRINT_WELD is still running, so it cannot drop job.
If only have one row there is running well.
How to know or hold next process until current process is finish.
Whats the best solution for this?Tq
You've got a few options, including:
- Force the job to stop, even though the job is still running:
dbms_scheduler.drop_job (job_name, force => true);
Probably not what you want, because you don't to stop something half-way
- Wait until the previous execution completes using the defer option:
dbms_scheduler.drop_job (job_name, defer => true);
- Submit the jobs with different names in the first place! This assumes it's fine to run multiple copies of the executable with different parameters at the same time.