pb with date format when create table with tOracleInput

One Star

pb with date format when create table with tOracleInput

Hello,
I am working with TOS 2.1.1.r4583 perl génération (perl version 5.8.8) on Windows2000, and I try to copy data from Oracle 10.2 to Mysql5.0.
The date fields in my Database source Oracle is like "05-APR-06" , "21-JUL-06"
The structure and data of my table destination database Mysql 5 is created with TOS component tOracleInput.
But when the job is terminated, the date field on my Mysql database destination is always "0000-00-00 00:00:00"
So, my Oracle table structure is:
SQL> desc SRF_DBO.TSRFILOTDECLAREAP
Name Null? Type
----------------------------------------- -------- ----------------------------
DATEMISEAJOURAP DATE
mysql> desc t_srfilotdeclareap;
+----------------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+-------------+------+-----+---------+-------+
|DATEMISEAJOURAP| datetime | YES | | NULL | |

Is any one have this pb ?
Community Manager

Re: pb with date format when create table with tOracleInput

Hi,
We need to see how the input data is read.
Can you connect your tOracleInput to a tLogRow, execute and send us a screenshot of the trace that displays on the console. This will give an idea on how the date time is read by tOracleInput component.
Let start this way.
Cheers,
Elisa
One Star

Re: pb with date format when create table with tOracleInput

Hi Elisa,
Here is my oracle data input (7th field is date format as DD-MON-YY)
***********************
Starting job test_charge_perl at 16:10 18/07/2007.
connecting to socket on port 4649 ...
connected
connecting to socket on port 4113 ...
connected
001000547.001.6|001000547|6|CL|ON|310|05-APR-06|01364|310.23|2006|313|GPM|223.97|0|0|223.97
001000547.001.8|001000547|8|CL|ON|395|05-APR-06|01364|394.8|2006|314|GPM|84.42|0|0|84.42
001000547.001.13|001000547|13|CL|ON|11|05-APR-06|01364|11.03|2006|316|GPM|11.02|0|0|11.02
001000547.001.16|001000547|16|CL|ON|157|05-APR-06|01364|157.32|2006|317|GPM|0|0|0|0
001000547.001.18|001000547|18|CL|ON|204|05-APR-06|01040|203.99|2006|318|GPM|203.98|0|0|203.98
Job test_charge_perl ended at 16:10 18/07/2007.

thkss,
Regards.
Highlighted
One Star

Re: pb with date format when create table with tOracleInput

Hello,
I am working with TOS 2.1.1.r4583 perl génération (perl version 5.8.8) on Windows2000, and I try to copy data from Oracle 10.2 to Mysql5.0.
The date fields in my Database source Oracle is like "05-APR-06" , "21-JUL-06"
The structure and data of my table destination database Mysql 5 is created with TOS component tOracleInput.
But when the job is terminated, the date field on my Mysql database destination is always "0000-00-00 00:00:00"
So, my Oracle table structure is:
SQL> desc SRF_DBO.TSRFILOTDECLAREAP
Name Null? Type
----------------------------------------- -------- ----------------------------
DATEMISEAJOURAP DATE
mysql> desc t_srfilotdeclareap;
+----------------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+-------------+------+-----+---------+-------+
|DATEMISEAJOURAP| datetime | YES | | NULL | |

Is any one have this pb ?

Hi Driss,
may be the issue is that you are having "Date" field in your Oracle DB and when it comes to MySql the field type changed to "Datetime". So, Alter the field type to "Date" in your MySql table.
may be this works
Employee

Re: pb with date format when create table with tOracleInput

Driss, you have to set the NLS_DATE_FORMAT so that it matches the MySQL expected string representation of a datetime.
With TOS < 2.3, you need a tPerl with:
$ENV{NLS_DATE_FORMAT} = 'YYYY-MM-DD HH24:MI:SS';

With TOS >= 2.3, a tSetGlobalVar is recommended:
key = 'NLS_DATE_FORMAT'
value = 'YYYY-MM-DD HH24:MISmiley FrustratedS'
related topics:
- 63
- 1959
- 566

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