Skip to Main Content

dbms_scheduler.create_calendar_string() returns different value in oracle 18c and 11g.

User_TK218Sep 14 2021

I used the same function(dbms_scheduler.create_calendar_string) with the same inputs in both Oracle 11g and 18c.
dbms_scheduler.create_calendar_string(frequency => rprJob.nFrequency,
INTERVAL => rprJob.nInterval,
bysecond => rprJob.aBySecond,
byminute => rprJob.aByMinute,
byhour => rprJob.aByHour,
byday_days => rprJob.aByDay_days,
byday_occurrence => rprJob.aByDay_occurrence,
bymonthday => rprJob.aByMonthday,
byyearday => rprJob.aByYearday,
byweekno => rprJob.aByWeekno,
bymonth => rprJob.aByMonth,
calendar_string => rprJob.sRepeatInterval);

With the inputs I gave in oracle 11g the calendar_string retuned is
'FREQ=WEEKLY;BYSECOND=15;BYMINUTE=30;BYHOUR=12;BYDAY=SUN';
but the returned calendar_string in oracle 18c is
'FREQ=WEEKLY;BYSECOND=;BYMINUTE=;BYHOUR=;BYDAY=SUN'

So In the bysecond, byminute, byhour values I gave inputs as bylist with a single entry in the list values 15,30,12 respectively.
In oracle 11g it's working fine the scheduler job is getting created with repeat_interval but in oracle 18c with calendar string returned the job creation is failing with the error message below.
ORA-27414: Invalid BYSECONS clause value
ORA-06512: at "SYS.DBMS_ISCHED", line 1150
ORA-06512: at "SYS.DBMS_SCHEDULER", line 1604
ORA-06512: at line 2
27414. 00000 - "Invalid %s clause value"
*Cause: The BY clause value was not recognized or was out-of-range for the
frequency specified.
*Action: Specify a valid BY clause value.

I couldn't find documentation for this dbms_scheduler.create_calendar_string function. Does anyone know something changed here between version 11g and 18c or am I doing something wrong above?

Comments
Post Details
Added on Sep 14 2021
5 comments
32 views