Strange Timestamp Behavior

17 02 2011

February 17, 2011

I have not experimented much with the TIMESTAMP datatype in Oracle – the DATE datatype is usually sufficient for my needs.  I thought that I would try to build a couple of test scripts to experiment with TIMESTAMPs, but I hit a bit of a snag in my testing.  The first script that I built follows:

DROP TABLE T1;

CREATE TABLE T1 (
  C1 TIMESTAMP(9));

CREATE OR REPLACE TRIGGER T1_UPDATE BEFORE UPDATE ON T1 FOR EACH ROW
BEGIN
  :NEW.C1 := :NEW.C1 + 1;
END T1_UPDATE;
/

ALTER SESSION SET NLS_TIMESTAMP_FORMAT="DD-MON-YY HH24:MI:SS";

INSERT INTO T1 VALUES(SYSDATE);

SELECT
  *
FROM
  T1;

UPDATE T1 SET C1=SYSDATE;

SELECT
  *
FROM
  T1; 

The above script creates a table with a single column having a TIMESTAMP(9) column, creates a trigger on the table that always adds one day to the TIMESTAMP column value every time a row is updated, inserts a row into the table, selects from the table, updates the row that was just inserted, and then selects from that table again.  Would you believe that the results for Oracle Database 10.2.0.2 and 11.1.0.7 are different for this test script?

Output from 10.2.0.2:

SQL> SELECT
  2    *
  3  FROM
  4    T1;

C1
------------------------------
16-FEB-11 10:45:03

SQL>
SQL> UPDATE T1 SET C1=SYSDATE;

1 row updated.

SQL>
SQL> SELECT
  2    *
  3  FROM
  4    T1;

C1
------------------------------
 (NULL)

Output from 11.1.0.7:

SQL> SELECT
  2    *
  3  FROM
  4    T1;

C1
------------------------------
16-FEB-11 10:47:05

SQL>
SQL> UPDATE T1 SET C1=SYSDATE;

1 row updated.

SQL>
SQL> SELECT
  2    *
  3  FROM
  4    T1;

C1
------------------------------
17-FEB-11 10:47:05 

Metalink (MOS) Doc ID 5352587.8, Bug 5352587 - "Triggers not working correctly with timestamp datatype" seems to show a problem similar to the above, as does Metalink (MOS) Bug 5860236 - "Timestamp in trigger given NULL value on update without use of TO_TIMESTAMP".

No problem, we can work around that problem with a slight modification to our script:

DELETE FROM T1;

INSERT INTO T1 VALUES(SYSDATE);

SELECT
  *
FROM
  T1;

UPDATE T1 SET C1=TO_TIMESTAMP(SYSDATE);

SELECT
  *
FROM
  T1; 

Output from 10.2.0.2:

SQL> SELECT
  2    *
  3  FROM
  4    T1;

C1
--------------------------------------------
16-FEB-11 10:57:02

SQL>
SQL> UPDATE T1 SET C1=TO_TIMESTAMP(SYSDATE);

1 row updated.

SQL>
SQL> SELECT
  2    *
  3  FROM
  4    T1;

C1
--------------------------------------------
17-FEB-11 12:00:00 

The date value is what was expected, but we lost the time component - the problem is obvious.  Let's try again with another slight modification to the script:

ALTER SESSION SET NLS_DATE_FORMAT="DD-MON-YY HH24:MI:SS";

DELETE FROM T1;

INSERT INTO T1 VALUES(SYSDATE);

SELECT
  *
FROM
  T1;

UPDATE T1 SET C1=TO_TIMESTAMP(SYSDATE);

SELECT
  *
FROM
  T1; 

Output from 10.2.0.2:

SQL> SELECT
  2    *
  3  FROM
  4    T1;

C1
--------------------------------------------
16-FEB-11 11:00:26

SQL>
SQL> UPDATE T1 SET C1=TO_TIMESTAMP(SYSDATE);

1 row updated.

SQL>
SQL> SELECT
  2    *
  3  FROM
  4    T1;

C1
--------------------------------------------
17-FEB-11 11:00:26 

The above shows exactly what we should expect.

----

There are quite a few different ways to return a TIMESTAMP from the database that represents the current date and time.  For example:

