Oracle Timestamp parsing error in JDBC Query pipelines: ORA-01843

Problem

When using a timestamp as an offset in a JDBC Query Consumer origin for an Oracle DB connection, i.e. 

SELECT * FROM table WHERE modified_timestamp >= '${OFFSET}' ORDER BY modified_timestamp

The Oracle JDBC driver is throwing some date string parsing error, e.g.: java.sql.SQLDataException: ORA-01843

Background

SDC stores pipeline origin offsets as a Java data with the format: YYYY/MM/DD HH24:Mi:SS, i.e:

"2023-04-25 14:34:42"

If the Oracle JDBC driver is throwing ORA-01843, this is because the date format used for the offset is not the format it is expecting.

If the Oracle session variable: NLS_TIMESTAMP is not equal to YYYY/MM/DD HH24:Mi:SS , the offset date string in the SQL query will not be automatically parsed into a Date type.

Solution

To correct this parsing error, you can do either of the following options:

1. Simply re-write your SQL query to include a to_date to explicitly convert the SDC timestamp offset to Date type, regardless of the Oracle session’s default NLS_TIMESTAMP value

   SELECT * FROM table WHERE modified_timestamp >= to_date("${OFFSET}", "YYYY/MM/DD HH24:Mi:SS") ORDER BY modified_timestamp

2. Set the NLS_TIMESTAMP session variable to YYYY/MM/DD HH24:Mi:SS  during the SQL Init Query (JDBC Query Origin → Advanced → Init Query).

   ALTER SESSION NLS_TIMESTAMP = "YYYY/MM/DD HH24:Mi:SS"