NEXT_DAY in DBMS_OUTPUT.PUT_LINE

Question

This seems to be a silly question, however I will anyway try my luck here. Refer below code block, you'd see NEXT_DAY accepts both 1 and 'Sunday' as an argument in SELECT statement but in DBMS_OUTPUT.PUT_LINE only 'Sunday', and throws error for 1 - "ORA-01846: not a valid day of the week".

If I put 'Sunday', it works. Anything I'm missing here?

declare 

  wday varchar(10);

begin

  select to_char(next_day(sysdate,1),'Day') into wday from dual; -- <== working with 1
  dbms_output.put_line (wday);

  select to_char(next_day(sysdate,'Sunday'),'Day') into wday from dual; -- <== working with 'Sunday'
  dbms_output.put_line (wday);

  --dbms_output.put_line (to_char(next_day(sysdate,1),'Day')); -- <== not working with 1

  dbms_output.put_line (to_char(next_day(sysdate,'Sunday'),'Day')); -- <== working with 'Sunday'

end;

/

PS: My NLS configuration : 1 = Sunday

Try simple change `declare wday varchar2(20)`. It worked for me. Without that I received `ORA-06502: PL/SQL: numeric or value error: character string buffer too small`. My 'NLS_DATE_LANGUAGE' is set to 'POLISH', so for instance 'MONDAY' is 'PONIEDZIAŁEK'. Maybe this is the issue or any clue? — Ponder Stibbons, Aug 01 '15 at 21:21
@PonderStibbons: The questioner has a problem with one of the two statements that does not use `wday`, so I don't see how a suggestion to change `wday` could go any way to solving the questioner's problem. — Luke Woodward, Aug 01 '15 at 21:56
Oracle documentation suggests only the 2nd statement is supported. Support for the statement with a number as 2nd parameter may vary. Here is an excerpt. "NEXT_DAY returns the date of the first weekday named by char that is later than the date date. The argument char must be a day of the week in the date language of your session, either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version. " http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions093.htm — ramana_k, Aug 01 '15 at 22:26
This happens when the number gets interpreted as String, might have been converted by the DBMS. (uncomment the last line to reproduce error: http://sqlfiddle.com/#!4/9eecb7/5025/0) — maraca, Aug 02 '15 at 10:23
You're correct @maraca - so post it as the answer. PL/SQL is implicitly converting the number to a string whereas SQL isn't. Plus it's not supported... — Ben, Aug 02 '15 at 10:31

score 2 · Answer 1 · edited May 23 '17 at 11:53

This error occurs when the number gets interpreted as string. You can reproduce it with:

SELECT to_char(next_day(sysdate, '3'), 'Day') FROM DUAL;

Thanks @Ben for pointing out, that this happens because the number gets implicitly converted by PL/SQL and that it's not supported.

One workaround would be to write your own number_to_day function with the benefit of increased readability and the possibility to make it multilingual later.

NEXT_DAY in DBMS_OUTPUT.PUT_LINE

1 Answers1