A recent thread in an Oracle forum was closed prematurely without providing the original poster (OP) a usable solution other than to file a bug report - lately, I sometimes wonder what is the intended purpose of that forum due to the number of threads that are closed without providing much of any help to the OP (other than a half dozen partially related links - it is important to recognize that there are a couple of people who post on that forum very well thought out, specific solutions for the problems faced by the OPs).  The test case script that the OP provided is quite interesting, and I have posted my results below when the script was executed on several different Oracle Database release versions.

Output from 10.2.0.2:

O timestamp obtido eh: [16-FEB-11 01.46.42.616000 PM]

DATA      TEXTO
--------- -------------------------------------------------------------------------------------------------
16-FEB-11 1:old.FLAG=123, :new.FLAG=321
16-FEB-11 1:old.TAG=teste trigger, :new.TAG=teste trigger
16-FEB-11 1:old.TS_ATU_DTR=16-FEB-11 01.46.42.523000000 PM, :new.TS_ATU_DTR=16-FEB-11 01.46.42.523000000 PM

16-FEB-11 2:old.FLAG=123, :new.FLAG=321
16-FEB-11 2:old.TAG=teste trigger, :new.TAG=10
16-FEB-11 2:old.TS_ATU_DTR=16-FEB-11 01.46.42.523000000 PM, :new.TS_ATU_DTR=16-FEB-11 01.46.42.616000000 PM

Output from 10.2.0.5:

O timestamp obtido eh: [16-FEB-11 06.18.56.532000 PM]

DATA      TEXTO
--------- -------------------------------------------------------------------------------------------------
16-FEB-11 1:old.FLAG=123, :new.FLAG=321
16-FEB-11 1:old.TAG=teste trigger, :new.TAG=teste trigger
16-FEB-11 1:old.TS_ATU_DTR=16-FEB-11 06.18.56.345000000 PM, :new.TS_ATU_DTR=

16-FEB-11 2:old.FLAG=123, :new.FLAG=321
16-FEB-11 2:old.TAG=teste trigger, :new.TAG=10
16-FEB-11 2:old.TS_ATU_DTR=16-FEB-11 06.18.56.345000000 PM, :new.TS_ATU_DTR=16-FEB-11 06.18.56.532000000 PM 

Output from 11.1.0.7:

O timestamp obtido eh: [16-FEB-11 01.46.41.333000 PM]

DATA      TEXTO
--------- ------------------------------------------------------------------
16-FEB-11 1:old.FLAG=123, :new.FLAG=321
16-FEB-11 1:old.TAG=teste trigger, :new.TAG=teste trigger
16-FEB-11 1:old.TS_ATU_DTR=, :new.TS_ATU_DTR=

16-FEB-11 2:old.FLAG=123, :new.FLAG=321
16-FEB-11 2:old.TAG=teste trigger, :new.TAG=10
16-FEB-11 2:old.TS_ATU_DTR=, :new.TS_ATU_DTR=16-FEB-11 01.46.41.333000000 PM 

Output from 11.2.0.1:

O timestamp obtido eh: [16-FEB-11 06.06.03.348000 PM]

DATA      TEXTO
--------- ------------------------------------------------------------------
16-FEB-11 1:old.FLAG=123, :new.FLAG=321
16-FEB-11 1:old.TAG=teste trigger, :new.TAG=teste trigger
16-FEB-11 1:old.TS_ATU_DTR=, :new.TS_ATU_DTR=
16-FEB-11 2:old.FLAG=123, :new.FLAG=321
16-FEB-11 2:old.TAG=teste trigger, :new.TAG=10
16-FEB-11 2:old.TS_ATU_DTR=, :new.TS_ATU_DTR=16-FEB-11 06.06.03.348000000 PM 

Output from 11.2.0.2:

O timestamp obtido eh: [16-FEB-11 06.02.33.978000 PM]

DATA      TEXTO
--------- -------------------------------------------------------------------------------------------------
16-FEB-11 1:old.FLAG=123, :new.FLAG=321
16-FEB-11 1:old.TAG=teste trigger, :new.TAG=teste trigger
16-FEB-11 1:old.TS_ATU_DTR=16-FEB-11 06.02.33.827000000 PM, :new.TS_ATU_DTR=

16-FEB-11 2:old.FLAG=123, :new.FLAG=321
16-FEB-11 2:old.TAG=teste trigger, :new.TAG=10
16-FEB-11 2:old.TS_ATU_DTR=16-FEB-11 06.02.33.827000000 PM, :new.TS_ATU_DTR=16-FEB-11 06.02.33.978000000 PM  

