Skip to Main Content
Forums

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!

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

mathguy

According to Morgan's library, this function is undocumented. (Which begs the question - are you using this in a critical production environment, and if so, why?)
https://www.morganslibrary.org/reference/pkgs/dbms_scheduler.html#sched15
In any case - it appears that rprJob is a package (either user-defined or perhaps another Oracle-provided but undocumented) which, among other things, defines the various constants used in your calls. It seems pretty clear that in one environment (the older one) the constants are in fact defined (15, 30 12) while in the other environment they are not. In other words: don't look at the function; look at the values passed to it. Where they come from, and how they are different in the two databases.

1 person found this helpful
User_TK218

@mathguy Thanks for the quick response. I was using this function in oracle 11g now I'm trying to upgrade my version to 18c which made me run into this issue. Also, I've looked at the values that are passed to the function in both versions and they are the same. In Oracle 18c I tried checking their values before and after the function is called and at both the time, the value is as expected(15,30,12). So I don't think there is a value change in the values passed to the function.

GregV

Hi,
As mathguy said, this function is undocumented, so there can be changes from one version to another without notice. It isn't recommended to use undocumented features especially for this reason. Building the repeat_interval string isn't that complicated, so you can very well do it on your own.

mathguy

When troubleshooting, the first task is to localize the issue. If you do want to continue to investigate (at the very least, out of curiosity) - the next thing you might try is to pass hard-coded numeric literals (such as 12, 15 etc.) Does the function, then, work as expected?
If it doesn't, you will know something changed about the function itself, and the problem has nothing to do with the package constants. Conversely, if in that case the function works as expected, you will know that the issue has to do with how the values are passed, and not the function itself. Either way you will have a smaller problem to tackle.

User_TK218

@gregv @mathguy Thanks for your responses. I did try and hard code and pass the values to the function in sqldeveloper still getting a result that is different from the older version. So I've decided to create a string my own or modify the string returned accordingly.

1 - 5

Post Details

Added on Sep 14 2021
#11g, #plsql
5 comments
387 views
2 mentions