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
    Likes Received:
    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
     

    Attached Files:

    Wask8N, Sep 21, 2011
    #1
    1. Advertisements

  2. Wask8N

    Wask8N

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

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 (here). After that, you can post your question and our members will help you out.