SQL & PL/SQL

So, for example, if I ALTER SESSION to set the NLS_DATE_LANGUAGE to 'German' or 'French', I will get an error message if I use NEXT_DAY(..., 'Tuesday'). 

And the OBVIOUS question is why you are changing your language to german if you want to use english for your functions?

I can think of two such hacks, but I don't like them. Question: Is there a better way? Of course, it would be best if Oracle would allow a third argument for nlsparam , as they do for other functions...

Why not use a third 'hack' and write  your own function to wrap Oracle's. Then you can pass that third argument and have your function:

1.  alter the session to the needed setting

2. call Oracle's function

3. alter the sessin setting back

Or take the 'slow boat to china' and submit an enhancement request to Oracle!

I don't want to use English for my function, I want the query I write to run without changes (and without requiring the user to change their NLS_DATE_LANGUAGE to English before running the query) in a multi-lingual organization. I change my session's setting only for testing purposes. Imagine the same query must run across my organization, and we have offices in New Zealand, France and Germany. How do I write the query without hard-coding 'Tuesday' anywhere?

Of course I could write a function; I am not sure if I can change session settings in a function (perhaps it's easy, it never crossed my mind), but I can certainly wrap one of the "first two hacks" within a function to hide it from the user.

Regarding the slow boat - I imagine it is only available to paying customers. I am not one of them.

Best,   mathguy

Hi,

It's a shame that NEXT_DAY doesn't have an option for specifying the language, the way that (for example) TO_CHAR does.    If we're going to use NEXT_DAY, then, unfortunately, we must figure out what the desired day of the week is called in the current language, whatever that may be.

If we want to find the next Tuesday, the methods you posted require that we first find a DATE that is a Tuesday, or a NUMBER that represents Tuesday.  It would be better (though still ugly) if we could pass 2 strings: one spelling out the desired day of the week, and the other giving the language.  That is, given a table like this:

CREATE TABLE  table_x
(   dt      DATE           -- 1st argument to NEXT_DAY
,   dy      VARCHAR2 (10)  -- desired day-of-the-week
,   lang    VARCHAR2 (10)  -- desired language
,   ans     DATE           -- correct answer (for testing)
);
INSERT INTO table_x (dt, dy, lang, ans) VALUES (DATE '2017-03-01', 'LUN.', 'French',  DATE '2017-03-06');
INSERT INTO table_x (dt, dy, lang, ans) VALUES (DATE '2017-03-01', 'DI',   'German',  DATE '2017-03-07');
INSERT INTO table_x (dt, dy, lang, ans) VALUES (DATE '2017-03-01', 'WED',  'English', DATE '2017-03-08');

we'd like to call NEXT_DAY with values derived from the dt, dy and lang columns, and have it return the value in the ans column.

Here's one way to do that:

WITH    week    AS
(
    SELECT  SYSDATE + LEVEL  AS w_dt
    FROM    dual
    CONNECT BY  LEVEL <= 7
)
,   got_nxt    AS
(
    SELECT  x.*
    ,       NEXT_DAY ( dt
                     , (
                           SELECT  TO_CHAR ( w_dt
                                           , 'DY'
                                           )
                           FROM    week
                           WHERE   TO_CHAR ( w_dt
                                           , 'DY'
                                           , 'NLS_DATE_LANGUAGE=' || lang
                                           ) = dy
                       )
                     )  AS nxt
    FROM    table_x  x
)
SELECT    n.*
,         CASE
              WHEN  nxt = ans
              THEN  'OK'
              ELSE  '***  WRONG!  ***'
          END  AS flag
FROM      got_nxt  n
ORDER BY  dt, dy, lang
;

TO_CHAR does allow us to specify a language.  The query above applies the power of TO_CHAR to our inputs.

Here's the output from the query above (with NLS_DATE_LANGUAGE=ENGLISH):

DT              DY    LANG       ANS             NXT             FLAG
--------------- ----- ---------- --------------- --------------- -----
01-Mar-2017 Wed DI    German     07-Mar-2017 Tue 07-Mar-2017 Tue OK
01-Mar-2017 Wed LUN.  French     06-Mar-2017 Mon 06-Mar-2017 Mon OK
01-Mar-2017 Wed WED   English    08-Mar-2017 Wed 08-Mar-2017 Wed OK

You could adapt this so that

• it didn't matter if dy was the abbreviation or the full name (as in NEXT_DAY), case-insensitive, and/or
• lang was optional.

I'll leave those as exercises.

Create a function which addresses the issue, then you can reuse and forget about it....

create or replace function next_day_nls(pDate date, pDayOfWeek varchar2, pNLSDateLang varchar2) return date is

  vResult date;

begin

  begin

    select next_day(pDate, DOW_Native)

    into vResult

    from (

      select to_char(dt, 'FMDAY', NLSParam) DOW, to_char(dt, 'FMDAY') DOW_Native 

      from (

        select trunc(sysdate, 'iw') + level - 1 dt, 'NLS_DATE_LANGUAGE='||pNlsDateLang NLSParam

        from dual

        connect by level <= 7

      )

    )

    where upper(substr(pDayOfWeek, 1, 3)) = substr(DOW, 1, 3);   

  exception

    when NO_DATA_FOUND or TOO_MANY_ROWS then

      vResult := next_day(pDate, pDayOfWeek);  -- Try default

  end;

  return vResult;

end;

alter session set nls_language = 'FRENCH';

select next_day_nls(sysdate, 'saturday', 'english') from dual

ND

11/03/2017 18:01:20

Hi,

Starting in Oracle 12, the query in reply #3 can be shortened considerably, using CROSS APPLY, like this:

SELECT    x.*
,         n.nxt
,         CASE
              WHEN  n.nxt  = x.ans
              THEN  'OK'
              ELSE  '***  WRONG!  ***'
          END  AS flag
FROM      table_x  x
CROSS APPLY  (
                 SELECT  x.dt + LEVEL  AS nxt
                 FROM    dual
                 WHERE   TO_CHAR ( x.dt + LEVEL
                               , 'DY'
                               , 'NLS_DATE_LANGUAGE=' || x.lang
                               )  = x.dy
                 CONNECT BY  LEVEL  <= 7
             ) n
ORDER BY  dt, dy, lang
;

NEXT_DAY takes second parameter as day name or day number, so hack#1 can be done like:

next_day(some_date,to_number(to_char(date '1-1-1' + 5,'d')))

where offset is 1 - 7 corresponding to Sunday - Monday. For example, next Thursday regardless of client:

SQL> select next_day(sysdate,to_number(to_char(date '1-1-1' + 5,'d'))) from dual;

NEXT_DAY(

---------

09-MAR-17

SQL> alter session set nls_territory=germany;

Session altered.

SQL> select next_day(sysdate,to_number(to_char(date '1-1-1' + 5,'d'))) from dual;

NEXT_DAY

--------

09.03.17

SQL>

SY.

NEXT_DAY takes second parameter as day name or day number [...]

Interesting. I will file this under "undocumented overrides overloads". The documentation, to which I provided a link in my original post, does not mention it.

Other than that, the solution does pretty much the same thing as my "first hack", it just provides a number for the day of the week instead of a name in a specific language. There may be some benefits to this (perhaps less computation, checking system tables for day names in various languages). Not too happy about having one more function call (explicit as you did, or implicit if we don't use it) to convert from string to number.

I would rather use this with my "second hack" - having to know beforehand what day of the week was '1-1-1' is not too appealing.

Thank you!    mathguy

mathguy wrote:
NEXT_DAY takes second parameter as day name or day number [...]
Interesting. I will file this under "undocumented overrides". The documentation, to which I provided a link in my original post, does not mention it.

Well - maybe not so 'interesting' since 'day number' brings its own set of issues since it (and other format variables) is dependent on the NLS_TERRITORY parameter:

https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_commands_1029.htm#OLADM780

The datetime format element D returns the number of the day of the week (1-7). The day of the week that is numbered 1 is specified implicitly by the initialization parameter NLS_TERRITORY.

So day 1 may be Sunday in some territories but 7 in others where 1 is Monday.

Reread my reply. Result is NLS independent. If client is in the US then to_char(sysdate + 5,'d') will return 5 and, since US week starts Sunday, 5 means Thursday. If client is in Romania or Lithuania, then to_char(sysdate + 5,'d') will return 4 and, since week there starts Monday, 4 again means Thursday.  If client is in Oman, then to_char(sysdate + 5,'d') will return 6 and, since week there starts Saturday, 6 again means Thursday.

SY.

I can't speak for RP, but I don't think he was criticizing your solution; I think he was just pointing out, correctly, that numeric "day of the week" is dependent on NLS_TERRITORY, so in a solution (like yours) you have to account for that. As you did. No doubt the solution you provided is correct, it just has to work around this difficulty.

I wonder, could one expand on your solution by creating a package of "enumerated values"??

create or replace
package day_enum
authid current_user
as
function tuesday return int;
...
end;
/
create or replace
package body day_enum
as
function tuesday return int
as
begin
  return to_number(  to_char( to_date( '3-jan-2017', 'dd-mon-yyyy'), 'd' ) );
end;
....
end;
/

Then, the end users would only need to do the following:

select next_day( sysdate, DAY_ENUM.TUESDAY() ) from dual

MK

mathguy

Make sure you create a post in

MK

Reread my reply. Result is NLS independent. If client is in the US then to_char(sysdate + 5,'d') will return 5 and, since US week starts Sunday, 5 means Thursday. If client is in Romania or Lithuania, then to_char(sysdate + 5,'d') will return 4 and, since week there starts Monday, 4 again means Thursday. If client is in Oman, then to_char(sysdate + 5,'d') will return 6 and, since week there starts Saturday, 6 again means Thursday.

You are correct.

The result is NLS independent because NLS_TERRITORY is used two times. It is used once to determine the day number and then used again when that day number is used in the NEXT_DAY method.

The solution you posted relies on NEXT_DAY accepting a day of week number. Couldn't that be simplified by removing the TO_NUMBER and using 'day' in the format instead of 'd'?

select next_day(sysdate,to_char(date '1-1-1' + 5,'day')) from dual;

I can't speak for RP, but I don't think he was criticizing your solution;

I can speak for him and he was wrong!

I think he was just pointing out, correctly, that numeric "day of the week" is dependent on NLS_TERRITORY, so in a solution (like yours) you have to account for that.

He accounted for it by using NLS_TERRITORY twice (see my reply to him) - once in the TO_CHAR used to extract the 'day number' based on the dummy date literal '1-1-1'. Then again in the NEXT_DAY  where the day number has already been adjusted for the territory.

That dummy date literal '1-1-1' could be ANY literal. It just needs to be a value that allows the +5 to map to Thursday (assuming you want that mapping - 1 - Sunday, 2 - Monday, etc

This works.

select next_day(sysdate,to_char(date '1967-3-25' + 5,'day')) from dual;

Just pick any date at all and then adjust it until the +5 gives you a thursday:

select to_char(date '1972-8-17' + 5,'day') from dual;

That gives a Tuesday so it would map '5' to Tuesday. So just adjust the date by 2 days

select to_char(date '1972-8-19' + 5,'day') from dual;

Now you get a Thursday so the mapping is correct. Repeat Solomon's adjusted query

select next_day(sysdate,to_char(date '1972-8-19' + 5,'day')) from dual;
. . .
09.03.17

The '1-1-1' just happens to work so is a more convenient value.

So eliminating the undocumented need for the TO_NUMBER works also

select next_day(sysdate,to_char(date '1972-8-19' + 5,'day')) from dual;
. . .
09.03.17

That avoids the  problem you mentioned about the 'day' being different in different languages

The "day of the week" argument must be in the NLS_DATE_LANGUAGE of the session in which the function is invoked.

You just use Solomon's technique but extract the textual name of the day instead of the number so that you are using the documented API of the NEXT_DAY function.

What about setting 7 context values to do the translations for you?
You might consider this using a magic value, but there is no math predicated on a known Date/Day-of-Week.  The setup only assumes that there are 7 days in a week and then iterates getting whatever value they happen to be.  You could use sysdate instead of a fixed date if you wanted.

If you wanted to support a dynamic nls_territory so you use the undocumented numeric parameter, you could do that with this method too.
You'd have to extend the context translation setup to change to the passed in territory and then put it back.
Since all the "smarts" only need to happen once and are then kept for the duration of the session it should scale well for many values.
Rather than relying on a 3-character truncation, I simply add both the full name and the abbreviation to the context.

Also, I didn't (but could have) empty the context on each call of the set.  So using this you could populate all of the days of the week for every language.

That may or may not be considered a feature, but is easily reversible if you didn't want that.

SQL> alter session set nls_date_format='yyyy-mm-dd Day';

Session altered.

SQL> CREATE OR REPLACE CONTEXT my_nls_context

  2      USING my_nls_contexts;

Context created.

SQL>

SQL> CREATE OR REPLACE PACKAGE my_nls_contexts

  2  AS

  3      PROCEDURE set_translations(p_language IN VARCHAR2);

  4  END;

  5  /

Package created.

SQL>

SQL> CREATE OR REPLACE PACKAGE BODY my_nls_contexts

  2  AS

  3      PROCEDURE set_translations(p_language IN VARCHAR2)

  4      IS

  5      BEGIN

  6          FOR i IN 1 .. 7

  7          LOOP

  8              DBMS_SESSION.set_context('my_nls_context',

  9                                       TO_CHAR(DATE '2017-03-07' + i, 'fmDAY', 'NLS_DATE_LANGUAGE=' || p_language),

10                                       TO_CHAR(DATE '2017-03-07' + i, 'DY'));

11              DBMS_SESSION.set_context('my_nls_context',

12                                       TO_CHAR(DATE '2017-03-07' + i, 'DY', 'NLS_DATE_LANGUAGE=' || p_language),

13                                       TO_CHAR(DATE '2017-03-07' + i, 'DY'));

14          END LOOP;

15      END;

16  END;

17  /

Package body created.

SQL> begin

  2      my_nls_contexts.set_translations('French');

  3  end;

  4  /

PL/SQL procedure successfully completed.

SQL> select next_day(date '2017-03-07', sys_context('my_nls_context','Mardi')) d from dual;

D

--------------------

2017-03-14 Tuesday

SQL> select next_day(date '2017-03-07', sys_context('my_nls_context','Lundi')) d from dual;

D

--------------------

2017-03-13 Monday

SQL> begin

  2      my_nls_contexts.set_translations('German');

  3  end;

  4  /

PL/SQL procedure successfully completed.

SQL> select next_day(date '2017-03-07', sys_context('my_nls_context','Montag')) d from dual;

D

--------------------

2017-03-13 Monday

SQL>

select next_day(date '2017-08-01' - 1, 'Tuesday') as next_tue from dual;

another workaround is

select trunc(date '2017-08-01' - 1,'iw') + 1 + case when trunc(date '2017-08-01' - 1,'iw')+1 < date '2017-08-01' then 7 else 0 end as next_tue from dual;

It is nls independent, because It avoids any type of datatype conversions (and uses iw). But somehwat difficult to read.

The solution, like my hacks, does depend on NLS settings to some degree - if we insist on passing 'Tuesday' as an argument, and not "second day of the ISO week". There is still a translation to convert the name 'Tuesday' to the number 2. (Again, to be clear - I did the same in my own "hacks".)

The code would need some changes, even if the "input" is the ISO day number. For one thing, next_day() returns the same time-of-day as its date input, while the solution you offered truncates to midnight. Also, with the formula exactly as written, if the input is a Tuesday this solution returns that day, not a week later as next_day() does. (Also, regardless of the formula, having a strict inequality, <, with truncated date on the left and non-truncated date on the right is going to be incorrect in general, since the result will depend on whether the input date has a non-zero time-of-day. But the formula as written doesn't work even if we restrict the whole discussion to truncated dates.)

Something like this should work ( dt is the input date):

trunc(dt, 'iw') + (dt - trunc(dt)) + case when dt - trunc(dt, 'iw') < 1 then 1 else 7 + 1 end
-- The magic number 1 appears three times in the query (highlighted in red).  
-- The magic number should be 0 for Monday, 1 for Tuesday, etc.

Cheers,  mathugy

I ended up writing a function for this. I wasn't going to (there is really no point in us writing our own functions to work around a limitation that Oracle themselves could easily fix - not unless one has a pressing need, and I don't). But I have only written one function so far (posted last year in a thread on implementing a prime numbers algorithm), so I can use the practice.

I offer it here for critique. In particular I am not pleased by the way I achieved one of my goals: making the system raise the "relevant" error when the user inputs an invalid nls parameter or an invalid day-of-the-week. I marked those spots specifically in the code, with comments about what I did. (I read about error handling both in Steven Feuerstein's book and in BluShadow's article in this community, and I still can't remember - and couldn't find quickly - if I can raise a system error directly from my code...)

My goals were:

Write a function my_next_day with three parameters: an input date, the name of a day of the week, and an nls parameter to pass in the language. As in other date functions, the nls parameter should be optional - it should default to the invoking session's NLS_DATE_LANGUAGE.

Allow the same syntax for the nls parameter as in other date functions:  'nls_date_language = French' and 'nls_date_language = ''French'''  should both work, etc.

Also, there should be flexibility for the name of the day-of-the-week (as there is in the system function NEXT_DAY); that is, if an initial segment of the argument passed in matches a valid name in either 'dy' or 'fmday' format, it should be accepted.

Here's what I came up with. I did some testing and it seems to do everything I wanted, but perhaps not as efficiently as it could - and I have some doubt about the way I handled exceptions. (I was lazy - I didn't want to write my own, I wanted to piggy-back on system exceptions, and I am guessing that there should be better ways than I did.)

create or replace function my_next_day (
    dt        in date    ,
    dow       in varchar2,
    nls_param in varchar2  default null
  ) return varchar2
  deterministic
as
  ldow     varchar2(200)  := lower(dow);
  lvl      number         := 1;
  flag     boolean        := false;
  ret_dt   date;
  day_name varchar2(200);
begin
  if nls_param is null
  then
    ret_dt := next_day(dt, dow);
  else
        -- the next statement is for testing purposes only: if the nls_param is invalid,
        -- the system exception for invalid nls parameter will be raised.
        -- IS THERE A BETTER WAY TO ACHIEVE THIS?  day_name is not used anywhere else.
    select to_char(dt, 'dy', nls_param) into day_name from dual;
    loop
    exit when flag = true or lvl = 8;
      if    ldow like to_char(dt + lvl, 'dy'   , nls_param) || '%'
         or ldow like to_char(dt + lvl, 'fmday', nls_param) || '%'
      then
        ret_dt := dt + lvl;
        flag   := true;
      end if;
      lvl := lvl + 1;
    end loop;
    if flag = false
    then
        -- the next statement is intentionally invalid - I use it to force
        -- the system to throw the "not a valid day of the week" error.
        -- IS THERE A BETTER WAY TO ACHIEVE THIS?        
      ret_dt := next_day(dt, 'not a valid day of the week');
    end if;
  end if;
  return ret_dt;
end;
/

Hi,

Question: Is there a better way?

without given measures anything will do.

Why not create Business Object to handle this simple task. In other words wrapped code or technique you already described.

But hey, it is called Business Object so it sounds much cooler than functions etc. (my opinion)

Here it is.

create or replace
type Next is object (
  Monday date
  ,
  Tuesday date
  ,
  Wednesday date
  ,
  Thursday date
  ,
  Friday date
  ,
  Saturday date
  ,
  Sunday date
  ,
  constructor function Next( FromDate date default sysdate ) return Self as result
);
/
create or replace
type body Next is
  --
  constructor function Next( FromDate date default sysdate ) return Self as result
  is
    -- FromDate offset date
    -- DayNumber from 0 to 6
    function Weekday( FromDate date, DayNumber number ) return date
    is
      AnyMonday date default date '2016-08-01';
    begin
      return next_day( 
        FromDate
        ,to_char( AnyMonday + DayNumber, 'day' )
      );
    end;
  --
  begin
    Self.Monday     := Weekday( FromDate, 0 );
    Self.Tuesday    := Weekday( FromDate, 1 );
    Self.Wednesday  := Weekday( FromDate, 2 );
    Self.Thursday   := Weekday( FromDate, 3 );
    Self.Friday     := Weekday( FromDate, 4 );
    Self.Saturday   := Weekday( FromDate, 5 );
    Self.Sunday     := Weekday( FromDate, 6 );
    return;
  end;
end;
/
-- tests
select Next().Tuesday as "Next Tuesday" from dual;
select Next( date '2017-3-8' ).Monday as "Next Monday" from dual;
drop type Next;
Type NEXT compiled
Type body NEXT compiled
Next Tuesday
------------
14-MAR-17   
Next Monday
-----------
13-MAR-17  
Type NEXT dropped.

Edited body bit cleaner.

Looks very similar to my solution! Just saying....  I suspect my select version is faster than your PL/SQL loop, even with the short circuit, but it is kind of doing the same thing - Oracle is very quick at SQL filtering, it has been optimised for decades and until the cows come home.  I look for the first 3 chars as I found a doc somewhere that said that was the min number of letters for day uniqueness across languages.  I put exception handling in just in case that isn't the case.  "Like" isn't particularly quick as it isn't checking in a static way.

if Flag = False (yuk),

if not Flag...

Is it strictly correct to put deterministic on your function?  If you exclude the nls_param, then wouldn't your result depend on your nls_language (nls_date_language)?

Wouldn't the result of say....

my_next_day(sysdate, 'Saturday')

depend on your language?

Same inputs, different result?

if nls_param is null

  then

    ret_dt := next_day(dt, dow);

  else

This will fail if "dow" is numeric.

The undocumented feature of using a numeric value for NLS_TERRITORY is only supported in SQL, not in PL/SQL.

You could still do it if you wanted by using a sql statement; but then your function incurrs an additional hit for context switch.

select  next_day(dt, dow) into ret_dt from dual;

Of course, if you make the assumption that your values will be names or abbreviations then it won't be a problem because an exception would be appropriate.

It is similar, but not very.

I hadn't read your solution closely (I find that trying to write my own function teaches me more than reading someone else's code). But now I have.

The three-letter thing is not right; in German, two letters suffice. Also, in French the first three letters do not suffice. LUNDI is shortened to LUN. (with the period); LUN alone does not work, it raises the "not a valid day of the week" exception.

Hierarchical query vs. PL/SQL loop: I have no doubt that the SQL version is faster, but I was more concerned with the context switch, from PL/SQL to SQL and back. I thought that is always a concern, is that wrong? (That is also one of the reasons I didn't like my so-called exception "handling".)

And, overall - the first version of the function I wrote did the same thing as yours does, finding the translation of the provided day-of-the-week name to the invoking session's NLS_DATE_LANGUAGE to use it with NEXT_DAY. But then I realized, since I do those computations in the loop anyway, as soon as I find a match I have also found the NEXT_DAY! I have no need to translate back to the session's NLS_DATE_LANGUAGE and apply NEXT_DAY anymore.

I hope this makes sense!

Cheers,   mathguy

Ugh...  You are probably right. I don't remember - does dependence on session parameters render a function non-deterministic?

That's why I put my function out there - to get this feedback!      Thank you,   -   mathguy

-- the next statement is intentionally invalid - I use it to force

-- the system to throw the "not a valid day of the week" error.

-- IS THERE A BETTER WAY TO ACHIEVE THIS?       

Yes, in your declare section, define the exception

  invalid_day_of_week EXCEPTION;
  PRAGMA EXCEPTION_INIT(invalid_day_of_week, -1846);

Then RAISE the exception when you need it

    if not flag
    then
        raise invalid_day_of_week;
    end if;

I didn't intend to implement the undocumented feature.     Thank you,    -   mathguy

Aha - yes, that rings a bell (I even scanned the two pages in Feuerstein's book about this as I was looking it up - I just didn't recognize that was it).   Thanks!

You don't really even need a separate flag.  If you don't have a ret_dt defined you know you have an error.

If you are expecting "good" names, you don't need to use like, you can do an exact match.  To be a little more forgiving, add a trim in addition to the lower()
Also, since the first thing you do in your loop is a to_char,  you don't need to an extra pre-to_char to see if the nls_param will work or not.  If it doesn't work in the "pre-to_char" it won't work in the "real" one, so you can simply skip that step.
And, as Paulzip noted above, the function isn't really deterministic so I removed that.

Putting it all together I ended up with this version of your function.
I hope it helps.

create or replace function my_next_day(

    dt        in date    ,

    dow       in varchar2,

    nls_param in varchar2  default null

  ) return date

as

  ldow     varchar2(200)  := lower(trim(dow));

  lvl      number         := 1;

  ret_dt   date := null;

  invalid_day_of_week EXCEPTION;

  PRAGMA EXCEPTION_INIT(invalid_day_of_week, -1846);

begin

  if nls_param is null

  then

    ret_dt := next_day(dt, dow);

  else

    loop

      if  ldow in (to_char(dt + lvl, 'dy', nls_param),

                   to_char(dt + lvl, 'fmday', nls_param))

      then

        ret_dt := dt + lvl;

      end if;

      lvl := lvl + 1;

      exit when ret_dt is not null or lvl > 7;

    end loop;

    if ret_dt is null

    then

        raise invalid_day_of_week;

    end if;

  end if;

  return ret_dt;

end;

/

Edit: had 2 exit points.  Consolidted to one, and changed result to date instead of varchar2, since that is the type of the return variable

mathguy wrote:
It is similar, but not very.
I hadn't read your solution closely (I find that trying to write my own function teaches me more than reading someone else's code). But now I have.
The three-letter thing is not right; in German, two letters suffice. Also, in French the first three letters do not suffice. LUNDI is shortened to LUN. (with the period); LUN alone does not work, it raises the "not a valid day of the week" exception.
Hierarchical query vs. PL/SQL loop: I have no doubt that the SQL version is faster, but I was more concerned with the context switch, from PL/SQL to SQL and back. I thought that is always a concern, is that wrong? (That is also one of the reasons I didn't like my so-called exception "handling".)
And, overall - the first version of the function I wrote did the same thing as yours does, finding the translation of the provided day-of-the-week name to the invoking session's NLS_DATE_LANGUAGE to use it with NEXT_DAY. But then I realized, since I do those computations in the loop anyway, as soon as I find a match I have also found the NEXT_DAY! I have no need to translate back to the session's NLS_DATE_LANGUAGE and apply NEXT_DAY anymore.
I hope this makes sense!
Cheers, mathguy

You are quite right about the 3 letters, I tried it with all allowed languages and days of week. I read it on someone's blog - which was obviously incorrect!  I usually check such things (the cynic in me), but didn't.

As for context switching, yes, it's definitely something to be aware of. From my experience, context switching performance issues aren't as bad as they used to be in Oracle, I think Oracle has done lots of work on SQL <-> PL/SQL.  However, SQL is very fast even when run in PL/SQL. I would imagine emulating something SQL does using PL/SQL iterations and variable assignment will be less performant, even when considering context switch.  I can't speak for every situation, but I've certainly seen it in my day to day job.

Your function and mine will both run in very small time periods, so tuning probably not imperative anyway.

I thought about using ret_dt for checks, instead of the boolean flag, but I think the code is easier to understand with the flag, and the overhead should be minimal.

The TRIM idea will not work. How much to trim depends on the language: in German, 'Di' is sufficient; in French, 'Mar' is not enough, it must be either 'Mar.', with the period, or 'Mardi'. And, yes, I wanted the behavior to be the same as NEXT_DAY, which allows things like 'TueMathguy' - it will just read 'Tue'.

The reason I did the check for the nls parameter the way I did is because I wanted to throw the system exception. If unhandled there, the error that would be thrown is a generic PL/SQL error. But, you showed how to handle that properly, so my check is no longer needed.

Cheers,   -   mathguy

actually I was talking about trimming whitespace;  but since you want to be able to pass in weird values like 'Tue****'  then yeah, nevermind on trying to cleanup for exact matches.

Mike Kutz wrote:
mathguy
Make sure you create a post in Database Ideas 
MK

Done:           Thank you,     -    mathguy

Trim:  Sorry, I read too quickly and I wasn't paying attention - I was still "under the influence" of "truncating" the string (taking an initial substring) to three letters, from the conversation with Paulzip. I "merged" the two issues in my mind - which is incorrect, of course.

Error handling:  defining a name for the "invalid day of the week" system exception works fine, because I raise it only in a very clearly defined set of circumstances. Even for this, though, I don't know how I can be 100% sure that the flag in my code can only be FALSE if the day of the week input was incorrect; if other, weird reasons may lead to the same flag = FALSE at the end, then that specific exception shouldn't be thrown. Anyway, at least it is working as expected, as far as I can tell.

The bigger headache is the error about invalid nls_date_language. The problem, as I see it, is that the PL/SQL exception thrown for invalid nls parameter is not the same as the SQL exception for the same invalid input. (Illustration below.) If it was the same I wouldn't even need to handle it; the only reason I handled it the way I did is that I wanted the "proper" exception to be thrown (an exception that points specifically to the nls parameter as being invalid). Again - see below for illustration.

If I remember correctly, I could move just the call to TO_CHAR to its own nested block, raise the exception and handle it in the outer block. I am not too keen on doing that, since other things may cause errors in the inner block, not just the nls_date_language value. I'm still not clear on the best way to handle.

Here is the illustration I mentioned earlier. Notice that if I pass in an incorrect date format, whether to the standard SQL function or to the one I define in PL/SQL, the exception thrown is the same. Not so, however, for invalid NLS_DATE_LANGUAGE. I simplified this as much as I could - I use TO_CHAR and a trivial PL/SQL wrapper for the illustration. This explains why in my code I made a call to the SQL function - the only way I could think of to cause the SQL exception to be thrown.

SQL> create or replace function my_to_char(dt date, fmt varchar2, nlsprm varchar2)
  2    return varchar2
  3  as
  4  begin
  5    return to_char(dt, fmt, nlsprm);
  6  end;
  7  /
 
SQL> column short_day_name format a14

SQL> select to_char(sysdate, 'dy', 'nls_date_language = german') as short_day_name from dual;
SHORT_DAY_NAME
--------------
do
SQL> select my_to_char(sysdate, 'dy', 'nls_date_language = german') as short_day_name from dual;
SHORT_DAY_NAME
--------------
do
--  The next two queries demonstrate that an invalid date format model raises the same exception in SQL and in PL/SQL:
SQL> select to_char(sysdate, 'mathguy', 'nls_date_language = german') as short_day_name from dual;
select to_char(sysdate, 'mathguy', 'nls_date_language = german') as short_day_name from dual
                        *
ERROR at line 1:
ORA-01821: date format not recognized
SQL> select my_to_char(sysdate, 'mathguy', 'nls_date_language = german') as short_day_name from dual;
select my_to_char(sysdate, 'mathguy', 'nls_date_language = german') as short_day_name from dual
      *
ERROR at line 1:
ORA-01821: date format not recognized
ORA-06512: at "INTRO.MY_TO_CHAR", line 5
-- the next two queries, however, show that if the nls_date_language is invalid, 
-- SQL and PL/SQL raise different exceptions:

SQL> select to_char(sysdate, 'dy', 'nls_date_language = mathguy') as short_day_name from dual;
select to_char(sysdate, 'dy', 'nls_date_language = mathguy') as short_day_name from dual
                                                                                    *
ERROR at line 1:
ORA-12702: invalid NLS parameter string used in SQL function

SQL> select my_to_char(sysdate, 'dy', 'nls_date_language = mathguy') as short_day_name from dual;
select my_to_char(sysdate, 'dy', 'nls_date_language = mathguy') as short_day_name from dual
      *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "INTRO.MY_TO_CHAR", line 5

If I remember correctly, I could move just the call to TO_CHAR to its own nested block, raise the exception and handle it in the outer block. I am not too keen on doing that, since other things may cause errors in the inner block, not just the nls_date_language value. I'm still not clear on the best way to handle.

As is often the case it boils down to properly defining the requirements.

For your use case that is mostly the requirements for exception handling.

Oracle deals with exceptions and blocks very consistently and it is well documented so not going to go through all of that nested block stuff here.

Any exception in a block transfers control OUT of the block.

So your decision about how many statements to put IN a block is:

1. If statement A raises an exception but you want control to continue with statement B then statement A needs to be in its own block so that the 'transfer out of the block' returns control to statement B.

2. If multiple statements within a block can raise the same exception AND you want to distinguish between those statements using ONLY exception handling then the statements (all but the last at least) need to be in their own block.

3. If multiple statements within a block can raise the same exception but you do NOT want to distinguish between the statements (based ONLY on exception handling) those statements can be in the same block

The simplest method I know to not have to deal with any of that is to just use a 'STEP_NUMBER' variable that you set BEFORE each statement. Then in the common exception handler the value of that variable will indicate which statement caused the exception:

. . .
V_STEP_NUMBER := 1;
   statement #1
V_STEP_NUMBER := 2;
   statement #2
. . .
V_STEP_NUMBER := n;
   statement #n

The above is just an example. Obviously you wouldn't use sequential numbers. I typically just give a short unique name to each step and use that. That way there isn't really any issue when you add new statements in between others or move statements around.

Door #2.

What you describe is pretty much what I remember (both from Steven Feuerstein's book and from BluShadow's write-up on this site). It makes sense; I hesitate only because I've written but a handful of functions so far, and there's only so much that sticks in my mind if I don't use it.

The main "error handling" problem in this exercise, as I saw it, is that when the NLS parameter passed in is invalid, the error PL/SQL throws is non-descript. It's the same error that may be thrown for so many other reasons. To isolate the NLS parameter issue, I would need a nested  block. What you described under your point 2. (I think!)

I understand what you are describing with the steps - I'll try it on the function built here.     Thanks!    mathguy

To isolate the NLS parameter issue, I would need a nested block.

Only if you want to isolate it based on exception alone.

Otherwise use the step method.

V_STEP_NUMBER := 'X';
  -- statement that can cause the NLS parameter issue
V_STEP_NUMBER := 'Y';
  -- next statement that can cause the same exception as the NLS parameter issue

In the exception handler the value X or Y will tell you which statement caused the exception.

If you still want to do step Y if step X fails then step Y needs to be in its own block.

That 'step method' is very common in ETL batch processes especially when validating data and you need to check EVERY column in EVERY row for data issues and log the exact problem it had.

Some problems (e.g. missing/invalid primary key value) need to cause the entire row to be rejected and later resubmitted. For those you don't bother validating anything at all because you can't pass the row to the next stage anyway.

Many problems with some columns can be fixed later (missing zip code). So there are often a lot of small blocks, one for each column that might fail validation and need reporting. That way you can keep validating the remaining columns if one of the earlier ones fails.

Something like:

declare
  function my_next_day( p_date date, p_day varchar2, p_nls varchar2 := null )
  return date
  is
    l_rv date;
    l_nls varchar2(3999) := substr( ltrim( p_nls ), 1, 3980 );
    l_dy varchar2(3999);
    l_day varchar2(3999);
    invalid_day_of_week exception;
    pragma exception_init( invalid_day_of_week, -1846 );
    invalid_nls_parameter exception;
    pragma exception_init( invalid_nls_parameter, -12702 );
  begin
    if l_nls is null or p_date is null or ltrim( p_day ) is null
    then
      l_rv := next_day( p_date, p_day );
    else
      if substr( upper( l_nls ), 1, 17 ) != 'NLS_DATE_LANGUAGE'
      then
        l_nls := 'NLS_DATE_LANGUAGE=' || l_nls;
      end if;
      begin
        l_dy := to_char( sysdate, 'dy', l_nls  );
      exception
        when value_error then raise invalid_nls_parameter;
      end;
      l_day := lower( substr( trim( p_day ), 1, 3999 ) );
      l_dy := substr( l_day, 1, length( l_dy ) );
      l_rv := p_date + 1;
      while not (  to_char( l_rv, 'dy', l_nls ) = l_dy
                or to_char( l_rv, 'fmday', l_nls ) = l_day
                )
      loop
        l_rv := l_rv + 1;
        if l_rv > p_date + 8
        then
          raise invalid_day_of_week;
        end if;
      end loop;
    end if;
    return l_rv;
  end;
  procedure show( p_date date ) is
  begin
    dbms_output.put_line( to_char( p_date, 'yyyy-mm-dd hh24:mi:ss  day' ) );
  end;
begin
  execute immediate 'alter session set nls_date_language = German';
  show( my_next_day( sysdate, 'mit' ) );
  execute immediate 'alter session set nls_date_language = AMERICAN';
  show( my_next_day( sysdate, 'di', 'dutch' ) );
  show( my_next_day( sysdate, 'wed' ) );
  show( my_next_day( sysdate, 'jeu.', 'french' ) );
  show( my_next_day( sysdate, 'vendredi', 'NLS_DATE_LANGUAGE=''french''' ) );
end;  

Cool, I didn't realize/remember that ORA-06502 is a named exception (VALUE_ERROR); I'll play a bit with the way you wrote the code around that point.

Other than that, your function does more than the standard NEXT_DAY function; it allows spaces at the beginning of the "day of the week" parameter, it allows the language not to have NLS_DATE_LANGUAGE= before it, etc. That is more than my goal, which was just to make NEXT_DAY work like other date functions (which don't allow that kind of flexibility).

Thank you!    mathguy