tOracleOutput : Using Oracle function while inserting/updating Data

[akumar2301]

This article explain how to use oracle functions while inserting/Updating data using tOracleOutput. 

Let us take a simple example 

Input 

Name	DOB_with_timezone
John	2000-01-1001:12:05-04:00
Nath	1986-01-1012:01:10-08:00

Now if we would like to use Oracle function UPPER and TO_TIMESTAMP_TZ  while inserting data in table. 

Solution

We could do this using "Additional Column" Setting in tOracleOutput . 

Problem

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.

But

it can access the globalMap so expression like  ((String)globalMap.get("name")) or ((String)globalMap.get("row2.dob_tz")) are good to go.

But 

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

tFixedflowInput

Output in Database 

[Anonymous]

Hi Abhi,

     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?

Warm Regards,
Nikhil Thampi

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 🙂

[Anonymous]

Hello,

I have moved your article to a more appropriate board.

Best regards

Sabrina