Notice in the above that the results from Oracle Database 10.2.0.2 show that the trigger code was able to retrieve the existing value of the TS_ATU_DTR (TIMESTAMP datatype) column and write that value to the logging table.  When the test was repeated on Oracle Database 11.1.0.7, we find that the existing (old) value of the TS_ATU_DTR column could not be read, and the replacement (new) value of that column also could not be read unless its value was explictly set in the trigger code.  So, the result of the OP's test script finds that 11.1.0.7 contains a bug related to TIMESTAMP datatypes that does not exist in 10.2.0.2, while the script at the start of this blog article demonstrates a bug related to TIMESTAMP datatypes that exists in 10.2.0.2, but does not exist in 11.1.0.7.  11.2.0.1 returned results that are very similar to 11.1.0.7.  The results for 10.2.0.5 and 11.2.0.2 matched exactly, however the new.TS_ATU_DTR value was not initially set correctly.

Are we able to help the OP with the issue that he faces, or should we just say "Not my problem, go bug someone else."

Let's create another reproducible test case script to see if we are able to isolate the cause of the problem:

SET LINESIZE 140
SET PAGESIZE 1000

DROP TABLE T1;
DROP TABLE T1_LOG;

CREATE TABLE T1 (
  ID NUMBER NOT NULL,
  COL2 NUMBER NOT NULL,
  COL_TS TIMESTAMP(9) WITH TIME ZONE NOT NULL);

CREATE TABLE T1_LOG (
  CHANGE_DATE DATE NOT NULL,
  CHANGE_TEXT VARCHAR2(130) NOT NULL);

CREATE OR REPLACE TRIGGER TRI_T1 BEFORE UPDATE ON T1
REFERENCING OLD AS OLDDATA NEW AS NEWDATA FOR EACH ROW
DECLARE
  OLD_COL_TS TIMESTAMP(9);
  NEW_COL_TS TIMESTAMP(9);
BEGIN
  OLD_COL_TS := :OLDDATA.COL_TS;
  NEW_COL_TS := :NEWDATA.COL_TS;

  INSERT INTO T1_LOG VALUES ( SYSDATE, 'Before:OLDDATA.COL_TS=' || OLD_COL_TS || ', :NEWDATA.COL_TS='||NEW_COL_TS);

  :NEWDATA.COL_TS := CURRENT_TIMESTAMP;
  NEW_COL_TS := :NEWDATA.COL_TS;

  INSERT INTO T1_LOG VALUES ( SYSDATE, 'After:OLDDATA.COL_TS=' || OLD_COL_TS || ', :NEWDATA.COL_TS='||NEW_COL_TS);
END;
/

INSERT INTO
  T1
VALUES (
  1,
  1,
  CURRENT_TIMESTAMP);

UPDATE
  T1
SET
  COL2=COL2+1;

SELECT
  *
FROM
  T1_LOG
ORDER BY
  CHANGE_TEXT DESC;

DECLARE
  OLD_COL_TS TIMESTAMP(9);
BEGIN
  UPDATE
    T1
  SET
    COL2=COL2+1;
END;
/

SELECT
  *
FROM
  T1_LOG
ORDER BY
  CHANGE_TEXT DESC;

DECLARE
  OLD_COL_TS TIMESTAMP(9);
BEGIN
  UPDATE
    T1
  SET
    COL2=COL2+1
  RETURNING
    COL_TS INTO OLD_COL_TS;
END;
/

SELECT
  *
FROM
  T1_LOG
ORDER BY
  CHANGE_TEXT DESC; 

The script performs a lot of actions - don't worry too much about it.  After creating the table and the logging table, we insert a row, and then modify that row three different ways.

Output from 10.2.0.2:

CHANGE_DA CHANGE_TEXT
--------- ------------------------------------------------------------------------------------------------------
16-FEB-11 Before:OLDDATA.COL_TS=16-FEB-11 03.52.21.148000000 PM, :NEWDATA.COL_TS=16-FEB-11 03.52.21.148000000 PM
16-FEB-11 After:OLDDATA.COL_TS=16-FEB-11 03.52.21.148000000 PM, :NEWDATA.COL_TS=16-FEB-11 03.52.21.163000000 PM

