Oracle Table Input with variables (${LAST_RUN_TS}, ${LAST_RUN_ID}) failing in Apache Hop #6250

sivaguru-rajasekaran · 2025-12-22T12:40:13Z

sivaguru-rajasekaran
Dec 22, 2025

Hi Hop Community,

I’m trying to implement incremental data migration from Oracle to MySQL using Apache Hop.

Goal:

Read from Oracle using LAST_RUN_TS (timestamp) and LAST_RUN_ID (number)
Variables are set in a workflow using “Set Variables”
Pipeline uses Oracle Table Input → MySQL Table Output

Oracle query (in Table Input):

SELECT *
FROM APPS.XXSIFY_CPQ_ORDER_DETAILS_UDM
WHERE
(
CREATED_ON > TO_TIMESTAMP('${LAST_RUN_TS}', 'YYYY-MM-DD HH24:MI:SS.FF')
)
OR
(
CREATED_ON = TO_TIMESTAMP('${LAST_RUN_TS}', 'YYYY-MM-DD HH24:MI:SS.FF')
AND ID > ${LAST_RUN_ID}
)
ORDER BY CREATED_ON, ID

What works:

Hardcoded values in the query work correctly
Workflow successfully sets variables:
LAST_RUN_TS = 1970-01-01 00:00:00.000000
LAST_RUN_ID = 0

Problem:

When using variables in the Table Input, the pipeline either:
- Interprets them as literal strings
- Or fails with conversion / binding errors
Oracle Table Input does not expose a “Parameters” tab, so I cannot bind variables explicitly

Errors observed:

ORA-01008: not all variables bound
Couldn't parse Timestamp field [LAST_RUN_TS]
Variables sometimes remain unresolved (${LAST_RUN_TS})

Environment:

Apache Hop version:
Database: Oracle
Execution engine: Local

Questions:

What is the recommended pattern in Hop for parameterized Oracle Table Input queries?
Is variable substitution in Table Input officially supported for timestamps?
Should this be handled via workflow → SQL generation → pipeline instead?

Any guidance or best practices would be appreciated.

Thanks!

bamaer · 2025-12-22T13:05:17Z

bamaer
Dec 22, 2025
Collaborator

Your parameterized query and the screenshot bot look correct, but there could be subtle issues that we're missing from the limited information here.

When using variables in the Table Input, the pipeline either Interprets them as literal strings

That's because that is what happens. The variables in your query are parsed before the query is sent to the query as a sql string. The variable doesn't prevent any SQL injection or similar. Try hovering over your variables to see what their exact values are, write your parsed SQL to the logs with a Write To Leg etc to troubleshoot and to make sure the SQL is exactly what you expect it to be.

An alternative approach could be to pass data from fields with the "insert data from transform" option, but you'll need to prepare your input stream correctly in that case. Check the docs and the examples in the samples project.

What you're trying to do is a common use case and is widely used, it shouldn't be too much of a problem to get it to work.

0 replies

sivaguru-rajasekaran · 2025-12-23T06:23:59Z

sivaguru-rajasekaran
Dec 23, 2025
Author

Thanks for the detailed explanation — that helped clarify what’s really happening under the hood with variable substitution.

Based on your suggestion, I moved the variable handling fully into a JavaScript step, followed by Generate Rows, and then passed the resolved values downstream instead of relying on variables inside the Table Input. With this approach, the SQL is now fully concrete before execution, and logging the parsed SQL confirms it’s exactly what I expect.

When I execute the steps individually, everything works fine:

Variables are substituted correctly in JavaScript
The generated rows contain the expected values
Table Output inserts data without issues

However, when I execute the full transformation, the Table Output step intermittently fails or behaves inconsistently, even though the upstream steps still show correct data in preview.

At this point, I suspect one of the following:

Row metadata mismatch between Generate Rows and Table Output
Commit / batch size behavior when running end-to-end
Execution order or row stream timing differences vs step-level execution

I’m currently validating field types, lengths, and commit settings on the Table Output, but if there are any known gotchas around:

Generate Rows → Table Output pipelines
Or differences between preview vs full-run execution

I’d appreciate a nudge in the right direction. This feels close — just trying to pin down that last edge case.

Thanks again...

0 replies

sivaguru-rajasekaran · 2025-12-23T06:24:29Z

sivaguru-rajasekaran
Dec 23, 2025
Author

