Skip to Main Content
  • Questions
  • Oracle DBMS_SCHEDULER looping found error ORA-27478

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Arief.

Asked: April 26, 2017 - 9:04 am UTC

Last updated: April 27, 2017 - 9:23 am UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

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

and Chris said...

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.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Multiple Serial job

Arief Rachman, April 27, 2017 - 12:37 am UTC

Thanks Tom,

My goal to achieved is to running multiple job with same job name because it must run serially. so previous job must finish first.

So, how can we running this looping process then if previous job still running , hold next job then running after previous job finish.

dbms_scheduler.drop_job (job_name, defer => true);

is this code can be implement?

thanks again.
Chris Saxon
April 27, 2017 - 9:23 am UTC

It's Chris, not Tom, but hey ;)

Anyway, you just put the defer parameter in your call to drop_job.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library