"ORA-01839: date not valid for month specified" when adding interval

Problem

You try to add month or years to a date using interval year to month data type. But

Cause

You probably are trying from a day in a month that does not exist in the resulting month, e. g. 30th of January plus one month -> 30th of February 😓. The problem is, that adding year month interval is like just changing the month: 30 : 1 + 0 : 1 => 30 : 2 i. e. 30th of February. There is no logic to handle this within year month interval data type.

Solution

You need to employ the add_months function for that. It can be a bit tricky if you have got interval data like you can easily extract from range partion by date of the data dictionary. I wrote a function the takes a date and the function string to create an interval (as it is stored in the data dictionary) NUMTOYMINTERVAL(1, 'MONTH') and returns according date. Be aware that it uses calls to a logger. You either need to install it as well or remove those calls.

/** Date calculations are not straight foreward such that adding a
 *  year-month interval to a date will probably result in a wrong result.
 *  Actually, you would be save until day 28, because each and every month
 *  has at least 28 days. Beyond that ...
 *  Examples with  + NUMTOYMINTERVAL('1', 'MONTH')
 *
 *  Finding           Expectation     Result
 *  ok          1999-01-15  1999-02-15      1999-02-15
 *  nok         1999-02-28  last of March   1999-03-28
 *  ok          1999-02-28  1999-03-28      1999-03-28
 *  nok         1999-01-29  1999-02-28      ORA-01839: date not valid for
 *                                          month specified
 *
 *
 *  @function ADD_MONTH_YEAR_INTERVAL
 *  @param in date I_DATE Base date on that the operation of the other
 *                        parameter is executed.
 *  @param in varchar2 I_NUMTOYMINTERVAL_STRING NumToYMInterval operation
 *                                              string, e. g.
 *                                              NUMTOYMINTERVAL('1', 'MONTH')
 *  @return date Result of the NumToYMInterval operation on the base date.
 *  @throws PKG_EXCEPTIONS.CG_ERR_N_UNSUPP_OPERATION
 */
function ADD_MONTH_YEAR_INTERVAL(
    I_DATE date,
    I_NUMTOYMINTERVAL_OPERATION varchar2
)
    return date
as
    /* local constants */
    CL_ROUTINE_NAME constant USER_OBJECTS.OBJECT_NAME%type :=
      'ADD_MONTH_YEAR_INTERVAL';
    CL_PARSER_PATTERN constant varchar2(1000 char) :=
      '^\s*NUMTOYMINTERVAL\s*\(\s*(-?\s*\d+(\.\d+)?)\s*,\s*''(MONTH|YEAR)''\s*\)\s*$';
    CL_SCALE_RESULT_PATTERN constant char(2 char) := '\3';
    CL_VALUE_RESULT_PATTERN constant char(2 char) := '\1';
    CL_STATEMENT constant char(1000 char) :=
      'select add_months(:1, :2) from dual';

    /* local variables */
    VL_SCALE varchar2(1000 char);
    VL_VALUE integer;
    VL_RESULT_DATE date;
