NEXT_DAY function works differently in SQL vs. PL/SQL?

Question

Why does this SQL query work:

SELECT NEXT_DAY(SYSDATE, 7)
FROM DUAL;

NEXT_DAY(SYSDATE,7)
-------------------
01-APR-17          
1 row selected.

...but this anonymous block of PL/SQL does not work?

DECLARE
  dteExpires DATE;
BEGIN
  dteExpires := NEXT_DAY(SYSDATE, 7);
END;

Error at line 1
ORA-01846: not a valid day of the week
ORA-06512: at line 4

I do not want to hard code the second parameter to the English name of a day ex. NEXT_DAY(SYSDATE, 'SATURDAY') or NEXT_DAY(SYSDATE, 'SUNDAY') etc.

For now, I will use the following work around, but I would really like to know why NEXT_DAY() behaves differently in PL/SQL than in SQL.

DECLARE
  dteExpires DATE;
BEGIN
  -- 2017-01-01 = SUNDAY
  dteExpires := NEXT_DAY(SYSDATE, TO_CHAR(TO_DATE('2017-01-01', 'YYYY-MM-DD'), 'DAY'));
END;

This is my DEV environment:

SELECT *
FROM v$version;

BANNER                                                                

-------------------------------------------------------------------------------- 
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production                 
CORE  12.1.0.2.0  Production                                            
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production               
NLSRTL Version 12.1.0.2.0 - Production                                

5 rows selected.

Answer 1

If you check the documentation for the SQL version of NEXT_DAY, you will find that passing in a number to represent a day of the week is undocumented. It works, for whatever reason, but if you rely on it working, you are taking the risk that in the future Oracle will change the implementation to conform to the stated specification of this function. (The risk is small, since Oracle is not crazy.)

https://docs.oracle.com/database/121/SQLRF/functions118.htm#SQLRF00672

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

I guess this is an indirect answer. I believe you are asking the wrong question. The question shouldn't be "why doesn't this work in PL/SQL"; rather, given the documentation, the question should be "why does it work in Oracle SQL". Only Oracle can answer that.

You may find this discussion at OTN useful: https://community.oracle.com/thread/4023654

And maybe this too: https://community.oracle.com/ideas/17257?tab=comment