Forum Stats

  • 3,769,228 Users
  • 2,252,932 Discussions
  • 7,874,951 Comments

Discussions

Categories

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

User_TK218
User_TK218 Member Posts: 14 Green Ribbon
in SQL & PL/SQL

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?

Tagged:
· Share on TwitterShare on Facebook

Answers

  • mathguy
    mathguy Member Posts: 10,155 Blue Diamond
    edited Sep 14, 2021 6:01AM

    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.

    User_TK218
    · Share on TwitterShare on Facebook
  • User_TK218
    User_TK218 Member Posts: 14 Green Ribbon

    @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.

    · Share on TwitterShare on Facebook
  • GregV
    GregV Member Posts: 3,069 Gold Crown

    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.

    · Share on TwitterShare on Facebook
  • mathguy
    mathguy Member Posts: 10,155 Blue Diamond

    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.

    · Share on TwitterShare on Facebook
  • User_TK218
    User_TK218 Member Posts: 14 Green Ribbon

    @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.

    · Share on TwitterShare on Facebook