Forum Stats

  • 3,852,479 Users
  • 2,264,108 Discussions
  • 7,905,079 Comments

Discussions

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

124»

Answers

  • sdstuber
    sdstuber Member Posts: 215 Bronze Badge
    edited Mar 8, 2017 11:37PM

    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.

  • mathguy
    mathguy Member Posts: 10,675 Blue Diamond
    edited Mar 9, 2017 10:34AM
    Mike Kutz wrote:mathguyMake sure you create a post in Database Ideas MK

    Done:           Thank you,     -    mathguy

  • mathguy
    mathguy Member Posts: 10,675 Blue Diamond
    edited Mar 9, 2017 9:16PM

    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
    --------------
    doSQL> 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 recognizedSQL> 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 recognizedORA-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
  • Unknown
    edited Mar 9, 2017 10:20PM
    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 #1V_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.

  • mathguy
    mathguy Member Posts: 10,675 Blue Diamond
    edited Mar 9, 2017 10:46PM

    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

  • Unknown
    edited Mar 9, 2017 10:59PM
    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 issueV_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.

  • Anton Scheffer
    Anton Scheffer Member Posts: 1,950 Gold Trophy
    edited Mar 10, 2017 9:54AM

    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;  
  • mathguy
    mathguy Member Posts: 10,675 Blue Diamond
    edited Mar 10, 2017 10:54AM

    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

This discussion has been closed.