GitHub user sivaguru-rajasekaran created a discussion: Oracle Table Input with
variables (${LAST_RUN_TS}, ${LAST_RUN_ID}) failing in Apache Hop
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: <YOUR VERSION>
- Database: Oracle
- Execution engine: Local
Questions:
1. What is the recommended pattern in Hop for parameterized Oracle Table Input
queries?
2. Is variable substitution in Table Input officially supported for timestamps?
3. Should this be handled via workflow → SQL generation → pipeline instead?
Any guidance or best practices would be appreciated.
Thanks!
<img width="943" height="556" alt="Image"
src="https://github.com/user-attachments/assets/bd092d1d-4807-4ecd-8735-6cca96bc9f2a";
/>
<img width="942" height="548" alt="Image"
src="https://github.com/user-attachments/assets/14db38b2-fb32-4ad3-8623-375fd57ac5ed";
/>
GitHub link: https://github.com/apache/hop/discussions/6250
----
This is an automatically sent email for [email protected].
To unsubscribe, please send an email to: [email protected]
