Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

Complex Date Rules for Job Execution

BEGIN
  FOR i IN 1 .. p_iterations LOOP
    l_start_ts_str := ‘TO_TIMESTAMP(‘’’ ||
                       TO_CHAR(l_start_ts, ‘DD-MON-YYYY HH24:MI:SS’) ||
                       ‘’’,’’DD-MON-YYYY HH24:MI:SS’’)’;
    l_interval := REPLACE(LOWER(p_interval), ‘systimestamp’,
l_start_ts_str);
    EXECUTE IMMEDIATE ‘SELECT ‘ || l_interval || ‘ INTO :return FROM dual’
      INTO l_next_run_ts;
   
    DBMS_OUTPUT.put_line('Next Run Date: ' || l_next_run_ts);
    l_start_ts := l_next_run_ts;
  END LOOP;
END;
/

The best way to come to grips with defining repeat intervals and comparing the different methods available is looking at some examples.  Table 3.11 below lists a range of repeat intervals along with expressions than can be used to achieve them.  The date expressions can be used to schedule jobs using the dbms_job package, while the timestamp and calendar syntax expressions can be used for jobs scheduled using the dbms_scheduler package in Oracle10g.  Where possible, a literal and interval literal example is given along with an example of the output generated by the test procedures.

Download your Oracle scripts now: www.oracle-script.com The definitive Oracle Script collection for every Oracle professional DBA