Load DATE type into an Oracle TIMESTAMP column

[Anonymous]

Hi,
I need to load a DATE field with millisecond ("yyyy-MM-dd HH:mm:ss.zzz") into an oracle table.
but unfortunately, oracle needs TIMESTAMP datatype in order to manage the milliseconds.
When I run my job, I receive the following exception:
"ORA-01858: a non-numeric character was found where a numeric was expected"
How to match the talend DATE with oracle TIMESTAMP?
Thanks in advance,
Regards,
michelangelo

[Anonymous]

zzz is the time zone. Use SSS as in HH:mm:ss.SSS instead.

[Anonymous]

Hi michelangelo,
You can review all the date format by pressing ctrl+space from "Edit Schema" option
See the pic.
Hope it will help you for the further job.
Best regards
Sabrina

[Anonymous]

Hi Sabrina and uInfinity,
Thanks for your support.
I changed the Date pattern on the tMap before the tOracleOutput, but unfortunately I still have the same error.
The result is that records are loaded on the db table but the milliseconds are set to zero: ",0000".
Please, see the pictures posted.
Regards,
michelangelo

[Anonymous]

Hi,
Could you please elaborate your case with an example with input and expected output values? I will make a testing for it.
Best regards
Sabrina

[Anonymous]

Hi Sabrina,
here is an example:
I have a tfileInputDelimited module that must read a csv file like this:
2013-02-28 11:48:20.456,AAA,172.20.0.31,BBB,monitoring,SUCCESS
in the input schema, the first field is a DATE with pattern: "yyyy-MM-dd HH:mm:ss.SSS"; other fields are STRING.

The output must be sent to an oracle table (tOracleOutput) with the following columns:
field datatype
STARTDATE TIMESTAMP(4)
NAME VARCHAR(20)
IPADDRESS VARCHAR(20)
CITY VARCHAR(20)
MODE VARCHAR(20)
RESULT VARCHAR(20)
the record on the table must be exactly in the same format of the input record.
Let me know if you need any further explanation.
Thank you so much for your support.
Regards
Michelangelo

[Anonymous]

Hi Sabrina,
Configuring a basic flow with a tFileInputDelimited and a tOracleInput, the oracle error disappear, but on the DB table, the milliseconds are still set to zero.
Es:
input file: 2013:03:04 12:34:02,5678 (set as DATE on talend schema)
DB TABLE: 2013:03:04 12:34:02,0000 (set as TIMESTAMP on oracle)
regards,
michelangelo

[Anonymous]

Hi michelangelo,

Es:
input file: 2013:03:04 12:34:02,5678 (set as DATE on talend schema)
DB TABLE: 2013:03:04 12:34:02,0000 (set as TIMESTAMP on oracle)

The Date Pattern is "yyyy-MM-dd HH:mm:ss.SSS" and the range of .SSS is 000~999, which means your input source should be 2013:03:04 12:34:02.567
See my pic
Best regards
Sabrina

[Anonymous]

Hi Sabrina,
I'm sorry but I still have this strange problem.
Please, look at my pic for the test flow:
FLOW 1) read from a file, print on the screen with tLogRow and write on the oracle table
As you can see, the millisecond are correctly printed by the tLogRow on screen: 2013-02-28 11:50:20.456
but looking on my oracle table, the milliseconds are missing: 2013-02-28 11:50:20.000
FLOW 2) read from the oracle table and print on the screen with tLogRow
Reading the record on the table, we can confirm that milliseconds are missing on the DB.

I cannot understand where is the issue... maybe it will have a very simple explanation, but I'm going crazy 😉
Thanks for your support Sabrina!
Regard,
michelangelo

[Anonymous]

Hi Sabrina,
look at this url
https://jira.talendforge.org/browse/TDI-24412?page=com.atlassian.jira.plugin.system.issuetabpanels%3...
it seems to be a known bug...
I used a tJavaFlex instead of the tOracleOutput with the same Java code
I made the needed changes proposed
and now it works! I have the milliseconds on the DB table.
The question is:
Is it the only solution? or can I still use the tOracleOutput with some specific configuration?
regards,
Michelangelo