2025/12/23 11:20:01 - Hop - Starting workflow
2025/12/23 11:20:01 - oracle_to_mysql_workflow - Start of workflow execution
2025/12/23 11:20:01 - oracle_to_mysql_workflow - Starting action [Workflow]
2025/12/23 11:20:01 - oracle_to_mysql_workflow - Finished action [Workflow] (result=[true])
2025/12/23 11:20:01 - oracle_to_mysql_workflow - Starting action [Get_Table_MetaData]
2025/12/23 11:20:01 - Get_Table_MetaData - Using run configuration [local]
2025/12/23 11:20:01 - get_table_metadata - Executing this pipeline using the Local Pipeline Engine with run configuration 'local'
2025/12/23 11:20:01 - get_table_metadata - Execution started for pipeline [get_table_metadata]
2025/12/23 11:20:02 - Table MetaData.0 - Finished reading query, closing connection.
2025/12/23 11:20:02 - Set variables.0 - Setting project variables...
2025/12/23 11:20:02 - Set variables.0 - Set variable SOURCE_SCHEMA to value [APPS]
2025/12/23 11:20:02 - Set variables.0 - Set variable SOURCE_TABLE to value [XXSIFY_CPQ_ORDER_DETAILS_UDM]
2025/12/23 11:20:02 - Set variables.0 - Set variable SOURCE_DATE_COL to value [CREATED_ON]
2025/12/23 11:20:02 - Set variables.0 - Set variable TARGET_SCHEMA to value [sify]
2025/12/23 11:20:02 - Set variables.0 - Set variable TARGET_TABLE to value [xxsify_cpq_order_details_udm]
2025/12/23 11:20:02 - Set variables.0 - Finished after 1 rows.
2025/12/23 11:20:02 - Set variables.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
2025/12/23 11:20:02 - Table MetaData.0 - Finished processing (I=1, O=0, R=0, W=1, U=0, E=0)
2025/12/23 11:20:02 - get_table_metadata - Pipeline duration : 0.371 seconds [ 0.371" ]
2025/12/23 11:20:02 - get_table_metadata - Execution finished on a local pipeline engine with run configuration 'local'
2025/12/23 11:20:02 - oracle_to_mysql_workflow - Finished action [Get_Table_MetaData] (result=[true])
2025/12/23 11:20:02 - oracle_to_mysql_workflow - Starting action [get_last_run_ts]
2025/12/23 11:20:02 - get_last_run_ts - Using run configuration [local]
2025/12/23 11:20:02 - get_last_run_ts - Executing this pipeline using the Local Pipeline Engine with run configuration 'local'
2025/12/23 11:20:02 - get_last_run_ts - Execution started for pipeline [get_last_run_ts]
2025/12/23 11:20:02 - Table input.0 - Finished reading query, closing connection.
2025/12/23 11:20:02 - Set variables.0 - We didn't receive a row while setting the default values.
2025/12/23 11:20:02 - Set variables.0 - Set variable LAST_RUN_TS to value [1970-01-01 00:00:00.000000]
2025/12/23 11:20:02 - Set variables.0 - Set variable LAST_RUN_ID to value [0]
2025/12/23 11:20:02 - Set variables.0 - Finished after 0 rows.
2025/12/23 11:20:02 - get_last_run_ts - Pipeline duration : 0.383 seconds [ 0.383" ]
2025/12/23 11:20:02 - get_last_run_ts - Execution finished on a local pipeline engine with run configuration 'local'
2025/12/23 11:20:02 - oracle_to_mysql_workflow - Finished action [get_last_run_ts] (result=[true])
2025/12/23 11:20:02 - oracle_to_mysql_workflow - Starting action [oracle_to_mysql_datamigration]
2025/12/23 11:20:02 - oracle_to_mysql_datamigration - Using run configuration [local]
2025/12/23 11:20:02 - oracle_to_mysql_generic - Executing this pipeline using the Local Pipeline Engine with run configuration 'local'
2025/12/23 11:20:02 - oracle_to_mysql_generic - Execution started for pipeline [oracle_to_mysql_generic]
2025/12/23 11:20:02 - MYSQL_TABLE_OUTPUT .0 - Connected to database [Mysql_local] (commit=2000000)
2025/12/23 11:20:03 - Generate rows.0 - Finished processing (I=0, O=0, R=0, W=10, U=0, E=0)
2025/12/23 11:20:03 - JavaScript.0 - Optimization level set to 9.
2025/12/23 11:20:03 - Write to log.0 -
2025/12/23 11:20:03 - Write to log.0 - ------------> Linenr 1------------------------------
2025/12/23 11:20:03 - Write to log.0 - X = null
2025/12/23 11:20:03 - Write to log.0 - SQL_QUERY = SELECT * FROM APPS.XXSIFY_CPQ_ORDER_DETAILS_UDM WHERE ( CREATED_ON > TO_TIMESTAMP('1970-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF') ) OR ( CREATED_ON = TO_TIMESTAMP('1970-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF') AND ID > 0) ORDER BY CREATED_ON, ID
2025/12/23 11:20:03 - Write to log.0 -
2025/12/23 11:20:03 - Write to log.0 - ====================
2025/12/23 11:20:03 - Write to log.0 -
2025/12/23 11:20:03 - Write to log.0 - ------------> Linenr 2------------------------------
2025/12/23 11:20:03 - Write to log.0 - X = null
2025/12/23 11:20:03 - Write to log.0 - SQL_QUERY = SELECT * FROM APPS.XXSIFY_CPQ_ORDER_DETAILS_UDM WHERE ( CREATED_ON > TO_TIMESTAMP('1970-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF') ) OR ( CREATED_ON = TO_TIMESTAMP('1970-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF') AND ID > 0) ORDER BY CREATED_ON, ID
2025/12/23 11:20:03 - Write to log.0 -
2025/12/23 11:20:03 - Write to log.0 - ====================
2025/12/23 11:20:03 - JavaScript.0 - Finished processing (I=0, O=0, R=10, W=10, U=0, E=0)
2025/12/23 11:20:03 - Write to log.0 -
2025/12/23 11:20:03 - Write to log.0 - ------------> Linenr 3------------------------------
2025/12/23 11:20:03 - Write to log.0 - X = null
2025/12/23 11:20:03 - Write to log.0 - SQL_QUERY = SELECT * FROM APPS.XXSIFY_CPQ_ORDER_DETAILS_UDM WHERE ( CREATED_ON > TO_TIMESTAMP('1970-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF') ) OR ( CREATED_ON = TO_TIMESTAMP('1970-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF') AND ID > 0) ORDER BY CREATED_ON, ID
2025/12/23 11:20:03 - Write to log.0 -
2025/12/23 11:20:03 - Write to log.0 - ====================
2025/12/23 11:20:03 - Write to log.0 -
2025/12/23 11:20:03 - Write to log.0 - ------------> Linenr 4------------------------------
2025/12/23 11:20:03 - Write to log.0 - X = null
2025/12/23 11:20:03 - Write to log.0 - SQL_QUERY = SELECT * FROM APPS.XXSIFY_CPQ_ORDER_DETAILS_UDM WHERE ( CREATED_ON > TO_TIMESTAMP('1970-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF') ) OR ( CREATED_ON = TO_TIMESTAMP('1970-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF') AND ID > 0) ORDER BY CREATED_ON, ID
2025/12/23 11:20:03 - Write to log.0 -
2025/12/23 11:20:03 - Write to log.0 - ====================
2025/12/23 11:20:03 - Write to log.0 -
2025/12/23 11:20:03 - Write to log.0 - ------------> Linenr 5------------------------------
2025/12/23 11:20:03 - Write to log.0 - X = null
2025/12/23 11:20:03 - Write to log.0 - SQL_QUERY = SELECT * FROM APPS.XXSIFY_CPQ_ORDER_DETAILS_UDM WHERE ( CREATED_ON > TO_TIMESTAMP('1970-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF') ) OR ( CREATED_ON = TO_TIMESTAMP('1970-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF') AND ID > 0) ORDER BY CREATED_ON, ID
2025/12/23 11:20:03 - Write to log.0 -
2025/12/23 11:20:03 - Write to log.0 - ====================
2025/12/23 11:20:03 - Write to log.0 -
2025/12/23 11:20:03 - Write to log.0 - ------------> Linenr 6------------------------------
2025/12/23 11:20:03 - Write to log.0 - X = null
2025/12/23 11:20:03 - Write to log.0 - SQL_QUERY = SELECT * FROM APPS.XXSIFY_CPQ_ORDER_DETAILS_UDM WHERE ( CREATED_ON > TO_TIMESTAMP('1970-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF') ) OR ( CREATED_ON = TO_TIMESTAMP('1970-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF') AND ID > 0) ORDER BY CREATED_ON, ID
2025/12/23 11:20:03 - Write to log.0 -
2025/12/23 11:20:03 - Write to log.0 - ====================
2025/12/23 11:20:03 - Write to log.0 -
2025/12/23 11:20:03 - Write to log.0 - ------------> Linenr 7------------------------------
2025/12/23 11:20:03 - Write to log.0 - X = null
2025/12/23 11:20:03 - Write to log.0 - SQL_QUERY = SELECT * FROM APPS.XXSIFY_CPQ_ORDER_DETAILS_UDM WHERE ( CREATED_ON > TO_TIMESTAMP('1970-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF') ) OR ( CREATED_ON = TO_TIMESTAMP('1970-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF') AND ID > 0) ORDER BY CREATED_ON, ID
2025/12/23 11:20:03 - Write to log.0 -
2025/12/23 11:20:03 - Write to log.0 - ====================
2025/12/23 11:20:03 - Write to log.0 -
2025/12/23 11:20:03 - Write to log.0 - ------------> Linenr 8------------------------------
2025/12/23 11:20:03 - Write to log.0 - X = null
2025/12/23 11:20:03 - Write to log.0 - SQL_QUERY = SELECT * FROM APPS.XXSIFY_CPQ_ORDER_DETAILS_UDM WHERE ( CREATED_ON > TO_TIMESTAMP('1970-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF') ) OR ( CREATED_ON = TO_TIMESTAMP('1970-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF') AND ID > 0) ORDER BY CREATED_ON, ID
2025/12/23 11:20:03 - Write to log.0 -
2025/12/23 11:20:03 - Write to log.0 - ====================
2025/12/23 11:20:03 - Write to log.0 -
2025/12/23 11:20:03 - Write to log.0 - ------------> Linenr 9------------------------------
2025/12/23 11:20:03 - Write to log.0 - X = null
2025/12/23 11:20:03 - Write to log.0 - SQL_QUERY = SELECT * FROM APPS.XXSIFY_CPQ_ORDER_DETAILS_UDM WHERE ( CREATED_ON > TO_TIMESTAMP('1970-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF') ) OR ( CREATED_ON = TO_TIMESTAMP('1970-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF') AND ID > 0) ORDER BY CREATED_ON, ID
2025/12/23 11:20:03 - Write to log.0 -
2025/12/23 11:20:03 - Write to log.0 - ====================
2025/12/23 11:20:03 - Write to log.0 -
2025/12/23 11:20:03 - Write to log.0 - ------------> Linenr 10------------------------------
2025/12/23 11:20:03 - Write to log.0 - X = null
2025/12/23 11:20:03 - Write to log.0 - SQL_QUERY = SELECT * FROM APPS.XXSIFY_CPQ_ORDER_DETAILS_UDM WHERE ( CREATED_ON > TO_TIMESTAMP('1970-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF') ) OR ( CREATED_ON = TO_TIMESTAMP('1970-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF') AND ID > 0) ORDER BY CREATED_ON, ID
2025/12/23 11:20:03 - Write to log.0 -
2025/12/23 11:20:03 - Write to log.0 - ====================
2025/12/23 11:20:03 - Write to log.0 - Finished processing (I=0, O=0, R=10, W=10, U=0, E=0)
2025/12/23 11:20:03 - ORACLE_TABLE_INPUT.0 - Finished reading query, closing connection.
2025/12/23 11:20:03 - MYSQL_TABLE_OUTPUT .0 - ERROR: Unexpected batch update error committing the database connection.
2025/12/23 11:20:03 - MYSQL_TABLE_OUTPUT .0 - ERROR: org.apache.hop.core.exception.HopDatabaseBatchException:
2025/12/23 11:20:03 - MYSQL_TABLE_OUTPUT .0 - Error updating batch
2025/12/23 11:20:03 - MYSQL_TABLE_OUTPUT .0 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1) VALUES ( 1)' at line 1
2025/12/23 11:20:03 - MYSQL_TABLE_OUTPUT .0 -
2025/12/23 11:20:03 - MYSQL_TABLE_OUTPUT .0 - at org.apache.hop.core.database.Database.createHopDatabaseBatchException(Database.java:1302)
2025/12/23 11:20:03 - MYSQL_TABLE_OUTPUT .0 - at org.apache.hop.core.database.Database.emptyAndCommit(Database.java:1290)
2025/12/23 11:20:03 - MYSQL_TABLE_OUTPUT .0 - at org.apache.hop.pipeline.transforms.tableoutput.TableOutput.emptyAndCommitBatchBuffers(TableOutput.java:1101)
2025/12/23 11:20:03 - MYSQL_TABLE_OUTPUT .0 - at org.apache.hop.pipeline.transforms.tableoutput.TableOutput.dispose(TableOutput.java:1057)
2025/12/23 11:20:03 - MYSQL_TABLE_OUTPUT .0 - at org.apache.hop.pipeline.transform.RunThread.run(RunThread.java:96)
2025/12/23 11:20:03 - MYSQL_TABLE_OUTPUT .0 - at java.base/java.lang.Thread.run(Thread.java:840)
2025/12/23 11:20:03 - MYSQL_TABLE_OUTPUT .0 - Caused by: java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1) VALUES ( 1)' at line 1
2025/12/23 11:20:03 - MYSQL_TABLE_OUTPUT .0 - at com.mysql.cj.jdbc.exceptions.SQLError.createBatchUpdateException(SQLError.java:214)
2025/12/23 11:20:03 - MYSQL_TABLE_OUTPUT .0 - at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchSerially(ClientPreparedStatement.java:883)
2025/12/23 11:20:03 - MYSQL_TABLE_OUTPUT .0 - at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchInternal(ClientPreparedStatement.java:465)
2025/12/23 11:20:03 - MYSQL_TABLE_OUTPUT .0 - at com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:858)
2025/12/23 11:20:03 - MYSQL_TABLE_OUTPUT .0 - at org.apache.hop.core.database.Database.emptyAndCommit(Database.java:1277)
2025/12/23 11:20:03 - MYSQL_TABLE_OUTPUT .0 - ... 4 more
2025/12/23 11:20:03 - MYSQL_TABLE_OUTPUT .0 - Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1) VALUES ( 1)' at line 1
2025/12/23 11:20:03 - MYSQL_TABLE_OUTPUT .0 - at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:112)
2025/12/23 11:20:03 - MYSQL_TABLE_OUTPUT .0 - at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:114)
2025/12/23 11:20:03 - MYSQL_TABLE_OUTPUT .0 - at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:988)
2025/12/23 11:20:03 - MYSQL_TABLE_OUTPUT .0 - at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1166)
2025/12/23 11:20:03 - MYSQL_TABLE_OUTPUT .0 - at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchSerially(ClientPreparedStatement.java:862)
2025/12/23 11:20:03 - MYSQL_TABLE_OUTPUT .0 - ... 7 more
2025/12/23 11:20:03 - MYSQL_TABLE_OUTPUT .0 - Finished processing (I=0, O=0, R=1, W=0, U=0, E=1)
2025/12/23 11:20:03 - oracle_to_mysql_generic - Pipeline detected one or more transforms with errors.
2025/12/23 11:20:03 - oracle_to_mysql_generic - Pipeline is killing the other transforms!
2025/12/23 11:20:03 - ORACLE_TABLE_INPUT.0 - Finished processing (I=1, O=0, R=0, W=1, U=0, E=0)
2025/12/23 11:20:03 - oracle_to_mysql_generic - Pipeline duration : 0.38 seconds [ 0.380" ]
2025/12/23 11:20:03 - oracle_to_mysql_generic - Execution finished on a local pipeline engine with run configuration 'local'
2025/12/23 11:20:03 - oracle_to_mysql_workflow - Finished action [oracle_to_mysql_datamigration] (result=[false])
2025/12/23 11:20:03 - oracle_to_mysql_workflow - Starting action [update_last_run_ts]
2025/12/23 11:20:03 - update_last_run_ts - Using run configuration [local]
2025/12/23 11:20:03 - LAST_RUN_TS - Executing this pipeline using the Local Pipeline Engine with run configuration 'local'
2025/12/23 11:20:03 - LAST_RUN_TS - Execution started for pipeline [LAST_RUN_TS]
2025/12/23 11:20:03 - Execute SQL script.0 - Finished reading query, closing connection.
2025/12/23 11:20:03 - LAST_RUN_TS - Pipeline duration : 0.288 seconds [ 0.288" ]
2025/12/23 11:20:03 - LAST_RUN_TS - Execution finished on a local pipeline engine with run configuration 'local'
2025/12/23 11:20:03 - oracle_to_mysql_workflow - Finished action [update_last_run_ts] (result=[true])
2025/12/23 11:20:03 - oracle_to_mysql_workflow - Workflow execution finished
2025/12/23 11:20:03 - Hop - Workflow execution has ended
2025/12/23 11:20:03 - oracle_to_mysql_workflow - Workflow duration : 1.647 seconds [ 1.647" ]

0 replies

hansva · 2025-12-23T06:28:25Z

hansva
Dec 23, 2025
Collaborator

This seems like a different error then the one you were reporting? Are you using a table output to insert in the MySQL table? If so we will need a reproduction path of that error. The table definition (DDL) of the table and an example row that is causing that error.

0 replies

sivaguru-rajasekaran · 2025-12-23T07:26:00Z

sivaguru-rajasekaran
Dec 23, 2025
Author

Table	Create Table
xxsify_cpq_order_details_udm	CREATE TABLE `xxsify_cpq_order_details_udm` (

SCQH_ID bigint NOT NULL,
BATCH_ID bigint DEFAULT NULL,
INVENTORY_ITEM_ID bigint DEFAULT NULL,
ITEM varchar(240) DEFAULT NULL,
ITEM_DESCRIPTION varchar(240) DEFAULT NULL,
QUANTITY decimal(18,6) DEFAULT NULL,
LIST_PRICE decimal(18,6) DEFAULT NULL,
DISCOUNT decimal(18,6) DEFAULT NULL,
NET_PRICE decimal(18,6) DEFAULT NULL,
NET_AMOUNT decimal(18,6) DEFAULT NULL,
ITEM_TYPE varchar(250) DEFAULT NULL,
ORDER_TYPE varchar(240) DEFAULT NULL,
ORDER_TYPE_ID bigint DEFAULT NULL,
LINE_TYPE varchar(240) DEFAULT NULL,
LINE_TYPE_ID bigint DEFAULT NULL,
CONTRACT_PERIOD varchar(240) DEFAULT NULL,
BU varchar(100) DEFAULT NULL,
BUSINESS_LINE varchar(240) DEFAULT NULL,
ORDER_NUMBER bigint DEFAULT NULL,
LINE_ID bigint DEFAULT NULL,
STATUS char(1) DEFAULT NULL,
ORDER_CREATION_DATE datetime DEFAULT NULL,
CREATION_DATE datetime NOT NULL,
CREATED_BY varchar(240) NOT NULL,
LAST_UPDATE_DATE datetime NOT NULL,
LAST_UPDATED_BY bigint DEFAULT NULL,
LAST_UPDATE_LOGIN bigint DEFAULT NULL,
BILL_GST_NO varchar(100) DEFAULT NULL,
SHIP_GST_NO varchar(100) DEFAULT NULL,
ORG_ID bigint DEFAULT NULL,
SCOD_ID bigint DEFAULT NULL,
SXOD_ID bigint DEFAULT NULL,
ADDRESS_REF_ID varchar(30) DEFAULT NULL,
GST_NO varchar(30) DEFAULT NULL,
CREATED_ON timestamp(6) NULL DEFAULT NULL,
ID bigint NOT NULL AUTO_INCREMENT,
PRIMARY KEY (ID),
UNIQUE KEY uk_xxsify_id (ID)
) ENGINE=InnoDB AUTO_INCREMENT=700013 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

0 replies

sivaguru-rajasekaran · 2025-12-23T07:29:00Z

sivaguru-rajasekaran
Dec 23, 2025
Author

Without the row generation and JavaScript substitution logic, the simple table input/output works perfectly fine. However, once those parts are introduced, dummy records start getting inserted into the movements.

0 replies

hansva · 2025-12-23T07:42:46Z

hansva
Dec 23, 2025
Collaborator

Table definition alone won't help us, we will need a pipeline with a row (datagrid) and a table output that reproduces that error.
We don't know what kind of data is causing that error on the table output.
For some reason, it's generating an incorrect insert statement.

0 replies

bamaer · 2025-12-23T07:50:49Z

bamaer
Dec 23, 2025
Collaborator

Also, you left the Generate Rows to 10 (the default), you almost certainly need a single row. It's not 100% clear from what you shared, but at least you should only get like the one below only once, that could make it easier to debug.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1) VALUES ( 1)' at line 1

0 replies

bamaer · 2025-12-23T08:02:14Z

bamaer
Dec 23, 2025
Collaborator

converting this to a discussion, since this doesn't seem to be a bug or a feature request.

0 replies

dmainou · 2025-12-23T23:12:45Z

dmainou
Dec 23, 2025

At the risk of pointing the obvious.... why in the world are you using the JS step to do that?
Just place your query on the table input and substitute the variables directly in there.

If they are not landing exactly as expected then land the variables as rows with a get variables, bash them into the correct format with a calc step, filter with a select to keep what you need in the exact order you need it and feed it into the table input as ? substitutions.

Also it's likely working intermittently because, to enable the streaming of data, in pipelines everything happens at the same time. You need to be aware of that and design for it.

0 replies

sivaguru-rajasekaran · 2025-12-25T13:45:52Z

sivaguru-rajasekaran
Dec 25, 2025
Author

Good question — this is actually the key distinction.

What you’re running into isn’t a limitation of Table Input, it’s when the values are being resolved.

LAST_RUN_TS and LAST_RUN_ID are being treated as compile-time variables, not row-level runtime values, which is why they don’t resolve when embedded directly in the query. JavaScript “works” only because it forces evaluation earlier — but that’s also why it’s fragile.

Instead of JS substitution, the reliable approach is to pass these as data, not variables.

Suggested pattern:

Use Get Variables (or a Table Input / Row Generator) to land
LAST_RUN_TS and LAST_RUN_ID as fields in a row

Use Calculator / Select Values to:

enforce correct data types

format timestamp explicitly (no implicit casting)

In Table Input, enable Replace variables in script? = No

Use ? placeholders in the SQL and let the incoming row bind them at runtime

Example:
SELECT ...
FROM ...
WHERE run_ts > ?
AND run_id > ?

As long as the incoming stream has:

column 1 → timestamp

column 2 → number

the engine will bind them correctly at execution time.

The intermittent behavior you’re seeing with JS is due to pipeline streaming — variable resolution and query execution can happen out of order unless everything is serialized into the same row flow.

Bottom line:
JS isn’t wrong, but it’s compensating for a timing issue. Treat the values as row data, not variables, and the problem goes away.

Happy to walk through a concrete example if needed.

1 reply

dmainou Dec 26, 2025

I think that that is exactly what i suggested.

sivaguru-rajasekaran · 2025-12-26T06:43:00Z

sivaguru-rajasekaran
Dec 26, 2025
Author

Hi @dmainou,

As discussed, I’ve been spending additional time exploring the suggested approach and evaluating alternate options as well. However, I’m still not getting a clear resolution.

Since management is keen to close this out at the earliest, I believe a quick discussion would help align on the right direction.

If possible, can we connect for 30 minutes to walk through this together? I’m available within the next 30 minutes as well, if that works for you.

Please let me know.

Thanks,
Sivaguru

1 reply

bamaer Dec 26, 2025
Collaborator

You seem to be adding new complexity with every iteration in this discussion.
What you're trying to achieve seems to be doable with one or a combination of the transforms/tableinput-accept-input.hpl or transforms/tableinput-variables.hpl examples in the samples project.
Most of what you're trying to achieve seems relatively straightforward, but the current Christmas and New Year holiday period in large parts of the world may not be the best timing to get this sorted out as possible.
The community support we provide here should get you a long way, is best-effort and on a voluntary basis, so trying to add pressure won't work. If your management needs fast and contractually guaranteed SLAs etc, free to check the commercial support page.

sivaguru-rajasekaran · 2025-12-26T09:05:13Z

sivaguru-rajasekaran
Dec 26, 2025
Author

Thanks a lot for the guidance so far. I now understand that most of the things I'm trying to do can be achieved using the tableinput-accept-input.hpl or tableinput-variables.hpl sample pipelines.

I also completely get that this is a holiday season for many people around the world, and community support is voluntary. No pressure at all — I genuinely appreciate the help being offered here.

Since my use case may get more complex as I continue working on it, I’d love to know if there are any additional suggestions or best practices you recommend for structuring a more advanced solution.

Wishing you and the whole team a wonderful Christmas and New Year!
Please let me know when the best time would be to reconnect after the holidays.

1 reply

bamaer Dec 26, 2025
Collaborator

There's no need for an interruption, feel free to continue asking questions. I just wanted to point out that trying to apply (even moderate) pressure like "my management needs to get this resolved asap" is not how community support works ;-)

sivaguru-rajasekaran · 2025-12-26T09:53:35Z

sivaguru-rajasekaran
Dec 26, 2025
Author

Thanks, I understand your point. I’ve already tried timestamp validation from my end in multiple ways. If anyone can share a proper guideline or relevant reference material, that would really help me move forward.

1 reply

bamaer Dec 26, 2025
Collaborator

We've basically given you all the information you need, but you'll have to do your part of the work and study the examples to make sure you get to a working situation.

What I would do in your situation (if passing timestamps to your query is your main goal):

add FROM_DATE and TO_DATE parameters to your pipeline. Parameters are not exactly the same as variables, but they behave exactly the same within the context of a pipeline. If this works with parameters, it should be straightforward to move on afterwards
use the easiest, most basic query possible to get used to working with variables: "select * from MY_TABLE where the_date > '${FROM_DATE}' and the_date < '${TO_DATE}'. If that works (again, with the most basic date format first), you'll know how variable replacement in your queries works, and you can move on to more complex examples.

Again: don't aim for everything and the kitchen sink all at once, break it down into small, manageable pieces and move in small steps.

sivaguru-rajasekaran · 2025-12-30T11:41:47Z

sivaguru-rajasekaran
Dec 30, 2025
Author

Query used:
SELECT
FROM_TS,
TO_TS
FROM ETL_TABLE_CONTROL
WHERE table_name = 'XXSIFY_CPQ_ORDER_DETAILS_UDM';

Problem:

FROM_TS and TO_TS are defined as TIMESTAMP columns.

When queried directly as TIMESTAMP, values do not appear in preview / result set in Table input.

However, when I explicitly cast them to STRING (or VARCHAR), the values show correctly in the preview.

Example:

SELECT
CAST(FROM_TS AS STRING),
CAST(TO_TS AS STRING)
FROM ETL_TABLE_CONTROL
WHERE table_name = 'XXSIFY_CPQ_ORDER_DETAILS_UDM';

Questions:

Is this a known issue with timestamp handling in preview/result rendering?

Could this be related to timezone, session settings, or internal conversion factors?

Is there a recommended way to handle TIMESTAMP columns in control tables to avoid this behavior?

Any guidance or workaround would be appreciated.

1 reply

hansva Dec 30, 2025
Collaborator

In your database connection, go to the Advanced tab and make sure "supports the Timestamp data type" is selected and try again.

sivaguru-rajasekaran · 2025-12-30T11:54:00Z

sivaguru-rajasekaran
Dec 30, 2025
Author

What I’ve already tried:

Disable / enable timestamp option

Session refresh and re-started apache-2.16.0 again..

Re-executing query

0 replies

sivaguru-rajasekaran · 2025-12-30T12:18:06Z

sivaguru-rajasekaran
Dec 30, 2025
Author

I’m encountering an issue while retrieving timestamp columns directly from the database.

Scenario:

No transformation or business logic is applied.

I’m simply selecting timestamp columns from a table and checking the preview.

The columns are defined as TIMESTAMP in the database.

Query used:
SELECT
FROM_TS,
TO_TS
FROM ETL_TABLE_CONTROL
WHERE table_name = 'XXSIFY_CPQ_ORDER_DETAILS_UDM';

Issue observed:

In the preview/output, both FROM_TS and TO_TS are returned as NULL.

Even tried timestamp as string getting this error , OriginalSql = SELECT *
FROM APPS.XXSIFY_CPQ_ORDER_DETAILS_UDM
WHERE CREATED_ON > '${FROM_TS_STR}'
AND CREATED_ON < '${TO_TS_STR}'
, Error Msg = ORA-01858: a non-numeric character was found where a numeric was expected

Since this is a direct read from the database with no logic applied, I want to understand if any additional configuration or mechanism is required to correctly retrieve TIMESTAMP values in preview.

0 replies

bamaer · 2025-12-30T12:51:20Z

bamaer
Dec 30, 2025
Collaborator

It looks like you\re still struggling with variable replacement, and are now adding potential timestamp issues into the mix. Can you retrieve your timestamp values correctly if you use hard coded parameter values or just try to get 5 or so records without any parameters?

0 replies

sivaguru-rajasekaran · 2025-12-30T12:56:35Z

sivaguru-rajasekaran
Dec 30, 2025
Author

I want to clarify and consolidate my earlier post.

SELECT
FROM_TS,
TO_TS
FROM ETL_TABLE_CONTROL
WHERE table_name = 'XXSIFY_CPQ_ORDER_DETAILS_UDM'

Note: Despite the column names, both FROM_TS and TO_TS are defined as TIMESTAMP in the database.

When I try to preview records for this query, no records are returned and returned as null.

There is no transformation or logic applied — it’s a direct select from the database.

Timestamp handling option is enabled (also tried disable/enable, no change).

Question:

Since this is a direct database read and not a transformation issue:

Is any implicit conversion or formatting required for TIMESTAMP columns to appear in preview?

Is this expected behavior due to preview engine limitations?

Is there a known issue with TIMESTAMP data types in control tables?

1 reply

hansva Dec 30, 2025
Collaborator

It is not a known issue, and it will be database-specific

sivaguru-rajasekaran · 2025-12-31T04:41:05Z

sivaguru-rajasekaran
Dec 31, 2025
Author

but changing the database schema or data type is not a feasible approach for us.Instead of modifying the database, are there any tool-level or session-level options available to handle this properly?

1 reply

dmainou Dec 31, 2025

Do me a favour,

Get into the meta data perspective.
Find your database connection
Click on explore
Find your table
Click on preview first 100 rows

There is little to debug on the pipeline side until you have confirmed you can connect and see data.

You have a where clause in your 'basic' example above.
You seem to have no data returned.
Nothing tells me there is data to retrieve.

sivaguru-rajasekaran · 2025-12-31T05:02:48Z

sivaguru-rajasekaran
Dec 31, 2025
Author

As suggested, I tested the full table data retrieval; however, even the corresponding timestamp is not being retrieved.

1 reply

dmainou Dec 31, 2025

And if you repeat that in dbeaver it similar?

sivaguru-rajasekaran · 2025-12-31T05:21:03Z

sivaguru-rajasekaran
Dec 31, 2025
Author

The same query works fine in DBeaver and returns the expected records. Additionally, data is retrieved correctly when SYSTIMESTAMP is used as the default parameter, but the issue occurs when the timestamp value is fetched from the database.

2 replies

dmainou Dec 31, 2025

Cool, copy your connection string from dbeaver.
Also ensure your driver is the same.
Then they should match

sivaguru-rajasekaran Dec 31, 2025
Author

Ok, I will do the same. Thanks

hansva · 2025-12-31T08:05:00Z

hansva
Dec 31, 2025
Collaborator

Could you create a ticket for this.
Important information needed:

Hop version
Oracle DB version
Oracle JDBC driver used
DDL of table (or fields) that are causing the issue

0 replies

sivaguru-rajasekaran · 2025-12-31T11:49:05Z

sivaguru-rajasekaran
Dec 31, 2025
Author

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'
);

0 replies

sivaguru-rajasekaran · 2025-12-31T11:54:22Z

sivaguru-rajasekaran
Dec 31, 2025
Author

Issue Id:#6279

0 replies

Oracle Table Input with variables (${LAST_RUN_TS}, ${LAST_RUN_ID}) failing in Apache Hop #6250

Uh oh!

sivaguru-rajasekaran Dec 22, 2025

Replies: 25 comments · 10 replies

Uh oh!

bamaer Dec 22, 2025 Collaborator

Uh oh!

sivaguru-rajasekaran Dec 23, 2025 Author

Uh oh!

sivaguru-rajasekaran Dec 23, 2025 Author

Uh oh!

hansva Dec 23, 2025 Collaborator

Uh oh!

sivaguru-rajasekaran Dec 23, 2025 Author

Uh oh!

sivaguru-rajasekaran Dec 23, 2025 Author

Uh oh!

hansva Dec 23, 2025 Collaborator

Uh oh!

bamaer Dec 23, 2025 Collaborator

Uh oh!

bamaer Dec 23, 2025 Collaborator

Uh oh!

dmainou Dec 23, 2025

Uh oh!

sivaguru-rajasekaran Dec 25, 2025 Author

Uh oh!

dmainou Dec 26, 2025

Uh oh!

sivaguru-rajasekaran Dec 26, 2025 Author

Uh oh!

bamaer Dec 26, 2025 Collaborator

Uh oh!

sivaguru-rajasekaran Dec 26, 2025 Author

Uh oh!

bamaer Dec 26, 2025 Collaborator

Uh oh!

sivaguru-rajasekaran Dec 26, 2025 Author

Uh oh!

bamaer Dec 26, 2025 Collaborator

Uh oh!

sivaguru-rajasekaran Dec 30, 2025 Author

Uh oh!

hansva Dec 30, 2025 Collaborator

Uh oh!

Uh oh!

sivaguru-rajasekaran Dec 30, 2025 Author

Uh oh!

sivaguru-rajasekaran Dec 30, 2025 Author

Uh oh!

bamaer Dec 30, 2025 Collaborator

Uh oh!

sivaguru-rajasekaran Dec 30, 2025 Author

Uh oh!

hansva Dec 30, 2025 Collaborator

Uh oh!

sivaguru-rajasekaran Dec 31, 2025 Author

Uh oh!

dmainou Dec 31, 2025

Uh oh!

sivaguru-rajasekaran Dec 31, 2025 Author

Uh oh!

Uh oh!

dmainou Dec 31, 2025

Uh oh!

sivaguru-rajasekaran Dec 31, 2025 Author

Uh oh!

dmainou Dec 31, 2025

Uh oh!

sivaguru-rajasekaran Dec 31, 2025 Author

Uh oh!

sivaguru-rajasekaran
Dec 22, 2025

Replies: 25 comments 10 replies

bamaer
Dec 22, 2025
Collaborator

sivaguru-rajasekaran
Dec 23, 2025
Author

sivaguru-rajasekaran
Dec 23, 2025
Author

hansva
Dec 23, 2025
Collaborator

sivaguru-rajasekaran
Dec 23, 2025
Author

sivaguru-rajasekaran
Dec 23, 2025
Author

hansva
Dec 23, 2025
Collaborator

bamaer
Dec 23, 2025
Collaborator

bamaer
Dec 23, 2025
Collaborator

dmainou
Dec 23, 2025

sivaguru-rajasekaran
Dec 25, 2025
Author

sivaguru-rajasekaran
Dec 26, 2025
Author

bamaer Dec 26, 2025
Collaborator

sivaguru-rajasekaran
Dec 26, 2025
Author

bamaer Dec 26, 2025
Collaborator

sivaguru-rajasekaran
Dec 26, 2025
Author

bamaer Dec 26, 2025
Collaborator

sivaguru-rajasekaran
Dec 30, 2025
Author

hansva Dec 30, 2025
Collaborator

sivaguru-rajasekaran
Dec 30, 2025
Author

sivaguru-rajasekaran
Dec 30, 2025
Author

bamaer
Dec 30, 2025
Collaborator

sivaguru-rajasekaran
Dec 30, 2025
Author

hansva Dec 30, 2025
Collaborator

sivaguru-rajasekaran
Dec 31, 2025
Author

sivaguru-rajasekaran
Dec 31, 2025
Author

sivaguru-rajasekaran
Dec 31, 2025
Author

sivaguru-rajasekaran Dec 31, 2025
Author