Got a few Jobs, in several databases in the form:
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'begin JOB1; end;'
,next_date => to_date('09/12/2014 20:18:14','dd/mm/yyyy hh24:mi:ss')
,interval => 'SYSDATE + 1/96'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/
I wrote a program to set them to broken and create a scheduler job.
I know I can use the same sql interval (ie.: 'SYSDATE + 1/96'), but I want to know if there is a way to program it so that the interval is set using the calendar option.
So the following conversion will take place:
sql interval ==> calendar interval
SYSDATE + 1/96 ==> FREQUENCY=MINUTELY, INTERVAL=15
TRUNC(SYSDATE + 1) + (3/24) ==> FREQUENCY=DAILY,BYHOUR=3,BYMINUTE=0
trunc(SYSDATE+1/24,'HH') ==> FREQUENCY=HOURLY,INTERVAL=1
Thanks
Update.
Here is the PL/SQL I wrote
DECLARE
vJobNumber user_jobs.job%type;
vInterval user_jobs.interval%type;
vNextDate TIMESTAMP;
vSchedulerJobCount PLS_INTEGER;
BEGIN
vSkip := FALSE;
BEGIN
SELECT job, CAST (next_date AS TIMESTAMP WITH TIME ZONE), interval
INTO vJobNumber, vNextDate, vInterval
FROM user_jobs
WHERE UPPER (what) LIKE '%JOBX%';
DBMS_JOB.BROKEN (vJobNumber, TRUE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Could not find an active dbms_job called JOBX');
vSkip := TRUE;
END;
--
IF NOT vSkip THEN
SELECT COUNT (*)
INTO vSchedulerJobCount
FROM user_scheduler_jobs
WHERE job_name = 'JOBX_JOB';
IF vSchedulerJobCount = 0 THEN
DBMS_SCHEDULER.create_job (job_name => 'JOBX_JOB'
,job_type => 'STORED_PROCEDURE'
,job_action => 'JOBX'
,start_date => vNextDate
,repeat_interval => vInterval
,enabled => TRUE);
DBMS_OUTPUT.put_line ('Scheduler Job JOBX_JOB was created');
ELSE
DBMS_OUTPUT.put_line ('Scheduler Job JOBX_JOB already exist!');
END IF;
END IF;
END;
/
So my question basically is, is there a way to program in PL/SQL a conversion from sql interval (SYSDATE + 1/96) to calendar interval (FREQUENCY=MINUTELY, INTERVAL=15)?
oh, i see now...
Yes there is - but it would be a large task :) You would have to write a parsing routine that would be able to put sysdate+1/96 into some structure and then write a lot of code to try to figure out what sysdate+1/96 means and then construct an calendar that maps to that.
So, yes there is - but it is probably not practical. There is nothing existing that I'm personally aware of (but I'll publish this to see if anyone has something they've done and would be willing to share) and I wouldn't want to sit down and write it myself (pretty big undertaking for a one time conversion...)