在Oracle Scheduler中使用excluse选项进行每周计划

We have configured our scheduler to run on weekly basis on evert monday but as a requirement, we would like to skip this scheduler for specific date of months. like i dont want to run this for 15May2015, 16June2015 etc.

我们已将调度程序配置为每周在evert星期一运行,但作为一项要求,我们希望跳过此调度程序以查看特定日期的月份。就像我不想在2015年5月16日,2015年6月16日等运行

BEGIN
DBMS_SCHEDULER.set_attribute( name => '"SCHEMA"."NAME"', attribute => 'repeat_interval', value => 'FREQ=WEEKLY;BYDAY=MON;BYHOUR=13;BYMINUTE=0;BYSECOND=0');
END; 
/

I have checked with scheduler argument and can see using EXCLUSE argument we can achieve that but every time I try its giving me compilation error.

我已经检查过scheduler参数,并且可以看到使用EXCLUSE参数我们可以实现,但每次我尝试它给我编译错误。

Could someone pls help.

有人可以帮忙吗?

1 个解决方案

#1

you have to define first another schedule for the excluded days. If there's no real repeating pattern between them, you have to define a schedule for each excluded day. Something like this:

您必须为排除的日期定义另一个计划。如果它们之间没有真正的重复模式,则必须为每个被排除的日期定义一个计划。像这样的东西:

BEGIN

    -- define a schedule for May 15th
    DBMS_SCHEDULER.CREATE_SCHEDULE (
        repeat_interval  => 'FREQ=YEARLY;BYDATE=0515',     
        comments         => 'Exclude May 15th',
        schedule_name    => '"May15th"'
    ); 

    -- define a schedule for June 16th
    DBMS_SCHEDULER.CREATE_SCHEDULE (
        repeat_interval  => 'FREQ=YEARLY;BYDATE=0616',     
        comments         => 'Exclude June 16th',
        schedule_name    => '"June16th"'
    );

    -- note the EXCLUDE in the value parameter
    DBMS_SCHEDULER.set_attribute( 
        name => '"SCHEMA"."NAME"', 
        attribute => 'repeat_interval', 
        value => 'FREQ=WEEKLY;BYDAY=MON;BYHOUR=13;BYMINUTE=0;BYSECOND=0;EXCLUDE=May15th,Jun16th;'
    );
END;