Home » SQL & PL/SQL » SQL & PL/SQL » Time Interval in Schedule Job (Oracle 10g)
Time Interval in Schedule Job [message #560813] Tue, 17 July 2012 04:49 Go to next message
Magi1210
Messages: 8
Registered: May 2012
Location: Chennai
Junior Member
Dear Buddies,
How to set time interval in schedule job
i need five minutes interval..

Please help

Thanks in Advance,
Maheswaran Sankarappan R
Re: Time Interval in Schedule Job [message #560820 is a reply to message #560813] Tue, 17 July 2012 05:15 Go to previous messageGo to next message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://lmgtfy.com/?q=oracle+Time+Interval+in+Schedule+Job

Regards
Michel

[Updated on: Tue, 17 July 2012 05:15]

Report message to a moderator

Re: Time Interval in Schedule Job [message #560831 is a reply to message #560820] Tue, 17 July 2012 06:13 Go to previous messageGo to next message
Littlefoot
Messages: 19817
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
"5 minutes" is set relative to "1 day", so you need to calculate how many minutes are there in a day. Obviously:
1 day = 24 hours,
1 hour = 60 minutes

Interval you are looking for is then "5 / (24 * 60)". If you create a job that looks exactly like that, it is easy to read it.

For example, a job that inserts SYSDATE into a table every minute might look like this:
SQL> desc test1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL                                                DATE

SQL> select sysdate from dual;

SYSDATE
-------------------
17.07.2012 13:02:00

SQL> declare
  2    x number;
  3  begin
  4    dbms_job.submit
  5    ( job       => x
  6     ,what      => 'begin insert into test1 values (sysdate); end;'
  7     ,next_date => sysdate
  8     ,interval  => 'SYSDATE + 1 / (24 * 60)'
  9     ,no_parse  => false
 10    );
 11
 12    dbms_output.put_line('Job Number is: ' || to_char(x));
 13
 14    commit;
 15  end;
 16  /
Job Number is: 1408

PL/SQL procedure successfully completed.

SQL> select sysdate, col from test1 order by col;

SYSDATE             COL
------------------- -------------------
17.07.2012 13:02:24 17.07.2012 13:02:03

SQL> select sysdate, col from test1 order by col;

SYSDATE             COL
------------------- -------------------
17.07.2012 13:03:08 17.07.2012 13:02:03
17.07.2012 13:03:08 17.07.2012 13:03:03

SQL> select sysdate, col from test1 order by col;

SYSDATE             COL
------------------- -------------------
17.07.2012 13:04:07 17.07.2012 13:02:03
17.07.2012 13:04:07 17.07.2012 13:03:03
17.07.2012 13:04:07 17.07.2012 13:04:03

SQL>
Re: Time Interval in Schedule Job [message #561026 is a reply to message #560831] Wed, 18 July 2012 06:19 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Excellent demonstration , littlefoot. i got a good understanding of what dbms_job.submit does , but some times if i give this interval in hours let say 3 hours and my timezone is saudi arabia , its running after 6 hours , is there any way to fix this issue.
Re: Time Interval in Schedule Job [message #561030 is a reply to message #561026] Wed, 18 July 2012 06:35 Go to previous messageGo to next message
cookiemonster
Messages: 11203
Registered: September 2008
Location: Rainy Manchester
Senior Member
Arif - you must know by now that an actual copy and paste of the code you used is far clearer to everyone than a vague description.
Re: Time Interval in Schedule Job [message #561039 is a reply to message #561030] Wed, 18 July 2012 07:06 Go to previous message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also you can read DBMS_JOB.

Regards
Michel
Previous Topic: Remote Procedure
Next Topic: Reading Flat File with Header (2 Merged)
Goto Forum:
  


Current Time: Thu Nov 27 15:22:03 CST 2014

Total time taken to generate the page: 0.08930 seconds