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 ?
Tags (1)
4 REPLIES
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.
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