Oracle dbms scheduler [message #633694] |
Tue, 24 February 2015 05:27 |
|
veeraiahmannem
Messages: 7 Registered: September 2013 Location: Bangalore
|
Junior Member |
|
|
Hi,
How to create a dbms job that needs to be run for every 40 mins starting from 6 PM and ending at 11 pm daily.
Thanks,
Veeru
|
|
|
Re: Oracle dbms scheduler [message #633697 is a reply to message #633694] |
Tue, 24 February 2015 05:39 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
What have you tried so far?
Did you have a look at these arguments?
start_date => <start_timestamp>,
repeat_interval => 'freq=hourly; byminute=0',
end_date => <end_timestamp>,
|
|
|
Re: Oracle dbms scheduler [message #633698 is a reply to message #633694] |
Tue, 24 February 2015 05:39 |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
You will find it easier to do this with DBMS_SCHEDULER than with DBMS_JOB. THe calendaring capabilities of the Scheduler are much superiot to those of DBMS_JOB.
--update: oh, I just saw Lalit's answer, and re-read the question title. From your text, I though you were using the old job package. Sorry.
[Updated on: Tue, 24 February 2015 05:41] Report message to a moderator
|
|
|
|
|
|
|
Re: Oracle dbms scheduler [message #633798 is a reply to message #633705] |
Tue, 24 February 2015 23:52 |
|
sss111ind
Messages: 634 Registered: April 2012 Location: India
|
Senior Member |
|
|
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => 'test_program',
PROGRAM_TYPE => 'STORED_PROCEDURE',
program_action => 'sal_percent',
ENABLED => true,
comments => 'Program ');
DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name => 'test_schedule',
start_date => SYSDATE,
repeat_interval => 'freq=minutely; byhour=18,19,20,21,22,23;interval =40',
END_DATE => NULL,
comments => 'Repeats every 40 minutes from 6pm to 11 pm');
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'test_job',
program_name => 'test_program',
schedule_name => 'test_schedule',
ENABLED => TRUE,
comments => 'Job ');
END;
|
|
|
|
|
Re: Oracle dbms scheduler [message #633821 is a reply to message #633815] |
Wed, 25 February 2015 07:13 |
|
sss111ind
Messages: 634 Registered: April 2012 Location: India
|
Senior Member |
|
|
You can see the next run date by this and it runs exactly after 40 minutes during the mentioned time interval.
SET SERVEROUTPUT ON;
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
Session altered.
DECLARE
start_date TIMESTAMP;
return_date_after TIMESTAMP;
next_run_date TIMESTAMP;
BEGIN
start_date :=trunc(sysdate)+18/24;
return_date_after := start_date;
FOR i IN 1..20 LOOP
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(
'freq=minutely; byhour=18,19,20,21,22,23;interval =40',
start_date, return_date_after, next_run_date);
DBMS_OUTPUT.PUT_LINE('next_run_date: ' || next_run_date);
return_date_after := next_run_date;
END LOOP;
END;
[Updated on: Wed, 25 February 2015 07:21] Report message to a moderator
|
|
|