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:
- 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 valueSELECT * FROM table WHERE modified_timestamp >= to_date("${OFFSET}", "YYYY/MM/DD HH24:Mi:SS") ORDER BY modified_timestamp
-
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"