Home » SQL & PL/SQL » SQL & PL/SQL » Oracle dbms scheduler (Oracle 11g)
Oracle dbms scheduler [message #633694] Tue, 24 February 2015 05:27 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #633699 is a reply to message #633698] Tue, 24 February 2015 05:48 Go to previous messageGo to next message
veeraiahmannem
Messages: 7
Registered: September 2013
Location: Bangalore
Junior Member
Hi Lalit,

Thanks for the reply.

i don't want to run it on hourly basis, i want it to run at every 40 minutes intervals over a period of time for every day.

here my job should start at 6 PM, 6.40 PM, 7.20 PM,8.00 etc.. and completes final run at 10:40 PM

i want to know how to define these arguments.

Thanks,
Veeru





Re: Oracle dbms scheduler [message #633701 is a reply to message #633699] Tue, 24 February 2015 05:52 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
Look up the calendaring functions in the docs. I think there is an example of almost exactly what you want. You can use multiple BY filters.

[Updated on: Tue, 24 February 2015 05:52]

Report message to a moderator

Re: Oracle dbms scheduler [message #633702 is a reply to message #633694] Tue, 24 February 2015 05:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is easier with dbms_scheduler but you can do it with dbms_job, have a look at DBMS JOB complex scheduling wiki page.

Re: Oracle dbms scheduler [message #633705 is a reply to message #633702] Tue, 24 February 2015 06:11 Go to previous messageGo to next message
veeraiahmannem
Messages: 7
Registered: September 2013
Location: Bangalore
Junior Member
hi Michel,

dbms_scheduler also should be fine, i can create a scheduler and call the scheduler inside the job.

Thanks,
Veeru
Re: Oracle dbms scheduler [message #633798 is a reply to message #633705] Tue, 24 February 2015 23:52 Go to previous messageGo to next message
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 #633814 is a reply to message #633798] Wed, 25 February 2015 05:30 Go to previous messageGo to next message
veeraiahmannem
Messages: 7
Registered: September 2013
Location: Bangalore
Junior Member
if we define the repeat interval minutes attribute to 40 ,it runs at 7:40 PM in the second iteration, but i wanted to run at 7:20 PM.

Thanks,
Veeru
Re: Oracle dbms scheduler [message #633815 is a reply to message #633814] Wed, 25 February 2015 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So adjust the parameters as you want.
Don't just wait for us to do your job.

Re: Oracle dbms scheduler [message #633821 is a reply to message #633815] Wed, 25 February 2015 07:13 Go to previous message
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

Previous Topic: using function in select statement giving same value
Next Topic: creation of records with respect to the delimited data from a column of base table
Goto Forum:
  


Current Time: Fri Apr 26 05:31:01 CDT 2024