CODEWITHSUNDEEP
oracle-databasedbms-job
user1783170
user1783170

Reputation: 113

Schedule Oracle Jobs

I have created an oracle jobs that triggered my stored procedure to run everyday at 5 AM.But i want to restrict the job to run only from Monday to Friday at 5 AM.Here is the job that i have written:

 DECLARE 
   X NUMBER;
 BEGIN  
   SYS.DBMS_JOB.SUBMIT  ( 
     job       => X    ,
     what      => 'BEGIN  usp_procedurename(''ENV''); END;'   ,
     next_date => to_date('10/01/2014 05:00:00','dd/mm/yyyy hh24:mi:ss')   ,
     interval  => 'SYSDATE + 1'   ,
     no_parse  => FALSE  );  
   SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
   COMMIT;
 END;

Is there is some way that i can change the interval that will restrict the job to run only from Monday to Friday at 5 AM or is there any other way ?

Any help on this is appreciated. Thanks

Upvotes: 0

Views: 957

Answers (1)

Frank Schmitt
Frank Schmitt

Reputation: 30835

Don't use dbms_job - it has long since been deprecated and superseded by dbms_scheduler.

With dbms_scheduler, this should do what you want:

begin
  dbms_scheduler.create_job(
      job_name => 'MY_JOB'
     ,job_type => 'PLSQL_BLOCK'
     ,job_action => 'BEGIN  usp_procedurename(''ENV''); END;'
     ,start_date      => to_timestamp_tz('2014-01-09 12:00:00 Europe/Berlin',
                                         'yyyy-mm-dd hh24:mi:ss tzr')
     ,repeat_interval => 'FREQ=DAILY;BYHOUR=05;BYDAY=Mon,Tue,Wed,Thu,Fri'
     ,enabled => TRUE
     ,comments => 'my job (runs Monday to Friday at 05:00)');
end;

Note: You should always use a timestamp with timezone for the start date; otherwise, your job will run at different times when your country switches from/to daylight saving time.

Upvotes: 2

Related Questions