This article explain how to use oracle functions while inserting/Updating data using tOracleOutput.
Let us take a simple example
Now if we would like to use Oracle function UPPER and TO_TIMESTAMP_TZ while inserting data in table.
We could do this using "Additional Column" Setting in tOracleOutput .
Normally "Additional Column" Setting does not have access to resultset of Main flow. So we cannot use expression like row2.name or row2.dob_tz.
it can access the globalMap so expression like ((String)globalMap.get("name")) or ((String)globalMap.get("row2.dob_tz")) are good to go.
As per Talend workflow design, in a Subjob , tOracleOutput will be configured 1st then rest of flow , so if we try to set the global Variable during the main flow , "tOracleOutput - Additional Column" cannot access updated globaMap Variable info
So , thanks to iterator of tFlowToIterate , we can create iterate over globalMap Variables of main flow. So job look like this.
tOracleOutput - Additional Column
name --> "UPPER('" + ((String)globalMap.get("row2.name")) + "')"
dob_tz --> "TO_TIMESTAMP_TZ('" + ((String)globalMap.get("row2.DOB_TZ")) + "','YYYY-MM-DDHH:MISTZH:TZM')"
Output in Database
Thanks for the article.
I would recommend you to create the articles like these under "How-Tos and Best Practices" section of our Talend Community.
Since there are lot of design and development related queries are floating here, the article may not get the right attention in current category.
@xdshi - Could you please advise whether we can move an existing post to another category?
Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)
I have moved your article to a more appropriate board.
Talend named a Leader.
Kickstart your first data integration and ETL projects.
Part 2 of a series on Context Variables
Learn how to do cool things with Context Variables
Find out how to migrate from one database to another using the Dynamic schema