Convert Interval value to calender syntax while migrating DBMS_JOB to DBMS_SCHEDULER

Introduction

DBMS_SCHEDULER is a collection of functions and procedures intended to make job scheduling in Oracle more robust and easy to use.  

The most interesting part in the DBMS_SCHEDULER.create_job is the parameter called repeat_interval.

In Oracle10g and beyond, the calendar syntax is the preferred way to define a jobs repeat interval, although PL/SQL expression can still be used if they evaluate to a timestamp.

When you migrate your existing DBMS_JOB to DBMS_SCHEDULER job, you may use below code to convert PLSQL syntax of Interval to Calendar syntax.

Using the code

Add below function to your program. Test the function by giving a existing USER_JOBS.JOB value. 

FUNCTION CalculateInterval(p_job_id IN NUMBER) RETURN VARCHAR2
IS
  l_error_msg varchar2(1000);
  v_interval VARCHAR2(200);
  v_original_interval VARCHAR2(200);
  
  v_freq_month NUMBER;
  v_freq_day NUMBER;
  v_freq_min NUMBER;
  v_weekday_value VARCHAR2(10);
  v_day_value NUMBER;
  v_hour_value NUMBER;
  v_minute_value NUMBER;
BEGIN
  v_interval := '';
  
  v_freq_month :=0;
  v_freq_day :=0;
  v_freq_min :=0;
  v_weekday_value :=0;
  v_day_value :=0;
  v_hour_value :=0;
  v_minute_value :=0;
  
  SELECT  TRUNC(MONTHS_BETWEEN(next_DATE, LAST_DATE)) AS FREQ_MONTH
         ,(TRUNC(next_DATE) - TRUNC(LAST_DATE)) AS FREQ_DAY
         , ROUND((next_DATE - LAST_DATE)* 24 * 60) AS FREQ_MINUTE
         , TO_CHAR(next_DATE, 'Dy') AS WEEKDAY_VALUE
         , TO_NUMBER(TO_CHAR(LAST_DATE, 'DD')) as DAY_VALUE
         , TO_NUMBER(TO_CHAR(LAST_DATE, 'HH24')) as HOUR_VALUE
         , TO_NUMBER(TO_CHAR(LAST_DATE, 'MI')) as MINUTE_VALUE
         , INTERVAL
  INTO   v_freq_month, v_freq_day, v_freq_min, v_weekday_value, v_day_value, v_hour_value, v_minute_value, v_original_interval
  FROM   USER_JOBS  
  WHERE job=p_job_id;
  
  -- Frequency = MONTHLY
  IF v_freq_month = 1 THEN
     v_interval := v_interval || 'FREQ=MONTHLY;' || 'BYMONTHDAY='|| v_day_value || ';' || _
       'BYHOUR='|| v_hour_value || ';' || 'BYMINUTE=' || v_minute_value || ';' ;
  ELSE
    -- Frequency = WEEKLY
    -- TRY - 'FREQ=DAILY; BYDAY=SUN; BYHOUR=18;'
    IF v_freq_day =7 THEN
      v_interval := v_interval || 'FREQ=WEEKLY;' || 'BYDAY='|| v_weekday_value || ';' _
        || 'BYHOUR='|| v_hour_value || ';' || 'BYMINUTE=' || v_minute_value || ';' ;
    END IF;
    
    -- Frequency = DAILY
    IF v_freq_day =1 THEN
      v_interval := v_interval || 'FREQ=DAILY;' || 'BYHOUR='|| v_hour_value || ';' || 'BYMINUTE=' || v_minute_value || ';' ;
    ELSE
      -- Frequency = HOURLY
      IF v_freq_min = 60 THEN
        v_interval := v_interval || 'FREQ=HOURLY;' || 'INTERVAL=1;' ;
      END IF;
      
      -- Frequency = MINUTELY
      IF v_freq_min < 60 THEN
        v_interval := v_interval || 'FREQ=MINUTELY;' || 'INTERVAL=' || v_freq_min || ';' ;
      END IF;  
    END IF;
      
    
  END IF;
  
  IF v_interval = '' OR v_interval IS NULL THEN
    v_interval := v_original_interval;
  END IF;
  
    
  RETURN v_interval;
  
EXCEPTION
  WHEN OTHERS THEN
    l_error_msg := 'Problem in function CalculateInterval '|| SQLERRM;
    RAISE_APPLICATION_ERROR( -20001, l_error_msg );
END CalculateInterval;