dbms_scheduler.create_job

Do you know that you can create Schedulers running in db level? Yes. We can create them. Recently I have come a cross a situation, where I had to create a oracle db level scheduler. Requirement was to periodically searching for modified data using a complex query and inserting them to another table. The same thing can be achieved using a application level scheduler job, but we moved to the db level solution. And it was a new experienced for me. So I am ready to share the knowledge I gathered with that task with you !!

BEGIN

dbms_scheduler.create_job (

job_name=> 'Rep_data_pumping',

job_type=> 'STORED_PROCEDURE',

job_action=> 'pumpRepData',

number_of_arguments=>0,

start_date=> TO_DATE('2015/02/07', 'yyyy/mm/dd'),         

repeat_interval=> 'SYSTIMESTAMP + INTERVAL '30' MINUTE',

end_date=>NULL,

job_class=> 'DEFAULT_JOB_CLASS',

enabled=>TRUE,

auto_drop=>FALSE,

comments=>     'pump rep data periodically');

END;

/

This creates the job named Rep_data_pumping which executes the stored procedure named pumpRepData.  And this will be starting on February 07th and then it runs in every 30 minutes. And this job is enabled. You can use the following commands for different actions as described. 

1. How to run a job manually?

call the dbms_scheduler.run_job procedure and the job will start immediately. 

BEGIN
dbms_scheduler.run_job(job_name=> 'Rep_data_pumping');

END;

/

2. How to stop a running job?

call the  dbms_scheduler.stop_job procedure and the job will stop the running job.

BEGIN
dbms_scheduler.stop_job(job_name=> 'Rep_data_pumping');

END;

/

3. How to disable the job?

call the  dbms_scheduler.disable procedure and the job will disable. One thing to note, if the job is running you will get an error. You must first stop the job and then try to disable the job

BEGIN
dbms_scheduler.disable(job_name=> 'Rep_data_pumping');

END;

/

4. How to enable the disabled job?
call the  dbms_scheduler.enable procedure and the job will enable.

BEGIN
dbms_scheduler.enable(job_name=> 'Rep_data_pumping');
END;
/

5. How to drop a job?
call the  dbms_scheduler.drop_job procedure and the job will drop. And again if the job is runing you will get an error. First stop the job and then drop it.

BEGIN
dbms_scheduler.drop_job(job_name=> 'Rep_data_pumping');

END;

/

Hope you can schedule the next job in db level!! :) :)

Cheers!!

'do good and good will come to you'