Forum Stats

  • 3,851,965 Users
  • 2,264,055 Discussions
  • 7,904,918 Comments

Discussions

Make NEXT_DAY() independent of the invoking session's NLS_DATE_LANGUAGE

13

Answers

  • Paulzip
    Paulzip Member Posts: 8,748 Blue Diamond
    edited Mar 8, 2017 1:20PM

    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?

  • sdstuber
    sdstuber Member Posts: 215 Bronze Badge
    edited Mar 8, 2017 1:28PM

    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.

  • mathguy
    mathguy Member Posts: 10,674 Blue Diamond
    edited Mar 8, 2017 1:28PM

    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

  • mathguy
    mathguy Member Posts: 10,674 Blue Diamond
    edited Mar 8, 2017 1:31PM

    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

  • sdstuber
    sdstuber Member Posts: 215 Bronze Badge
    edited Mar 8, 2017 1:34PM

    -- 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;

    mathguy
  • mathguy
    mathguy Member Posts: 10,674 Blue Diamond
    edited Mar 8, 2017 1:37PM

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

  • mathguy
    mathguy Member Posts: 10,674 Blue Diamond
    edited Mar 8, 2017 1:38PM

    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!

  • sdstuber
    sdstuber Member Posts: 215 Bronze Badge
    edited Mar 8, 2017 1:53PM

    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

  • Paulzip
    Paulzip Member Posts: 8,748 Blue Diamond
    edited Mar 8, 2017 2:02PM
    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.

  • mathguy
    mathguy Member Posts: 10,674 Blue Diamond
    edited Mar 8, 2017 2:04PM

    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

This discussion has been closed.