CHANGE_DA CHANGE_TEXT
--------- ------------------------------------------------------------------------------------------------------
16-FEB-11 Before:OLDDATA.COL_TS=16-FEB-11 03.52.21.163000000 PM, :NEWDATA.COL_TS=16-FEB-11 03.52.21.163000000 PM
16-FEB-11 Before:OLDDATA.COL_TS=16-FEB-11 03.52.21.148000000 PM, :NEWDATA.COL_TS=16-FEB-11 03.52.21.148000000 PM
16-FEB-11 After:OLDDATA.COL_TS=16-FEB-11 03.52.21.163000000 PM, :NEWDATA.COL_TS=16-FEB-11 03.52.21.179000000 PM
16-FEB-11 After:OLDDATA.COL_TS=16-FEB-11 03.52.21.148000000 PM, :NEWDATA.COL_TS=16-FEB-11 03.52.21.163000000 PM

CHANGE_DA CHANGE_TEXT
--------- ------------------------------------------------------------------------------------------------------
16-FEB-11 Before:OLDDATA.COL_TS=16-FEB-11 03.52.21.179000000 PM, :NEWDATA.COL_TS=16-FEB-11 03.52.21.179000000 PM
16-FEB-11 Before:OLDDATA.COL_TS=16-FEB-11 03.52.21.163000000 PM, :NEWDATA.COL_TS=16-FEB-11 03.52.21.163000000 PM
16-FEB-11 Before:OLDDATA.COL_TS=16-FEB-11 03.52.21.148000000 PM, :NEWDATA.COL_TS=16-FEB-11 03.52.21.148000000 PM
16-FEB-11 After:OLDDATA.COL_TS=16-FEB-11 03.52.21.179000000 PM, :NEWDATA.COL_TS=16-FEB-11 03.52.21.179000000 PM
16-FEB-11 After:OLDDATA.COL_TS=16-FEB-11 03.52.21.163000000 PM, :NEWDATA.COL_TS=16-FEB-11 03.52.21.179000000 PM
16-FEB-11 After:OLDDATA.COL_TS=16-FEB-11 03.52.21.148000000 PM, :NEWDATA.COL_TS=16-FEB-11 03.52.21.163000000 PM 

Output from 10.2.0.5 (with EXEC DBMS_LOCK.SLEEP(1) added between updates):

CHANGE_DA CHANGE_TEXT
--------- ------------------------------------------------------------------------------------------------------
16-FEB-11 Before:OLDDATA.COL_TS=16-FEB-11 06.20.10.819000000 PM, :NEWDATA.COL_TS=16-FEB-11 06.20.10.819000000 PM
16-FEB-11 After:OLDDATA.COL_TS=16-FEB-11 06.20.10.819000000 PM, :NEWDATA.COL_TS=16-FEB-11 06.20.10.819000000 PM

CHANGE_DA CHANGE_TEXT
--------- ------------------------------------------------------------------------------------------------------
16-FEB-11 Before:OLDDATA.COL_TS=16-FEB-11 06.20.10.819000000 PM, :NEWDATA.COL_TS=16-FEB-11 06.20.10.819000000 PM
16-FEB-11 Before:OLDDATA.COL_TS=16-FEB-11 06.20.10.819000000 PM, :NEWDATA.COL_TS=16-FEB-11 06.20.10.819000000 PM
16-FEB-11 After:OLDDATA.COL_TS=16-FEB-11 06.20.10.819000000 PM, :NEWDATA.COL_TS=16-FEB-11 06.20.11.865000000 PM
16-FEB-11 After:OLDDATA.COL_TS=16-FEB-11 06.20.10.819000000 PM, :NEWDATA.COL_TS=16-FEB-11 06.20.10.819000000 PM

CHANGE_DA CHANGE_TEXT
--------- ------------------------------------------------------------------------------------------------------
16-FEB-11 Before:OLDDATA.COL_TS=16-FEB-11 06.20.11.865000000 PM, :NEWDATA.COL_TS=
16-FEB-11 Before:OLDDATA.COL_TS=16-FEB-11 06.20.10.819000000 PM, :NEWDATA.COL_TS=16-FEB-11 06.20.10.819000000 PM
16-FEB-11 Before:OLDDATA.COL_TS=16-FEB-11 06.20.10.819000000 PM, :NEWDATA.COL_TS=16-FEB-11 06.20.10.819000000 PM
16-FEB-11 After:OLDDATA.COL_TS=16-FEB-11 06.20.11.865000000 PM, :NEWDATA.COL_TS=16-FEB-11 06.20.12.910000000 PM
16-FEB-11 After:OLDDATA.COL_TS=16-FEB-11 06.20.10.819000000 PM, :NEWDATA.COL_TS=16-FEB-11 06.20.11.865000000 PM
16-FEB-11 After:OLDDATA.COL_TS=16-FEB-11 06.20.10.819000000 PM, :NEWDATA.COL_TS=16-FEB-11 06.20.10.819000000 PM 

