Running Oracle Jobs with DBMS_SCHEDULER

--create program
BEGIN
DBMS_SCHEDULER.create_program(program_name => 'TEST_PRG',
program_type => 'STORED_PROCEDURE',
program_action => 'EVENT_HANDLING.TEST',
number_of_arguments => 4,
enabled => FALSE,
comments => 'AAE event fetcher Program');
END;
/
SELECT * FROM all_scheduler_programs p WHERE p.program_name = 'TEST_PRG';
--create program arguments
BEGIN
DBMS_SCHEDULER.define_program_argument(program_name => 'TEST_PRG',
argument_position => 1,
argument_name => 'pin_Start',
argument_type => 'NUMBER',
default_value => '1',
out_argument => FALSE);
DBMS_SCHEDULER.define_program_argument(program_name => 'TEST_PRG',
argument_position => 2,
argument_name => 'pin_End',
argument_type => 'NUMBER',
default_value => '10',
out_argument => FALSE);
DBMS_SCHEDULER.define_program_argument(program_name => 'TEST_PRG',
argument_position => 3,
argument_name => 'pin_DebugMode',
argument_type => 'NUMBER',
default_value => '0',
out_argument => FALSE);
DBMS_SCHEDULER.define_program_argument(program_name => 'TEST_PRG',
argument_position => 4,
argument_name => 'pin_Rem',
argument_type => 'NUMBER',
default_value => '0',
out_argument => FALSE);
END;
/
SELECT * FROM all_scheduler_program_args WHERE program_name = 'TEST_PRG';
--create job
BEGIN
DBMS_SCHEDULER.create_job(job_name => 'TEST_JOB',
program_name => 'TEST_PRG',
start_date => NULL,
repeat_interval => 'FREQ=MINUTELY;INTERVAL=15',
end_date => NULL,
enabled => FALSE,
auto_drop => FALSE,
comments => 'AAE event fetcher Job');
END;
/
SELECT * FROM all_scheduler_jobs j WHERE j.job_name = 'TEST_JOB';
--enable program and program
BEGIN
DBMS_SCHEDULER.enable(NAME => 'TEST_PRG');
DBMS_SCHEDULER.enable(NAME => 'TEST_JOB');
END;
/
--run job
BEGIN
DBMS_SCHEDULER.RUN_JOB(job_name => 'TEST_JOB',
use_current_session => TRUE);
END;
/
SELECT * FROM all_scheduler_running_jobs WHERE job_name = 'TEST_JOB';
SELECT * FROM all_scheduler_job_log l WHERE l.job_name = 'TEST_JOB' order by 1 DESC;
--stop job
BEGIN
DBMS_SCHEDULER.stop_job(job_name => 'TEST_JOB', force => TRUE);
END;
/
--drop prg args
BEGIN
DBMS_SCHEDULER.drop_program_argument(program_name => 'TEST_PRG',
argument_position => 1);
DBMS_SCHEDULER.drop_program_argument(program_name => 'TEST_PRG',
argument_position => 2);
DBMS_SCHEDULER.drop_program_argument(program_name => 'TEST_PRG',
argument_position => 3);
DBMS_SCHEDULER.drop_program_argument(program_name => 'TEST_PRG',
argument_position => 4);
END;
/
SELECT * FROM all_scheduler_running_jobs WHERE job_name = 'TEST_JOB';
--drop program, job and arguments
BEGIN
DBMS_SCHEDULER.disable(NAME => 'TEST_PRG', force => TRUE);
DBMS_SCHEDULER.drop_program(program_name => 'TEST_PRG',
force => TRUE);
DBMS_SCHEDULER.drop_job(job_name => 'TEST_JOB', force => TRUE);
END;
/
SELECT * FROM all_scheduler_job_run_details order by 1 desc;
--change repeat interval
BEGIN
DBMS_SCHEDULER.set_attribute(NAME => 'TEST_JOB',
attribute => 'repeat_interval',
VALUE => 'FREQ=MINUTELY;INTERVAL=1');
END;
/
SELECT * FROM all_scheduler_jobs j WHERE j.job_name = 'TEST_JOB';