This discussion is archived
6 Replies Latest reply: Jan 3, 2013 9:07 AM by hh*394375*91 RSS

Problem scheduling procedure to run at 3 different times

hh*394375*91 Newbie
Currently Being Moderated
I created the following statement to have a simple procedure to run at 3 different time.

repeat_interval => 'freq=DAILY; BYHOUR=12,13,14;',

The job runs the moment I schedule it but I did not see it kick off at the other times.

What did I do wrong and I check the time of the db

The complete scheduling statement.
begin
Dbms_Scheduler.create_job(
job_name => 'DEMO_HALL_TEST_SCHEDULE',
job_type => 'STORED_PROCEDURE',
job_action => 'HR.a_simple_subprogram',
start_date      => SYSTIMESTAMP,
repeat_interval => 'freq=DAILY; BYHOUR=12,13,14;',
enabled => TRUE,
comments => 'Demo for job schedule.');
END;
/
PROCEDURE
create or replace
procedure a_simple_subprogram
is
begin
  null;
end;
  • 1. Re: Problem scheduling procedure to run at 3 different times
    rp0428 Guru
    Currently Being Moderated
    >
    I created the following statement to have a simple procedure to run at 3 different time.

    repeat_interval => 'freq=DAILY; BYHOUR=12,13,14;',

    The job runs the moment I schedule it but I did not see it kick off at the other times.
    >
    Are you sure the job doesn't run at those times?

    The job doesn't do anything and takes no time at all to execute so how would you 'see it kick off'?

    How are you trying to monitor whether it runs or not?

    Have the procedure write to a log table or something so you can tell if the job has executed.
  • 2. Re: Problem scheduling procedure to run at 3 different times
    hh*394375*91 Newbie
    Currently Being Moderated
    I am using sql developer. With it I can look at the run log. It displays information there with a status of succeeded.

    I will change the procedure to write something.

    Howard

    Edited by: hh**** on Jan 2, 2013 4:53 PM
  • 3. Re: Problem scheduling procedure to run at 3 different times
    rp0428 Guru
    Currently Being Moderated
    >
    I am using sql developer. With it I can look at the run log. It displays information there with a status of succeeded
    >
    What 'run log'? We can only go by what you post and the information you provide.

    After each execution you should be able to query the job status, when it ran, what the result was and when it is scheduled to run again.

    Post that information.
  • 4. Re: Problem scheduling procedure to run at 3 different times
    838196 Newbie
    Currently Being Moderated
    Hi,
    you didn't set the minutes. So they are derived from the start date. Probably the job will start at some time between 12 and 13
    SQL> select job_name, start_date, next_run_date, repeat_interval from user_scheduler_jobs;
    
    JOB_NAME                       START_DATE                       NEXT_RUN_DATE                    REPEAT_INTERVAL
    ------------------------------ -------------------------------- -------------------------------- ----------------------------------------
    DEMO_HALL_TEST_SCHEDULE        03.01.13 13:09:06,873025 +01:00  03.01.13 14:09:06,600000 +01:00  freq=DAILY; BYHOUR=12,13,14;
    DEMO_HALL_TEST_SCHEDULE_2      03.01.13 13:09:09,384254 +01:00  03.01.13 14:00:09,400000 +01:00  freq=DAILY; BYHOUR=12,13,14; byminute=0;
    Excerpt from Oracle® Database PL/SQL Packages and Types Reference, DBMS_SCHEDULER
    >
    Start Dates and Repeat Intervals The Scheduler retrieves the date and time from the job or schedule start date and incorporates them as defaults into the repeat_interval. For example, if the specified frequency is yearly and there is no BYMONTH or BYMONTHDAY clause in the repeat interval, then the month and day that the job runs on are retrieved from the start date. Similarly, if frequency is monthly but there is no BYMONTHDAY clause in the repeat interval, then the day of the month that the job runs on is retrieved from the start date. If present, BYHOUR, BYMINUTE, and BYSECOND defaults are also retrieved from the start date, and used if those clauses are not specified.
  • 5. Re: Problem scheduling procedure to run at 3 different times
    hh*394375*91 Newbie
    Currently Being Moderated
    I am going to read this excerpt right now. So are you saying that since I did not state byminute=0; That it dirived it and therefore only ran once.
    I was expecting to see it had ran 3 times but instead only saw that it ran once.
  • 6. Re: Problem scheduling procedure to run at 3 different times
    hh*394375*91 Newbie
    Currently Being Moderated
    Thanks I now see it being logged at each hour.
    I needed to put the byminute=0; bysecond=0;',

    Howard

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points