Convert Interval value to calender syntax while migrating DBMS_JOB to DBMS_SCHEDULER





1.00/5 (2 votes)
Useful piece of code which will convert the DBMS_JOB interval value to calendar syntax.
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;