Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

Processing

Post Details

Added on Sep 14 2021
5 comments
374 views