CODEWITHSUNDEEP
oracle-databaseplsqloracle11gschedulingjobs
Jaanna
Jaanna

Reputation: 1670

job scheduling for a few hours everyday

I need to schedule a job starting from 0600 till 1800. The job should run after every two hours. For example 0800, 1000, 1200, 1400, 1600, 1800.

Here is the code I have managed to do so far:

DECLARE
     l_id binary_integer;
     begin
       sys.dbms_job.submit(job => l_id, what => 'integration_export;', interval => 'TRUNC(SYSDATE,''hh24'')+0/24/60');
       sys.dbms_output.put_line(l_id);    
end;

This will, of course, run the job after every 2 hours without stopping at 1801 - 0759. How may I add this restriction? One thing I though is to create another schedule procedure which wakes up at 1801 and changes NEXT_DATE for this job. However, I am wondering if it is a good idea.

Any suggestions?

Thanks in advance :-)

Upvotes: 2

Views: 20998

Answers (2)

DazzaL
DazzaL

Reputation: 21973

dbms_job is old. I'd recomend you use the dbms_scheduler (introduced in Oracle 10g) instead.

dbms_scheduler.create_job(job_name        => 'YOUR_JOB',
                          job_type        => 'PLSQL_BLOCK', 
                          job_action      => 'integration_export;',
                          start_date      => systimestamp,
                          repeat_interval => 'freq=hourly; byhour=8,10,12,14,16,18; byminute=0; bysecond=0;',
                      enabled         => true,
                      auto_drop       => false,
                      comments        => 'some comment about the job');

Upvotes: 7

Alen Oblak
Alen Oblak

Reputation: 3325

Instead of dmbs_job, use the advanced dbms_scheduler. Here is an example:

begin
  DBMS_SCHEDULER.create_job (
    job_name        => 'Integration_export',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'integration_export;',
    start_date      => SYSTIMESTAMP,
    enabled         => TRUE,
    repeat_interval => 'freq=daily; byhour=6,8,10,12,14,16,18; byminute=0; bysecond=0');
end;
/

Upvotes: 2

Related Questions