When Using The Stmt.setTimestamp Method, The Timestamp Precision Is NULL If The Timestamp Has Zero Millis, Micros, And Nanos (Doc ID 3035648.1)

Last updated on JULY 17, 2024

Applies to:

Symptoms

It was detected that the Timestamp precision is NULL when timestamp has zero millis, micros, and nanos

The timestamp that has all zeros in millis, micros and nanos will be viewed by Oracle's child cursor as NULL precision on the timestamp value in the prepared statement.
The preparedStatement.setTimestamp method is used in the test program as shown below.

This causes additional child cursors due to the bind mismatch.

It was created a simple java test program, using two different Timestamps:

Timestamp ts_no_precision = Timestamp.valueOf("2024-05-24 09:01:15.000000000");
Timestamp ts_with_precision = Timestamp.valueOf("2024-05-24 09:01:15.000000001");

System out showed:
Timestamp no precision: 2024-05-24 09:01:15.0
Timestamp with precision: 2024-05-24 09:01:15.000000001

The cursor for "no precision" datatype = timestamp precision NULL
The cursor for "with precision" datatype = timestamp precision 9

This behavior can cause many additional child cursors due to the difference in the precision.

Program to test this scenario:

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.