3 Replies Latest reply: May 9, 2013 1:32 AM by spajdy RSS

    Start Date

    934896
      BEGIN
      dbms_scheduler.create_job(job_name => 'APPLICATION_CHAIN_JOB',
      job_type => 'CHAIN',
      job_action => 'APPLICATION_CHAIN',
      start_date => '19-APR-13 1.00.00 PM',
      repeat_interval => 'FREQ = MINUTELY; INTERVAL=30; BYHOUR = 6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,0',
      enabled => TRUE,
      comments => 'Refresh Self Serve Dimensions');
      END;
      /


      I have the above job, I would prefer not to Hard Code a start date/time. Is there any way to set the start_date in the code above to start at 6am the next day after it’s created…ie 6am SYSDATE+1???? .

      For example I created the job now i.e 04/20/2013 at 5:30PM. I want the job to start the next morning(04/21/2013) at 6 AM and run every hour with an interval of 30 minutes. I think repeat interval is fine, just want to make a change to the start_date, please help.

      Thanks
        • 1. Re: Start Date
          spajdy
          BEGIN
          dbms_scheduler.create_job(job_name => 'APPLICATION_CHAIN_JOB',
          job_type => 'CHAIN',
          job_action => 'APPLICATION_CHAIN',
          start_date => trunc(sysdate+1),
          repeat_interval => 'FREQ = MINUTELY; INTERVAL=30; BYHOUR = 6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,0',
          enabled => TRUE,
          comments => 'Refresh Self Serve Dimensions');
          END;
          /
          For start_date you can use any expression that evaluate to date or timestamp or timestamp with timezone data type.
          • 2. Re: Start Date
            934896
            Hi here is another one.

            I have the start date as sysdate. That means it should fire once it is created.
            And then I want it to fire everynight at midnight( repeat_interval => 'FREQ = Daily; INTERVAL=1; BYHOUR = 0',), But this fires at 12:30 Am Is there something I am missing here?

            Thanks

            BEGIN

                 dbms_scheduler.create_job(job_name => 'ETL_TEST_JOB',
            job_type => 'CHAIN',
            job_action => 'ETL_TEST_CHAIN',
            start_date => SYSDATE,
            repeat_interval => 'FREQ = Daily; INTERVAL=1; BYHOUR = 0',
            enabled => TRUE,
            comments => 'Run tests for source and target data.');
            END;
            /
            • 3. Re: Start Date
              spajdy
              When you don't specify byminute, bysecond, then this attributes are derived from start_date.
              Try this and see results:
              DECLARE
                v_date TIMESTAMP WITH TIME ZONE := SYSDATE;
              BEGIN
                FOR i IN 1..10
                LOOP  
                  dbms_scheduler.evaluate_calendar_string(calendar_string =>  'FREQ = Daily; INTERVAL=1; BYHOUR = 0',start_date => v_date,return_date_after => v_date,next_run_date => v_date);
                  dbms_output.put_line(to_char(v_date,'dd.mm.yyyy hh24:mi:ss tzr'));
                END LOOP;
              END;
              /
              to set to midnight
              DECLARE
                v_date TIMESTAMP WITH TIME ZONE := SYSDATE;
              BEGIN
                FOR i IN 1..10
                LOOP  
                  dbms_scheduler.evaluate_calendar_string(calendar_string =>  'FREQ = Daily; INTERVAL=1; BYHOUR = 0;BYMINUTE=0;BYSECOND=0',start_date => v_date,return_date_after => v_date,next_run_date => v_date);
                  dbms_output.put_line(to_char(v_date,'dd.mm.yyyy hh24:mi:ss tzr'));
                END LOOP;
              END;
              /
              and with time zone
              DECLARE
                v_date TIMESTAMP WITH TIME ZONE := to_timestamp_tz(to_char(SYSDATE,'dd.mm.yyyy hh24:mi:ss')||' Europe/Prague','dd.mm.yyyy hh24:mi:ss tzr');
              BEGIN
                FOR i IN 1..10
                LOOP  
                  dbms_scheduler.evaluate_calendar_string(calendar_string =>  'FREQ = Daily; INTERVAL=1; BYHOUR = 0;BYMINUTE=0;BYSECOND=0',start_date => v_date,return_date_after => v_date,next_run_date => v_date);
                  dbms_output.put_line(to_char(v_date,'dd.mm.yyyy hh24:mi:ss tzr'));
                END LOOP;
              END;
              /