Convert repeat_interval from DBMS scheduler to a usable datatype

Discussion in '.NET' started by Wask8N, Sep 21, 2011.

  1. Wask8N

    Wask8N

    Joined:
    Sep 21, 2011
    Messages:
    2
    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

    Attached Files:

    Wask8N, Sep 21, 2011
    #1
    1. Advertising

  2. Wask8N

    Wask8N

    Joined:
    Sep 21, 2011
    Messages:
    2
    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;
    /
    Wask8N, Oct 5, 2011
    #2
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Codemonkey
    Replies:
    8
    Views:
    9,525
  2. P.DELMAS
    Replies:
    3
    Views:
    3,238
    P.DELMAS
    Oct 1, 2003
  3. Steven Woody

    Recommended Open Source DBMS for J2EE

    Steven Woody, Oct 21, 2003, in forum: Java
    Replies:
    10
    Views:
    996
    Drew Volpe
    Oct 23, 2003
  4. Maurice LING

    Seeking for SQL99 pure Java DBMS

    Maurice LING, Jul 1, 2004, in forum: Java
    Replies:
    1
    Views:
    434
    Roedy Green
    Jul 1, 2004
  5. Jeff
    Replies:
    2
    Views:
    1,145
    Steve C. Orr [MCSD, MVP, CSM, ASP Insider]
    Apr 16, 2007
Loading...

Share This Page