Does the NEXT_DAY function work differently in SQL and 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 does this anonymous PL / SQL block 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 for the English name of the day ex. NEXT_DAY(SYSDATE, 'SATURDAY') or NEXT_DAY(SYSDATE, 'SUNDAY') etc.

Currently I will use the following work, 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 a number representing the day of the week is undocumented. It works for any reason, but if you rely on its work, you risk that in the future Oracle will change the implementation to meet the declared specification of this function. (The risk is small because 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 numerical input to represent the day of the week.

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

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

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

Answer 2

I think the only way to make the NEXT_DAY function independent of the current NLS_DATE_LANGUAGE session is to NLS_DATE_LANGUAGE ISO week, where the first day of the week is always Monday:

 SELECT NEXT_DAY(SYSDATE, TO_CHAR(TRUNC(SYSDATE, 'IW')+5, 'Day')) from dual; 

TRUNC(SYSDATE, 'IW')+5 means "Next Saturday"