Convert repeat_interval from DBMS scheduler to a usable datatype


Joined
Sep 21, 2011
Messages
2
Reaction score
0
Making a custom job scheduler that pulls job from our oracle DB and I would like to be able to convert a repeat_interval format into something I can use to test against job start times so the app knows when to run the next job.

So far the logic I have pulls the next_start_time field and adds the converted repeat_interval to next_start_time then I test against the current time.

ex:

next_start_time = 9/20/2011 11:50:00.000000 AM -05:00
repeat_interval = FREQ=DAILY;BYHOUR=13;BYMINUTE=30;

public void ConvertInterval()
{
split repeat_interval
insert into TimeSpan
return ConvertedToTimespan
}

next_start_time.add(ConvertedToTimespan)

If (next_start_time <= DateTime.Now)
{ run job }

I split by ; and extract each part then split again by = to extract the numbers. I have 2 different splits because there's a lot of case statements differenciating the Frequency values. Then add them into a time span, and just add that time span to the next_start_time. The thing is that logic does not work.

There's a lot of different match ups for interval values so it makes it a lot harder. I attached a picture of what values and match ups will be used. Does anyone have any other logic or ideas to throw at me, I'm super stumped? Thanks

DBMS Scheduler: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sched.htm
 

Attachments

  • repeatinterval.JPG
    repeatinterval.JPG
    29.5 KB · Views: 358
Ad

Advertisements

Joined
Sep 21, 2011
Messages
2
Reaction score
0
Might as well answer my own question since this post has 50 views or so.

Found out through a peer there is a function in oracle that converts repeat_intervals to dates.

CREATE OR REPLACE FUNCTION SUPPORT_SERVICES.CMN_DBMS_REPEAT_INTERVAL_TO_DT (
p_repeat_interval_str IN VARCHAR2 := NULL,
p_start_date IN TIMESTAMP WITH TIME ZONE := NULL,
p_return_date_after IN TIMESTAMP WITH TIME ZONE := NULL
)
RETURN TIMESTAMP WITH TIME ZONE
IS
dte TIMESTAMP(6) WITH TIME ZONE;

BEGIN
dte := NULL;
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(p_repeat_interval_str, p_start_date, p_return_date_after, dte);
RETURN dte;
END CMN_DBMS_REPEAT_INTERVAL_TO_DT;
/
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top