how to get the REPEAT_INTERVAL in minutes format of Oracle SCHEDULE?

Question

I am using Oracle 11g Database and Repeat interval in Oracle DB is defined as string format like FREQ=MINUTELY; INTERVAL=10;. Is there any way to convert the same into +00 00:10:00.000000 this format ?

Answer 1

Use DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING and some conversion functions to convert a calendar string to a interval format.

declare
    v_next_run_date date;
    v_start_date date := date' 2000-01-01';
    v_interval interval day(2) to second;
begin
    dbms_scheduler.evaluate_calendar_string
    (
        calendar_string   => 'FREQ=MINUTELY; INTERVAL=10;',
        start_date        => v_start_date,
        return_date_after => v_start_date,
        next_run_date     => v_next_run_date
    );

    --Convert dates to timestamps, subtraction then converts to interval.
    v_interval := 
        cast(v_next_run_date as timestamp)
        -
        cast(v_start_date as timestamp);

    dbms_output.put_line(v_interval);       
end;
/

Output:

+00 00:10:00.000000