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.