Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

DBMS_SCHEDULER repeat_interval question ...

garywickeDec 1 2016 — edited Dec 1 2016

Environment

Oracle 11.2.0.4 EE on Solaris

I have a DBMS_SCHEDULER job created to run a small shell script that I want to run every five (5) minutes.

I initially set the REPEAT_INTERVAL up as:

repeat_interval => 'FREQ=DAILY; BYMINUTE=00,05,10,15,20,25,30,35,40,45,50,55'

However as I was monitoring the executions from DBA_SCHEDULER_JOBS I noticed that after it executed the job at the 55th minute the NEXT_RUN_DATE would show the next day at the 00th minute of the same hour instead of the same day at the 00th minute of the next hour.

Well that was confusing enough so here's an example:

If the LAST_START_DATE was 01-Dec-2016 13:55:11 the NEXT_RUN_DATE would show 02-Dec-2016 13:00:11 instead of 01-Dec-2016 14:00:11.

I observed this several times.

I 'fixed' the issue by adding the BYHOUR parameter and specifying every hour of the day 0-23 but I didn't think that should have been necessary.

I've researched the REPEAT_INTERVAL format examples and documentation but have not come across this particular situation.

Is this working as designed?  If so, why would the repeat_interval not assume every hour of the day if the BYHOUR parameter was missing?

Any suggestions and better examples are most welcome.

-gary

This post has been answered by Pavan Kumar on Dec 1 2016
Jump to Answer

Comments

user597585

anyone?  I'm struggling with the same issue...  Not trying to be lazy... 

James_D

Is your model using a java.util.Date? If so the simplest thing would be to update the model classes so they use the new java.time API. You should consider the old Date and Calendar classes "nearly deprecated", imho.

If that's not possible, you'll need to do some wiring. A fairly ugly hack would be to define a StringProperty containing some string that uniquely determined your date (e.g., I suppose you could use a String representation of the number of milliseconds since epoch), then bi-directionally bind both your ObjectProperty<Date> and your DatePicker's value property to that String property, using appropriate StringConverters in each case.

Or, just use a couple of listeners and update each property when the other changes. Here's an example if you need this approach:

import java.text.DateFormat;

import java.time.Instant;

import java.time.LocalDate;

import java.time.LocalDateTime;

import java.time.ZoneId;

import java.time.ZonedDateTime;

import java.time.temporal.ChronoField;

import java.util.Date;

import javafx.application.Application;

import javafx.beans.binding.Bindings;

import javafx.beans.property.ObjectProperty;

import javafx.beans.property.SimpleObjectProperty;

import javafx.geometry.Insets;

import javafx.geometry.Pos;

import javafx.stage.Stage;

import javafx.scene.Scene;

import javafx.scene.control.Button;

import javafx.scene.control.DatePicker;

import javafx.scene.control.Label;

import javafx.scene.layout.BorderPane;

import javafx.scene.layout.HBox;

public class Main extends Application {

  @Override

  public void start(Stage primaryStage) {

  BorderPane root = new BorderPane();

  ObjectProperty<Date> date = new SimpleObjectProperty<>(new Date());

  DatePicker datePicker = new DatePicker(LocalDate.now());

  Button addDayButton = new Button("Add day");

  addDayButton.setOnAction(event -> date.set(new Date(date.get().getTime() + 24 * 60 * 60 * 1000)));

  Button subtractDayButton = new Button("Subtract day");

  subtractDayButton.setOnAction(event -> date.set(new Date(date.get().getTime() - 24 * 60 * 60 * 1000)));

  Label label = new Label();

  DateFormat format = DateFormat.getDateInstance();

  label.textProperty().bind(Bindings.createStringBinding(() -> format.format(date.get()), date));

  date.addListener((obs, oldDate, newDate) -> { 

    

  Instant instant = newDate.toInstant();

  ZonedDateTime zonedDateTime = ZonedDateTime.ofInstant(instant, ZoneId.systemDefault());

  LocalDate localDate = zonedDateTime.toLocalDate();

     if ( ! datePicker.getValue().equals(localDate)) { 

        datePicker.setValue(localDate); 

     } 

  }); 

  datePicker.valueProperty().addListener((obs, oldDate, newDate) -> { 

  ZonedDateTime zonedDateTime = newDate.atStartOfDay(ZoneId.systemDefault());

  Instant instant = Instant.from(zonedDateTime);

  Date dateToSet = Date.from(instant);

  if (! dateToSet.equals(date.get())) {

  date.set(dateToSet);

  }

  }); 

  root.setCenter(datePicker);

  HBox controls = new HBox(5, subtractDayButton, addDayButton, label);

  controls.setPadding(new Insets(10));

  controls.setAlignment(Pos.CENTER);

  root.setBottom(controls);

  Scene scene = new Scene(root,400,400);

  primaryStage.setScene(scene);

  primaryStage.show();

  }

  public static void main(String[] args) {

  launch(args);

  }

}

The conversions between Date and LocalDate are a bit tricky as they're very different beasts. Date includes a time (of day) and (sort of, implicitly) time zone information.

user597585

Thank you.  I'll give that a try later today.  I just wanted to make sure I wasn't missing something obvious.

user597585

I was lazy and changed it to LocalDate.... works.  I think I have the option to switch over my POJOs.  Thanks again for the help.

James_D

Actually much the better approach if you can do it. I updated the code to something that actually works, in case anyone else was looking at this.

1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 29 2016
Added on Dec 1 2016
4 comments
6,014 views