Oracle datetime output in wrong format

Highlighted
Four Stars

Oracle datetime output in wrong format

Hi Talend Community,

 

I queried a oracle DB, using the following query

.2019-12-11_21-58-12.pngo_query(above is the schema i defined for the oracledb)

Where i get the 2 values and convert the table times to utc.

Underneath you see the output, for the query.

2019-12-11_21-58-25.pngo_output

I would like to send this to a sql db. i used the components toracleinput -->tmap-->tmssqloutput

 

Underneath you see the tmap i configured.

 

2019-12-11_22-03-39.pngtmap

This is the mssql db schema i configured

 

2019-12-11_22-04-32.pngsql_typeou

2019-12-11_22-05-12.pngoutput

The question i have is the following:

 

i wrote to a csv file first to check the values that were going to be send to the msql db.

 

As you can see this doesnt reflect the 2nd picture(query output).

 

I think the problem is in the dates that come from the oracleinput, but i cannot figure out where this could come from and how to resolve this.

 

Is there something that i didnt configure right, or something that i oversee.

 

 

Employee

Re: Oracle datetime output in wrong format

Hi,

 

    Since the data from input area is already in date format, I don't think you have to parse again. The data will automatically reallign to new format since its in date data type. Please also note that you are parsing the date at output area in one format but underlying schema is in different format.

image.png

 

I would recommend you to use tLogrow to test the output after each component to make sure that you are getting right output. Once you are sure that data is correct, you can remove them.

 

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

Three Stars

Re: Oracle datetime output in wrong format

Hi Thanks for the reply.

 

With the tlogrow i get the following output.

2019-12-12_10-02-45.png 

as you can see this does not match the toracleinput query result. I am missing the sop_start_utc time and the stop_end_utc time is in a weird format.

 

How can i solve this

Three Stars

Re: Oracle datetime output in wrong format

This is the output i get when i connect the tlogrow to the toracleinput

 

2019-12-12_10-52-25.png

Employee

Re: Oracle datetime output in wrong format

Hi,

 

    In the Oracle query, you are using to_char function to convert the entrire data to String. Could you please share the schema of the tOracleInput? I would like to double check whether its in same format. When I saw the schema for tMap, it was showing as date function.

 

     In short, the first step will be to make sure that the data types are correct for the input component and see whether its printing correctly from the input component. There is nothing wrong in taking the date value as string from DB. You can convert the column to date data type in later stage using either tConvertType component or using Talend parse date functions. 

 

    I think once you resolve this issue, you should be able to resolve the underlying problem. 

 

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

2019 GARTNER 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

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog