Oracle DBMS_SCHEDULER looping found error ORA-274278

Question

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.

Whats the best solution for this?Tq

J. Chomel · Answer 1 · 2017-04-27T06:03:40.103

You where actually nearly there!

You just need to wait the end of the RUNPRINT_WELD job you started - it appears it can be longer than 5s -> if not finish, wait another 5s, still with dbms_lock.sleep (5). You can do this by fetching data from dba_scheduler_running_jobs table: the jobs that are actually running:

CREATE OR REPLACE procedure SEAT.ss_print_fundoshi_run_weld_tst (ptype varchar2)
is
  vname varchar2(100);
  v_cnt Number;
  v_dir varchar2(200) := 'SEATMAP';
  fhandle            UTL_FILE.FILE_TYPE;
BEGIN
  for i in (
        select sspf.*
          from ss_print_fundoshi_tmp_file sspf
         where vdesc = ptype
        order by vno;
  ) loop
   vname := 'RUNPRINT_WELD'; -- add a number after job name to differentiate
--   dbms_scheduler.drop_job (vname);  -- why drop it? you must find a way to decide if it really need to be dropped.
   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);
    -- a while loop to wait end of job
    v_cnt := 1;
    WHILE v_cnt>=1
    LOOP
      SELECT count(1) INTO v_cnt 
        FROM dba_scheduler_running_jobs srj
       WHERE srj.job_name IN ('RUNPRINT_WELD')
      ;
      IF v_cnt>0 THEN
        dbms_lock.sleep (5);
      END IF;
    END LOOP;
  end loop;
END;
/

Thanks for your reply.. i've tried with your solution but it cannot dropped because will show error job_name not exists. My Goal is just looping several data to run bat file using dbms_scheduler,, but it keep showing error when new row (because current job is not yet finished) — reefman, Apr 26 '17 at 09:16
@reefman, did it work with the while loop to wait for the job to finish? — J. Chomel, Apr 28 '17 at 05:51
thanks @j-chomel ,, i'll report the result ASAP using your method, because right now i just add dbms_lock.sleep (20). — reefman, Apr 28 '17 at 07:19

Oracle DBMS_SCHEDULER looping found error ORA-274278

1 Answers1