Output from 11.1.0.7:

CHANGE_DA CHANGE_TEXT
--------- ------------------------------------------------------------------------------------------------------
16-FEB-11 Before:OLDDATA.COL_TS=16-FEB-11 03.52.20.032000000 PM, :NEWDATA.COL_TS=16-FEB-11 03.52.20.032000000 PM
16-FEB-11 After:OLDDATA.COL_TS=16-FEB-11 03.52.20.032000000 PM, :NEWDATA.COL_TS=16-FEB-11 03.52.20.032000000 PM

CHANGE_DA CHANGE_TEXT
--------- ------------------------------------------------------------------------------------------------------
16-FEB-11 Before:OLDDATA.COL_TS=16-FEB-11 03.52.20.032000000 PM, :NEWDATA.COL_TS=16-FEB-11 03.52.20.032000000 PM
16-FEB-11 Before:OLDDATA.COL_TS=16-FEB-11 03.52.20.032000000 PM, :NEWDATA.COL_TS=16-FEB-11 03.52.20.032000000 PM
16-FEB-11 After:OLDDATA.COL_TS=16-FEB-11 03.52.20.032000000 PM, :NEWDATA.COL_TS=16-FEB-11 03.52.20.048000000 PM
16-FEB-11 After:OLDDATA.COL_TS=16-FEB-11 03.52.20.032000000 PM, :NEWDATA.COL_TS=16-FEB-11 03.52.20.032000000 PM

CHANGE_DA CHANGE_TEXT
--------- ------------------------------------------------------------------------------------------------------
16-FEB-11 Before:OLDDATA.COL_TS=16-FEB-11 03.52.20.032000000 PM, :NEWDATA.COL_TS=16-FEB-11 03.52.20.032000000 PM
16-FEB-11 Before:OLDDATA.COL_TS=16-FEB-11 03.52.20.032000000 PM, :NEWDATA.COL_TS=16-FEB-11 03.52.20.032000000 PM
16-FEB-11 Before:OLDDATA.COL_TS=, :NEWDATA.COL_TS=
16-FEB-11 After:OLDDATA.COL_TS=16-FEB-11 03.52.20.032000000 PM, :NEWDATA.COL_TS=16-FEB-11 03.52.20.048000000 PM
16-FEB-11 After:OLDDATA.COL_TS=16-FEB-11 03.52.20.032000000 PM, :NEWDATA.COL_TS=16-FEB-11 03.52.20.032000000 PM
16-FEB-11 After:OLDDATA.COL_TS=, :NEWDATA.COL_TS=16-FEB-11 03.52.20.064000000 PM 

Output from 11.2.0.1 (with EXEC DBMS_LOCK.SLEEP(1) added between updates):

CHANGE_DA CHANGE_TEXT
--------- ------------------------------------------------------------------------------------------------------
16-FEB-11 Before:OLDDATA.COL_TS=16-FEB-11 06.09.11.971000000 PM, :NEWDATA.COL_TS=16-FEB-11 06.09.11.971000000 PM
16-FEB-11 After:OLDDATA.COL_TS=16-FEB-11 06.09.11.971000000 PM, :NEWDATA.COL_TS=16-FEB-11 06.09.11.986000000 PM

CHANGE_DA CHANGE_TEXT
--------- ------------------------------------------------------------------------------------------------------
16-FEB-11 Before:OLDDATA.COL_TS=16-FEB-11 06.09.11.986000000 PM, :NEWDATA.COL_TS=16-FEB-11 06.09.11.986000000 PM
16-FEB-11 Before:OLDDATA.COL_TS=16-FEB-11 06.09.11.971000000 PM, :NEWDATA.COL_TS=16-FEB-11 06.09.11.971000000 PM
16-FEB-11 After:OLDDATA.COL_TS=16-FEB-11 06.09.11.986000000 PM, :NEWDATA.COL_TS=16-FEB-11 06.09.13.035000000 PM
16-FEB-11 After:OLDDATA.COL_TS=16-FEB-11 06.09.11.971000000 PM, :NEWDATA.COL_TS=16-FEB-11 06.09.11.986000000 PM

