Apache Hop fails to handle Oracle TIMESTAMP comparison when value is sourced from database column #6279
Description
Same SQL works in DBeaver
Same SQL works in Hop when using SYSTIMESTAMP
Fails only when timestamp comes from ETL_TABLE_CONTROL
Error shows up during prepare / describe phase, not execution
This tells us Oracle is NOT the problem.
The problem is how the ETL tool injects / binds the timestamp value.
When executing an Oracle SQL query inside Apache Hop, the query fails only when a TIMESTAMP value is sourced from a database column.
The same query:
Works correctly in DBeaver
Works correctly in Hop when SYSTIMESTAMP is used
Fails only when LAST_RUN_TS is fetched from ETL_TABLE_CONTROL
Actual System
Hop version-2.16.0
Oracle DB version-Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Oracle JDBC driver used-oracle.jdbc.OracleDriver
CREATE TABLE ETL_TABLE_CONTROL (
TABLE_NAME VARCHAR2(128) NOT NULL,
LAST_RUN_TS TIMESTAMP(9),
STATUS VARCHAR2(20),
LAST_RUN_ID NUMBER,
ROW_COUNT NUMBER,
ERROR_CODE VARCHAR2(100),
ERROR_MESSAGE VARCHAR2(4000),
FROM_TS TIMESTAMP(6),
TO_TS TIMESTAMP(6),
CONSTRAINT PK_ETL_TABLE_CONTROL
PRIMARY KEY (TABLE_NAME)
);
INSERT INTO ETL_TABLE_CONTROL (
TABLE_NAME,
LAST_RUN_TS,
STATUS,
LAST_RUN_ID,
ROW_COUNT,
FROM_TS,
TO_TS
)
VALUES (
'XXSIFY_CPQ_ORDER_DETAILS_UDM',
TO_TIMESTAMP(
'31-12-25 03:13:55.000000000 PM',
'DD-MM-RR HH:MI:SS.FF9 AM'
),
'INIT',
92582,
0,
TIMESTAMP '2025-12-28 11:37:34',
TIMESTAMP '2025-12-30 11:37:34'
);