0

I want to schedule a dbms scheduler job in plsql developer to run every day from 4pm to 6am and the frequency within this range should be every 15 minutes. and a second job to run 6am to 4pm. I am not 100% sure how to do it but i thought about writing a small code in the job action before calling my package which checks the sysdate.If its within range go on and if not return null. But how do I check if sysdate is currently between 06pm and 06am?

The below code is not working. I have also tried using to_date('16:00', 'HH24:MI')

begin
  
  
if to_char(sysdate, 'HH24:MI') between to_char('16:00', 'HH24:MI') and to_char('06:00', 'HH24:MI') then 

dbms_output.put_line('within range');
-- call loader package
   
else
  
dbms_output.put_line('not within range');
-- return null do nothing

end if;

  
end;

Thank you all.

Swoop
  • 49
  • 1
  • 10
  • 4
    Look into scheduler windows – Paul W Jun 05 '23 at 14:28
  • you mean even if job frequency set to every 15 minutes can set 'by hour' to 16,17,18,19 ... ? – Swoop Jun 05 '23 at 14:33
  • [DBMS_SCHEDULER.CREATE_SCHEDULE](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SCHEDULER.html#GUID-91D53EDA-72FD-452F-891B-FE266CAC8615) and [DBMS_SCHEDULER.CREATE_WINDOW](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SCHEDULER.html#GUID-A8AF780E-2806-4504-BEBF-8D21430DBF85) – astentx Jun 05 '23 at 14:44

1 Answers1

0

You are probably better off with gaining an understanding of the interval/frequency language that the scheduler offers. For example you could do something like:

FREQ=HOURLY,BYHOUR=0,1,2,3,4,5,6,16,17,18,19,20,21,22

but if you just want to alter your existing code to make it work, then BETWEEN must take a lower and upper bound, so you would need two ranges

( to_char(sysdate, 'HH24MI') between '1600' and '2359'  ) or
( to_char(sysdate, 'HH24MI') between '0000' and '0600'  )

Also, check out this post to see how you can use the calendar string functions to make sure you're getting the correct execution times

https://connor-mcdonald.com/2016/04/01/understanding-scheduler-syntax/

Connor McDonald
  • 10,418
  • 1
  • 11
  • 16