2 Replies Latest reply: Aug 27, 2013 5:08 AM by botang(唐波) RSS

    How to evaluate the Complex Schedules?

    1cde31cd-113c-41fc-9815-80b4d889359f

      Hi, everyone.

       

      I got the error:

       

      SQL> conn / as sysdba

      Connected.

      BEGIN

      sys.dbms_scheduler.create_schedule(

      repeat_interval => 'FREQ=YEARLY;BYDATE=20140101;BYHOUR=14;BYMINUTE=10;BYSECOND=0;intersect=hr.schedule1',

      start_date => systimestamp at time zone '+8:00',

      comments => 'schedule_comphc',

      schedule_name => '"HR"."SCHEDULE_COMPHC"');

        7  END;

        8  /

      BEGIN

      *

      ERROR at line 1:

      ORA-27421: usage of BYSECOND not supported in a calendar definition

      ORA-06512: at "SYS.DBMS_ISCHED", line 679

      ORA-06512: at "SYS.DBMS_SCHEDULER", line 1130

      ORA-06512: at line 2

       

       

       

       

       

       

      The process is:

      I have create a schedule: hr.schedule1

       

         BEGIN
          sys.dbms_scheduler.create_schedule(
          repeat_interval => 'FREQ=YEARLY;BYYEARDAY=1,
      32,-306;BYHOUR=14;BYMINUTE=10;BYSECOND=0',
          start_date => systimestamp at time zone '+8:00',
          comments => 'schedule1',
          schedule_name => '"HR"."SCHEDULE1"');
        END;

      Then I want to create a complex schedule:


      BEGIN

      sys.dbms_scheduler.create_schedule(

      repeat_interval => 'FREQ=YEARLY;BYDATE=20140101;BYHOUR=14;BYMINUTE=10;BYSECOND=0;intersect=hr.schedule1',

      start_date => systimestamp at time zone '+8:00',

      comments => 'schedule_comphc',

      schedule_name => '"HR"."SCHEDULE_COMPHC"');

      END;


      WHY? Please give me a way to do it.

        • 1. Re: How to evaluate the Complex Schedules?
          Antonio Navarro

          ORA-27421:

          usage of string not supported in a calendar definition
          Cause:The calendar definition contained a clause that is specific for a repeat interval
          Action:Specify a valid calendar definition.

           

          Please, probe to omit bysecond clause.

           

          HTH

          Antonio NAVARRO

          • 2. Re: How to evaluate the Complex Schedules?
            botang(唐波)

            It is not the truth to omit bysecond clause.

             

            The schedule1 should be defined as following:

             

            begin

              sys. dbms_scheduler.drop_schedule(schedule_name => 'HR.SCHEDULE1');

            end;

             

             

             

             

             

             

            BEGIN

                sys.dbms_scheduler.create_schedule(

                repeat_interval => 'FREQ=YEARLY;BYYEARDAY=1,32,-306',

                start_date => systimestamp at time zone '+8:00',

                comments => 'schedule1',

                schedule_name => '"HR"."SCHEDULE1"');

            END;

             

             

            BEGIN

               sys.dbms_scheduler.create_schedule(

               repeat_interval => 'FREQ=YEARLY;BYDATE=20140101;BYHOUR=14;BYMINUTE=10;BYSECOND=0;intersect=hr.schedule1',

               start_date => systimestamp at time zone '+8:00',

               comments => 'schedule_comphc',

               schedule_name => '"HR"."SCHEDULE_COMPHC"');

            END;

             

             

             

             

             

            declare

               v_1  timestamp;

            begin

              for i in 1..10

               loop

                DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (

                calendar_string=>'hr.schedule1'   ,

               start_date => sysdate,

               return_date_after  => v_1,

               next_run_date    => v_1 );

              dbms_output.put_line(to_char(v_1,'YYYY-MM-DD:HH24:MI:SS'));

            end loop;

            end;

            THE OUTPUT:

             

            2014-01-01:17:56:32

            2014-02-01:17:56:32

            2014-03-01:17:56:32

            2015-01-01:17:56:32

            2015-02-01:17:56:32

            2015-03-01:17:56:32

            2016-01-01:17:56:32

            2016-02-01:17:56:32

            2016-03-01:17:56:32

            2017-01-01:17:56:32

             

             

             

             

             

             

             

             

             

            declare

               v_1  timestamp;

            begin

             

                DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (

                calendar_string=>'hr.schedule_comphc'   ,

               start_date => sysdate,

               return_date_after  => v_1,

               next_run_date    => v_1 );

              dbms_output.put_line(to_char(v_1,'YYYY-MM-DD:HH24:MI:SS'));

             

            end;

            THE OUTPUT:

            2014-01-01:14:10:00