Why does NEXT_DAY work differently in PL/SQL than in SQL? ORA-01846: not a valid day of the week

Question:

This SQL query works:

SELECT NEXT_DAY (sysdate, 7) "NEXT DAY" FROM DUAL;
  
NEXT DAY
-------------------
2021-08-15 00:31:12

But in PL/SQL, the same function doesn't work:

declare
    nextday date := NEXT_DAY (sysdate, 7);
begin null;
end;
/

ORA-01846: not a valid day of the week

Wouldn't want to hardcode the days of the week in the second parameter in English, like this:

NEXT_DAY(SYSDATE, 'SATURDAY') 
--или 
NEXT_DAY(SYSDATE, 'SUNDAY')

So far I'm using the following solution:

dteExpires DATE := NEXT_DAY (SYSDATE, TO_CHAR(TO_DATE('2021-08-15', 'YYYY-MM-DD'), 'DAY'));

I would like to know why NEXT_DAY() behaves differently in PL/SQL than in SQL?


A loose translation of the question NEXT_DAY function works differently in SQL vs. PL/SQL? by @wweicker

Answer:

If you look at the documentation for the SQL function NEXT_DAY , you'll notice that passing a number to represent the day of the week is not documented. For some reason it works, but if you rely on it, then there is a risk that in the future Oracle will change the implementation to comply with the specification of this function (the risk is small, but still).

The PL/SQL implementation only works with the documented specification – it does not accept a numeric parameter to represent the day of the week.

Given the documentation, the question is better put like this: Why does this work in SQL? .
Only Oracle developers can answer this question.

Perhaps this discussion on OTN will be helpful, or this too .


Free translation of answer from @mathguy

Scroll to Top