Oracle Job not starting according to Start Date paramter

Question

I have an Oracle For loop which creates n jobs. For each job created the start date is an interval of 10 seconds from previous job. But for some reason each job launches within 1 second of each other. Are future jobs not possible in Oracle ?

    LTIMESTAMP := SYSTIMESTAMP;

    FOR REC IN (SELECT *
                  FROM ORDERS
                 WHERE PROCESS_FLAG = CST_IS_ELIGIBLE_FOR_PROCESSING
                 ORDER BY ORDER_DATE ASC)
    LOOP

        LJOBNAME := CST_JOB_NAME_PREFIX || TO_CHAR(REC.ORDER_ID);

        DBMS_SCHEDULER.CREATE_PROGRAM(PROGRAM_NAME        => CST_PROGRAM_NAME,
                                      PROGRAM_ACTION      => 'PKG_BATCH_MAIN.SP_START_JOB',
                                      PROGRAM_TYPE        => 'STORED_PROCEDURE',
                                      NUMBER_OF_ARGUMENTS => 2,
                                      ENABLED             => FALSE);

        DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(PROGRAM_NAME      => CST_PROGRAM_NAME,
                                               ARGUMENT_POSITION => 1,
                                               ARGUMENT_TYPE     => 'NUMBER');

        DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(PROGRAM_NAME      => CST_PROGRAM_NAME,
                                               ARGUMENT_POSITION => 2,
                                               ARGUMENT_TYPE     => 'NUMBER');

        DBMS_SCHEDULER.ENABLE(CST_PROGRAM_NAME);

        DBMS_SCHEDULER.CREATE_JOB(JOB_NAME        => LJOBNAME,
                                  PROGRAM_NAME    => CST_PROGRAM_NAME,
                                  START_DATE      => LTIMESTAMP,
                                  REPEAT_INTERVAL => 'FREQ=SECONDLY; BYSECOND=1',
                                  END_DATE        => NULL,
                                  AUTO_DROP       => TRUE,
                                  ENABLED         => FALSE,
                                  COMMENTS        => 'Job launched for each ORDER detail id');

            DBMS_SCHEDULER.SET_ATTRIBUTE(LJOBNAME,
                                         'MAX_RUNS',
                                         1);

            DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(LJOBNAME,
                                                  1,
                                                  TO_CHAR(REC.ORDER_ID));
            DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(LJOBNAME,
                                                  2,
                                                  TO_CHAR(REC.ORDER_DETAILS_ID));

            DBMS_SCHEDULER.ENABLE(LJOBNAME);

            LTIMESTAMP := LTIMESTAMP + INTERVAL '10' SECOND;

    END LOOP;

Can you give us `LOG_DATE, REQ_START_DATE, ACTUAL_START_DATE, RUN_DURATION` from `DBA_SCHEDULER_JOB_RUN_DETAILS` for some of these jobs? — atokpas, May 11 '17 at 06:08

score 0 · Answer 1 · answered May 11 '17 at 08:31

I'm guessing LTIMESTAMP is timestamp datatype insted of TIMESTAMP WITH TIME ZONE. During autoconversion you lose the accuracy of the timestamp.

Scheduler uses timezone from SELECT * FROM dba_scheduler_global_attribute WHERE attribute_name = 'DEFAULT_TIMEZONE';

In session you are using timezone from SELECT sessiontimezone FROM DUAL;

declare
 with_timestamp  TIMESTAMP WITH TIME ZONE := systimestamp;
 no_timestamp    timestamp                  := with_timestamp;
 auto_conversion TIMESTAMP WITH TIME ZONE := no_timestamp;
begin 
 dbms_output.put_line(to_char(with_timestamp,'YYYY-MM-DD hh24:mi:ss TZR')); 
 dbms_output.put_line(to_char(no_timestamp,'YYYY-MM-DD hh24:mi:ss TZR'));
 dbms_output.put_line(to_char(auto_conversion,'YYYY-MM-DD hh24:mi:ss TZR'));

end;

Results from my server are

2017-05-11 09:15:44 +01:00
2017-05-11 09:15:44 +00:00
2017-05-11 09:15:44 +02:00

In my case all joby will start in an hour. Your start_date is earlier than the current date for the scheduler.

Tried changing LTIMESTAMP datatype from timestamp to TIMESTAMP WITH TIME ZONE, but still has the same issue — Alex David, May 11 '17 at 08:54

Oracle Job not starting according to Start Date paramter

1 Answers1