Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_JOB (NEXT_DATE / INTERVAL) (Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production)
DBMS_JOB (NEXT_DATE / INTERVAL) [message #664453] |
Wed, 19 July 2017 00:34 |
chat2raj.s
Messages: 163 Registered: October 2010 Location: Chennai, India
|
Senior Member |
|
|
I want the below job to start at 4 am and fire every 6 hours. But when the date changes, again it has to start at 4 am. Will this work ?
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'CHK_RAWDATA_GEN_STATUS;'
,next_date => trunc(sysdate+1)+4/24
,interval => 'sysdate+6/24'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
|
|
|
Re: DBMS_JOB (NEXT_DATE / INTERVAL) [message #664454 is a reply to message #664453] |
Wed, 19 July 2017 00:50 |
chat2raj.s
Messages: 163 Registered: October 2010 Location: Chennai, India
|
Senior Member |
|
|
Hope the below code will work.
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'CTC_CHK_LG_RAWDATA_GEN_STATUS;'
,next_date => trunc(sysdate+1)+4/24
,interval => 'F_DCALC_NEXTRUNDATE'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/
CREATE OR REPLACE FUNCTION F_DCALC_NEXTRUNDATE RETURN DATE AS
BEGIN
IF EXTRACT(HOUR FROM CAST(SYSDATE AS TIMESTAMP)) > '20' THEN
RETURN trunc(sysdate+1)+4/24 ;
ELSE
RETURN sysdate+6/24;
END IF;
END;
|
|
|
Re: DBMS_JOB (NEXT_DATE / INTERVAL) [message #664457 is a reply to message #664453] |
Wed, 19 July 2017 01:53 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:Will this work ?
SQL> with
2 dates (line, dt) as (
3 select 1 line, trunc(sysdate+1)+4/24 dt from dual
4 union all
5 select line+1, dt+6/24
6 from dates d
7 where line < 10
8 )
9 select * from dates
10 /
LINE DT
---------- -------------------
1 20/07/2017 04:00:00
2 20/07/2017 10:00:00
3 20/07/2017 16:00:00
4 20/07/2017 22:00:00
5 21/07/2017 04:00:00
6 21/07/2017 10:00:00
7 21/07/2017 16:00:00
8 21/07/2017 22:00:00
9 22/07/2017 04:00:00
10 22/07/2017 10:00:00
Yes.
However there will be a slight drift at each execution and so the date will shift, something like:
SQL> with
2 dates (line, dt) as (
3 select 1 line, trunc(sysdate+1)+4/24 dt from dual
4 union all
5 select line+1, dt+6/24+dbms_random.value(10,300)/86400
6 from dates d
7 where line < 10
8 )
9 select * from dates
10 /
LINE DT
---------- -------------------
1 20/07/2017 04:00:00
2 20/07/2017 10:01:45
3 20/07/2017 16:03:43
4 20/07/2017 22:07:38
5 21/07/2017 04:08:27
6 21/07/2017 10:08:39
7 21/07/2017 16:10:22
8 21/07/2017 22:11:10
9 22/07/2017 04:15:58
10 22/07/2017 10:16:44
So you have to fix the current date to round it to your 6 hours bound:
SQL> with
2 dates as (
3 select sysdate dt,
4 trunc(sysdate)+(4+trunc((to_number(to_char(sysdate,'HH24'))-4)/6)*6)/24 dt_fixed
5 from dual
6 )
7 select dt, dt_fixed, dt_fixed+6/24 next_dt
8 from dates
9 /
DT DT_FIXED NEXT_DT
------------------- ------------------- -------------------
19/07/2017 08:53:07 19/07/2017 04:00:00 19/07/2017 10:00:00
|
|
|
|
Re: DBMS_JOB (NEXT_DATE / INTERVAL) [message #664459 is a reply to message #664458] |
Wed, 19 July 2017 01:58 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
And in the previous ones which end with:
Michel Cadot wrote on Sun, 26 February 2017 09:06
Thanks for sharing the idea, let us know if you succeed with it.
chat2raj.s wrote on Thu, 08 December 2016 13:35Ok Let me check that too.
...
But we don't know what it came with.
Please update them to let us and future readers know.
[Updated on: Wed, 19 July 2017 08:21] Report message to a moderator
|
|
|
Re: DBMS_JOB (NEXT_DATE / INTERVAL) [message #664466 is a reply to message #664453] |
Wed, 19 July 2017 07:31 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
Instead of dbms_job, use dbms_scheduler - much more flexible.
Something like this. Note the use of 'repeat interval'
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'oe.my_job1',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN CHK_RAWDATA_GEN_STATUS; END;',
start_date => '15-JUL-08 1.00.00AM US/Pacific',
repeat_interval => 'BY_HOUR=4,10,16,20',
end_date => '15-SEP-08 1.00.00AM US/Pacific',
enabled => TRUE,
comments => 'Gather table statistics');
END;
/
|
|
|
|
Re: DBMS_JOB (NEXT_DATE / INTERVAL) [message #664468 is a reply to message #664467] |
Wed, 19 July 2017 12:23 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
_jum wrote on Wed, 19 July 2017 10:05Quote:repeat_interval => 'BY_HOUR=4,10,16,20',
Is this a typo and should be?
repeat_interval => 'BYHOUR=4,10,16,20',
Correct. there may be some other typos as well, but the point is that dbms_scheduler gives better options that dbms_job, and that those options will do exactly what the OP is after.
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:43:39 CDT 2024
|