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 Go to next message
chat2raj.s
Messages: 161
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 Go to previous messageGo to next message
chat2raj.s
Messages: 161
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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 #664458 is a reply to message #664457] Wed, 19 July 2017 01:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also, I didn't have your feedback in your previous topic, did it work in the end?

[Updated on: Wed, 19 July 2017 08:20]

Report message to a moderator

Re: DBMS_JOB (NEXT_DATE / INTERVAL) [message #664459 is a reply to message #664458] Wed, 19 July 2017 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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:35
Ok 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 Go to previous messageGo to next message
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 #664467 is a reply to message #664466] Wed, 19 July 2017 10:05 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
Quote:
repeat_interval => 'BY_HOUR=4,10,16,20',
Is this a typo and should be?
repeat_interval => 'BYHOUR=4,10,16,20',
Re: DBMS_JOB (NEXT_DATE / INTERVAL) [message #664468 is a reply to message #664467] Wed, 19 July 2017 12:23 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
_jum wrote on Wed, 19 July 2017 10:05
Quote:
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.
Previous Topic: Solution for ORA-06550: line 6, column 31: PLS-00103: Encountered the symbol ")" when expecting one
Next Topic: ORA-1555 during pl/sql pocedure
Goto Forum:
  


Current Time: Thu Mar 28 11:26:42 CDT 2024