Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Nelson.

Asked: December 09, 2014 - 8:24 pm UTC

Last updated: December 10, 2014 - 4:07 pm UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

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)?

and Tom said...

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...)

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

to OP

A reader, December 10, 2014 - 6:48 pm UTC

sorry for this silly question....

SYSDATE + 1/96 will be after 4 days, how it will mapped to FREQUENCY=MINUTELY, INTERVAL=15?


John Stegeman, December 11, 2014 - 6:37 am UTC

how does sysdate + 1/96 equate to 4 days? 1/96 is 1/96 of a day (15 minutes), not 1/96 of a year

thanks John

A reader, December 11, 2014 - 9:50 am UTC


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library