Copying Oracle Scheduler jobs with Arguments

Oracle provides dbms_scheduler.copy_job function to copy oracle scheduler jobs which works great. But problem is that it cannot copy  jobs which have  arguments.

Need to copy jobs can arise due to following scenarios

1)Runaway jobs – When Scheduler job is running without any SID. This means OS session is not present and this will not clear. These jobs have to be recreated to resolve the issue.
2)Jobs can not be stopped – many times jobs cannot be stopped by dbms_scheduler.stop_job(<jobname>,true) as they error out with “Internal error”
3)We need to have similar job but under different schema or with different name

I have written below code which would prompt for username and jobname and will copy the job along with its arguments

declare
l_owner varchar2(30) :='&job_owner';
l_old_job varchar2(30) :='&job_name';
l_new_job varchar2(30);
cnt integer;
cursor c_arg is select argument_position,value from DBA_SCHEDULER_JOB_ARGS where job_name = l_old_job and owner = l_owner order by argument_position;
begin
l_new_job :='INTERMEDIATE_CPY';
select count(*) into cnt from dba_scheduler_jobs where job_name = l_new_job and owner = l_owner;
		if ( cnt > 0 ) then
			dbms_output.put_line ('dropping scheduler job INTERMEDIATE_CPY');
			 dbms_scheduler.drop_job(l_owner||'.'||l_new_job,true);
			end if;
sys.dbms_scheduler.copy_job(l_owner||'.'||l_old_job,l_owner||'.'||l_new_job);
sys.dbms_scheduler.drop_job(l_owner||'.'||l_old_job,true);
sys.dbms_scheduler.copy_job(l_owner||'.'||l_new_job,l_owner||'.'||l_old_job);
for v_arg in c_arg
	loop
		dbms_output.put_line('Setting ARGUMENT_POSITION '||v_arg.argument_position||' to value '||v_arg.value);
	 sys.dbms_scheduler.set_job_argument_value(job_name =>l_owner||'.'||l_old_job,ARGUMENT_POSITION=>v_arg.argument_position,ARGUMENT_VALUE =>v_arg.value);
	 end loop;
	 sys.dbms_scheduler.enable(l_owner||'.'||l_old_job);
	sys.dbms_scheduler.drop_job(l_owner||'.'||l_new_job,true);
end;
/

In case you don’t copy the arguments, jobs will fail with status as STOPPED and give REASON as “Job slave process was terminated”. Also it can generate following ORA-07445 error

ORA-07445: exception encountered: core dump [kpubsuuc()+197] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []

Tags: copy_jobsdatabasedbms_scheduleroracle