CHANGE_DA CHANGE_TEXT
--------- ------------------------------------------------------------------------------------------------------
16-FEB-11 Before:OLDDATA.COL_TS=16-FEB-11 06.09.11.986000000 PM, :NEWDATA.COL_TS=16-FEB-11 06.09.11.986000000 PM
16-FEB-11 Before:OLDDATA.COL_TS=16-FEB-11 06.09.11.971000000 PM, :NEWDATA.COL_TS=16-FEB-11 06.09.11.971000000 PM
16-FEB-11 Before:OLDDATA.COL_TS=, :NEWDATA.COL_TS=
16-FEB-11 After:OLDDATA.COL_TS=16-FEB-11 06.09.11.986000000 PM, :NEWDATA.COL_TS=16-FEB-11 06.09.13.035000000 PM
16-FEB-11 After:OLDDATA.COL_TS=16-FEB-11 06.09.11.971000000 PM, :NEWDATA.COL_TS=16-FEB-11 06.09.11.986000000 PM
16-FEB-11 After:OLDDATA.COL_TS=, :NEWDATA.COL_TS=16-FEB-11 06.09.14.089000000 PM

Output from 11.2.0.2 (with EXEC DBMS_LOCK.SLEEP(1) added between updates):

CHANGE_DA CHANGE_TEXT
--------- ----------------------------------------------------------------------------
16-FEB-11 Before:OLDDATA.COL_TS=16-FEB-11 17:50:19, :NEWDATA.COL_TS=16-FEB-11 17:50:19
16-FEB-11 After:OLDDATA.COL_TS=16-FEB-11 17:50:19, :NEWDATA.COL_TS=16-FEB-11 17:50:19

CHANGE_DA CHANGE_TEXT
--------- ----------------------------------------------------------------------------
16-FEB-11 Before:OLDDATA.COL_TS=16-FEB-11 17:50:19, :NEWDATA.COL_TS=16-FEB-11 17:50:19
16-FEB-11 Before:OLDDATA.COL_TS=16-FEB-11 17:50:19, :NEWDATA.COL_TS=16-FEB-11 17:50:19
16-FEB-11 After:OLDDATA.COL_TS=16-FEB-11 17:50:19, :NEWDATA.COL_TS=16-FEB-11 17:50:20
16-FEB-11 After:OLDDATA.COL_TS=16-FEB-11 17:50:19, :NEWDATA.COL_TS=16-FEB-11 17:50:19

CHANGE_DA CHANGE_TEXT
--------- ----------------------------------------------------------------------------
16-FEB-11 Before:OLDDATA.COL_TS=16-FEB-11 17:50:20, :NEWDATA.COL_TS=
16-FEB-11 Before:OLDDATA.COL_TS=16-FEB-11 17:50:19, :NEWDATA.COL_TS=16-FEB-11 17:50:19
16-FEB-11 Before:OLDDATA.COL_TS=16-FEB-11 17:50:19, :NEWDATA.COL_TS=16-FEB-11 17:50:19
16-FEB-11 After:OLDDATA.COL_TS=16-FEB-11 17:50:20, :NEWDATA.COL_TS=16-FEB-11 17:50:21
16-FEB-11 After:OLDDATA.COL_TS=16-FEB-11 17:50:19, :NEWDATA.COL_TS=16-FEB-11 17:50:20
16-FEB-11 After:OLDDATA.COL_TS=16-FEB-11 17:50:19, :NEWDATA.COL_TS=16-FEB-11 17:50:19 

Notice in the above that 10.2.0.2 produced three nice sets of data, with TIMESTAMP values shown on all rows.  The final set of output (in blue) from 11.1.0.7 shows NULL initial values for the TIMESTAMP columns.  The NULL problem only happens in the trigger code when the RETURNING clause is used in the UPDATE statement.  Neat, I think that we found a bug, and we are able to work around that bug simply by reworking the code to not use the RETURNING clause.  The same solution will work on 10.2.0.5, 11.2.0.1, and 11.2.0.2.

----

Other TIMESTAMP resources from Metalink (MOS):

  • Doc ID 340512.1 - "Timestamps & time zones - Frequently Asked Questions"
  • Doc ID 5649579.8, Bug 5649579 - "CAST 'as date' of TIMESTAMP rounds in SQL, truncates in PLSQL"
  • Doc ID 780809.1 - "TO_TIMESTAMP is Returning Different Results on Oracle 9i Compare to Oracle 10g"







Follow

Get every new post delivered to your Inbox.

Join 139 other followers