tOracleOutput : Using Oracle function while inserting/updating Data

Highlighted
Eleven Stars

tOracleOutput : Using Oracle function while inserting/updating Data

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
Nath1986-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 Smiley Sad

TalendImage.JPG

 

So , thanks to iterator of tFlowToIterate , we can create iterate over globalMap Variables of main flow. So job look like this.

 

TalendImage2.JPG

 

tOracleOutput -  Additional Column

TalendImage3.JPG

name --> "UPPER('" + ((String)globalMap.get("row2.name")) + "')"

dob_tz --> "TO_TIMESTAMP_TZ('" + ((String)globalMap.get("row2.DOB_TZ")) + "','YYYY-MM-DDHH:MISmiley FrustratedSTZH:TZM')"

 

tFixedflowInput

TalendImage4.JPG

 

Output in Database 

 

TalendImage5.JPG

 

 

 

Regards
Abhishek KUMAR
Employee

Re: tOracleOutput : Using Oracle function while inserting/updating Data

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 :-)

Moderator

Re: tOracleOutput : Using Oracle function while inserting/updating Data

Hello,

I have moved your article to a more appropriate board.

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Why Companies Move to the Cloud: 7 Success Stories

Learn how and why companies are moving to the Cloud

Read Now