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.
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:
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
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/