Hi everybody,
I'm struggling with a strange casting problem between TIMESTAMP and DATE. Strange, because this happens in SQL-Developer and in the Testdatabase, but not when called from within SQL-Plus, although all Session Parameters are set up completely equal.
I'm working on Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 on Windows and execute the following script, taken from SQL-Developer Skript output:
<pre>
create table ts_test(
stamp timestamp,
stamp_copy timestamp)
table TS_TEST erstellt.
create or replace view vw_test as
select cast(stamp as date) stamp,
cast(stamp_copy as date) stamp_copy
from ts_test
view VW_TEST erstellt.
insert into ts_test
select systimestamp, null from dual
1 Zeilen eingefügt.
commit
festgeschrieben.
create or replace trigger trg_ts_test_iu
instead of update on vw_test
begin
update ts_test
set stamp_copy = :new.stamp_copy;
end;
TRIGGER trg_ts_test_iu kompiliert
update vw_test
set stamp_copy = stamp
1 Zeilen aktualisiert.
select stamp, stamp_copy, dump(stamp_copy)
from ts_test
STAMP STAMP_COPY DUMP(STAMP_COPY)
-----------------------------------------------------------------------------------------------------------------
30.05.2012 19:08:49,218000000 30.05.3192 20:09:50,000000000 Typ=180 Len=7: 131,192,5,30,21,10,51
select *
from nls_session_parameters
PARAMETER VALUE
----------------------------------------------------------------------
NLS_LANGUAGE GERMAN
NLS_TERRITORY GERMANY
NLS_CURRENCY €
NLS_ISO_CURRENCY GERMANY
NLS_NUMERIC_CHARACTERS ,.
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT dd.mm.yyyy hh24:mi:ss
NLS_DATE_LANGUAGE GERMAN
NLS_SORT GERMAN
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD.MM.YYYY HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD.MM.YYYY HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY €
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS CHAR
NLS_NCHAR_CONV_EXCP FALSE
17 Zeilen gewählt
rollback
Rollback abgeschlossen.
</pre>
Look at the strange data that stamp_copy has after the update.
When run from within SQL-Plus, everything is fine. But when I execute this code in our application, the same happens. I even saw a data dating to year 11907 (!), giving an exception when selecting it from the table.
Has anybody any idea on how to work around this bug?
Best regards,
Jürgen