begin
    PKG_PLNSQL_LOGGER.DEBUG(
        MESSAGE => 'I_DATE: ' ||
          to_char(I_DATE, PKG_UTILITIES.CG_DATETIME_FORMAT),
        PACKAGE_NAME => CG_PACKAGE_NAME,
        FUNCTION_PROCEDURE_NAME => CL_ROUTINE_NAME
    );
    PKG_PLNSQL_LOGGER.DEBUG(
        MESSAGE => 'I_NUMTOYMINTERVAL_OPERATION: ' ||
          I_NUMTOYMINTERVAL_OPERATION,
        PACKAGE_NAME => CG_PACKAGE_NAME,
        FUNCTION_PROCEDURE_NAME => CL_ROUTINE_NAME
    );
    PKG_PLNSQL_LOGGER.DEBUG(
        MESSAGE => 'CL_PARSER_PATTERN: ' ||
          CL_PARSER_PATTERN,
        PACKAGE_NAME => CG_PACKAGE_NAME,
        FUNCTION_PROCEDURE_NAME => CL_ROUTINE_NAME
    );
    PKG_PLNSQL_LOGGER.DEBUG(
        MESSAGE => 'CL_SCALE_RESULT_PATTERN: ' ||
          CL_SCALE_RESULT_PATTERN,
        PACKAGE_NAME => CG_PACKAGE_NAME,
        FUNCTION_PROCEDURE_NAME => CL_ROUTINE_NAME
    );
    PKG_PLNSQL_LOGGER.DEBUG(
        MESSAGE => 'CL_VALUE_RESULT_PATTERN: ' ||
          CL_VALUE_RESULT_PATTERN,
        PACKAGE_NAME => CG_PACKAGE_NAME,
        FUNCTION_PROCEDURE_NAME => CL_ROUTINE_NAME
    );
    PKG_PLNSQL_LOGGER.DEBUG(
        MESSAGE => 'CL_STATEMENT: ' ||
          CL_STATEMENT,
        PACKAGE_NAME => CG_PACKAGE_NAME,
        FUNCTION_PROCEDURE_NAME => CL_ROUTINE_NAME
    );

    VL_SCALE := upper(
        regexp_replace(
            I_NUMTOYMINTERVAL_OPERATION,
            CL_PARSER_PATTERN,
            CL_SCALE_RESULT_PATTERN,
            1, -- needed to get to the matching parameter
            0, -- needed to get to the matching parameter
            'i'
        )
    );
    PKG_PLNSQL_LOGGER.DEBUG(
        MESSAGE => 'VL_SCALE: ' ||
          VL_SCALE,
        PACKAGE_NAME => CG_PACKAGE_NAME,
        FUNCTION_PROCEDURE_NAME => CL_ROUTINE_NAME
    );
    VL_VALUE := regexp_replace(
        I_NUMTOYMINTERVAL_OPERATION,
        CL_PARSER_PATTERN,
        CL_VALUE_RESULT_PATTERN,
        1, -- needed to get to the matching parameter
        0, -- needed to get to the matching parameter
        'i'
    );
    PKG_PLNSQL_LOGGER.DEBUG(
        MESSAGE => 'VL_VALUE: ' ||
          VL_VALUE,
        PACKAGE_NAME => CG_PACKAGE_NAME,
        FUNCTION_PROCEDURE_NAME => CL_ROUTINE_NAME
    );
    if VL_SCALE is null or VL_VALUE is null then
        PKG_PLNSQL_LOGGER.INFO(
            MESSAGE => 'I_NUMTOYMINTERVAL_OPERATION: ' ||
              I_NUMTOYMINTERVAL_OPERATION,
            PACKAGE_NAME => CG_PACKAGE_NAME,
            FUNCTION_PROCEDURE_NAME => CL_ROUTINE_NAME
        );
        PKG_PLNSQL_LOGGER.INFO(
            MESSAGE => 'CL_PARSER_PATTERN: ' ||
              CL_PARSER_PATTERN,
            PACKAGE_NAME => CG_PACKAGE_NAME,
            FUNCTION_PROCEDURE_NAME => CL_ROUTINE_NAME
        );
        PKG_PLNSQL_LOGGER.INFO(
            MESSAGE => 'CL_SCALE_RESULT_PATTERN: ' ||
              CL_SCALE_RESULT_PATTERN,
            PACKAGE_NAME => CG_PACKAGE_NAME,
            FUNCTION_PROCEDURE_NAME => CL_ROUTINE_NAME
        );
        PKG_PLNSQL_LOGGER.INFO(
            MESSAGE => 'CL_VALUE_RESULT_PATTERN: ' ||
              CL_VALUE_RESULT_PATTERN,
            PACKAGE_NAME => CG_PACKAGE_NAME,
            FUNCTION_PROCEDURE_NAME => CL_ROUTINE_NAME
        );
        raise_application_error(
            PKG_EXCEPTIONS.CG_ERR_N_UNSUPP_OPERATION,
            PKG_EXCEPTIONS.CG_ERR_D_UNSUPP_OPERATION
        );
    elsif VL_SCALE = 'YEAR' then
        -- There is no add_year function so we have to base on months.
        VL_VALUE := VL_VALUE * 12;
    end if;
    execute immediate CL_STATEMENT
      into VL_RESULT_DATE
      using I_DATE, VL_VALUE;

    return VL_RESULT_DATE;
end ADD_MONTH_YEAR_INTERVAL;

BTW

You are save to use the day second interval data type for this kind of calculations.

ORA-01031: insufficient privileges from within a package

Symptom

You try to create tables from within a package, function or procedure. You run the package as the owner of the schema, the stored code is in that schema and the user has the create table privilege granted by a role.
Instead of the table being created, you get ORA-01031: insufficient privileges

Cause

Unknown quirk of Oracle?

Solutions

Make sure the security context of the stored code is authid current_user

Others

Creating views on tables that have been granted by role seems to cause the same problem.

Windows console and UTF-8 and Oracle SQLPLUS

Symptom

 You run scripts in UTF-8 codepage by sqplplus over the bat shell and special characters like umlauts get borked.

Cause

There can be two:

1. Your bat shell is not running in UTF-8 codepage: Execute chcp to find out. If it returns 65001, you do not have to change anything.
2. The Oracle ODBC codepage is UTF-8, you do not have to do anything.

Solution

You probably do not need to fiddle with the Windows Registry like described below. You might want to read How to utf-8 with sqlplus too.

1. Change the codepage of your bat shell to UTF-8 by executing chcp 65001. However, this is a one shot solution only. A permanent solution could be to change registry entry HKEY_LOCAL_MACHINE\Software\Microsoft\Command Processor\Autorun to @chcp 65001>nul.
   Be aware that for some obscure reason the execution of the dir command in console makes it close invariably!
   Be aware that changing registry entry HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage\OEMCP to 65001 is reported to leave your system in an UNBOOTABLE state!
2. Change the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\KEY_OraClient12Home\NLS_LANG to AMERICAN_AMERICA.UTF8.

ORA-01891: Datetime/Interval internal error

This is a very strange error. This post is about that but is rather treating symptoms than the cause.

It is possible to get data into a date or timestamp attribute that is out of range/bounds/limits  - see my last post. If you select such data from an attribute with time zone information where your client sports another time zone than the DB server in sqlplus, you get above error but in my opinion the actual error is not the selecting but the storing data out of range. I cannot tell whether ORA-01891 can have other causes, but just said is definitely one. You can reproduce with the following.

begin
 for R in(
  select
   table_name
  from
   user_tables
  where
   table_name = 'TEST_ORA_01891'
 ) loop
  execute immediate 'drop table ' || R.TABLE_NAME || ' purge';
 end loop;
end;
/

create
 table
  TEST_ORA_01891(
   ts_1 timestamp(1),
   tstz_1 timestamp(1) with local time zone
  ) nologging;

insert
 into
  TEST_ORA_01891
 values(
  to_timestamp( '9999-12-31 23:59:59.999999999', 'yyyy-mm-dd hh24:mi:ss.ff9' ),
  to_timestamp( '9999-12-31 23:59:59.999999999', 'yyyy-mm-dd hh24:mi:ss.ff9' )
 );

commit;

desc TEST_ORA_01891

prompt Select from timestamp without time zone works.
set echo on
select
 ts_1
from
 TEST_ORA_01891;
set echo off

prompt Select from timestamp WITH time zone fails.
set echo on
select
 tstz_1
from
 TEST_ORA_01891;
set echo off

select sessiontimezone from dual;

begin
 for R in(
  select
   table_name
  from
   user_tables
  where
   table_name = 'TEST_ORA_01891'
 ) loop
  execute immediate 'drop table ' || R.TABLE_NAME || ' purge';
 end loop;
end;
/

exit

PL/SQL-Prozedur erfolgreich abgeschlossen.


Tabelle wurde erstellt.


1 Zeile wurde erstellt.


Transaktion mit COMMIT abgeschlossen.

 Name                                      Null?    Typ
 ----------------------------------------- -------- ----------------------------
 TS_1                                               TIMESTAMP(1)
 TSTZ_1                                             TIMESTAMP(1) WITH LOCAL TIME
                                                     ZONE

Select from timestamp without time zone works.
SQL> select
  2          ts_1
  3  from
  4          TEST_ORA_01891;

TS_1
---------------------------------------------------------------------------
01.01.00 00:00:00,0

SQL> set echo off
Select from timestamp WITH time zone fails.
SQL> select
  2          tstz_1
  3  from
  4          TEST_ORA_01891;
ERROR:
ORA-01891: Interner Datumzeit-/Intervall-Fehler


Es wurden keine Zeilen ausgewõhlt
SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
--------------------------------------
+02:00
SQL> set echo off

PL/SQL-Prozedur erfolgreich abgeschlossen.

Precondition is that the timezone of client and server differ - see above and:

C:\Users\kellnert>tzutil /g
W. Europe Standard Time

W. Europe Standard Time (UTC+01:00) Amsterdam, Berlin, Bern, Rom, Stockholm, Wien
W. Europe Standard Time

Funny enough at least two Java clients are not affected by this error as following screenshots illustrates.

What with timestamp 10000-01-01 00:00:00000000000?

Oracle defines the maximum of a date (or timestamp) to be 9999-12-31 23:59:59(.999999999). Nonetheless it is possible to get a date as told in the subject. It is sort of a rounding problem. If you insert 9999-12-31 23:59:59.999999999 into an attribute of type timestamp(x) with x < 9 Oracle rounds up to the date in the subject.

sys_guid() not pseudo-random

It turns out that on some platforms sys_guid() is NOT pseudo-random, see also http://feuerthoughts.blogspot.ch/2006/02/watch-out-for-sequential-oracle-guids.html
RFC 4122  only specifies version 4 to be pseudo-random (see also Wikipedia). Oracle however specifies it non to be pseudo-random (see also its example).