One Star

Load DATE type into an Oracle TIMESTAMP column

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
21 REPLIES
One Star

Re: Load DATE type into an Oracle TIMESTAMP column

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

Re: Load DATE type into an Oracle TIMESTAMP column

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Load DATE type into an Oracle TIMESTAMP column

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
Moderator

Re: Load DATE type into an Oracle TIMESTAMP column

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Load DATE type into an Oracle TIMESTAMP column

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
One Star

Re: Load DATE type into an Oracle TIMESTAMP column

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
Moderator

Re: Load DATE type into an Oracle TIMESTAMP column

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Load DATE type into an Oracle TIMESTAMP column

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
One Star

Re: Load DATE type into an Oracle TIMESTAMP column

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
Moderator

Re: Load DATE type into an Oracle TIMESTAMP column

Hi,
The issue has been resolved in talend version 5.0.4, 5.1.4, 5.2.2, 5.3.0M3. Fortunately, the version of 5.2.2 will be released in the two days. Please wait for that.
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.
One Star

Re: Load DATE type into an Oracle TIMESTAMP column

Thank you so much for your support Sabrina!
I'll wait for 5.2.2.
Regards,
michelangelo
Moderator

Re: Load DATE type into an Oracle TIMESTAMP column

Hi michelangelo,
If the talend open studio version of 5.2.2 is available, I will inform you in forum at the first time. Please pay attention about talend forum, thanks.
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.
Moderator

Re: Load DATE type into an Oracle TIMESTAMP column

Hi mpagano,
Here is a good news that Talend open studio version of 5.2.2 is available now.
Please see this announcement forum for details Forum28549
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.
One Star

Re: Load DATE type into an Oracle TIMESTAMP column

Thank you Sabrina!
Best Regards,
michelangelo
Moderator

Re: Load DATE type into an Oracle TIMESTAMP column

Hi mpagano,
Would you mind sharing your testing result (Talend open studio products 5.2.2) with us. The feedback is very highly appreciated.
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.
One Star

Re: Load DATE type into an Oracle TIMESTAMP column

I'm having the same issue in v.5.1.3 Enterprise Edition.
Do you mind posting the JavaFlex workaround? I'm not certain where the tOracleOutput code starts and stops.
Moderator

Re: Load DATE type into an Oracle TIMESTAMP column

Hi bentleyc,
The issue has been resolved in talend version 5.0.4, 5.1.4, 5.2.2, 5.3.0M3, perhaps you can make upgrade for your studio or download talend open studio products 5.2.2 from Talend Official Website.
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.
One Star

Re: Load DATE type into an Oracle TIMESTAMP column

Hi,
We are currently using TOS 5.5.1
For one of my requirement of filtering source data with timestamp (having milli second as granularity), I am storing the last timestamp in a variable and using it in sql query to filter source.
But in date variable milli seconds are getting truncated.
Why is millisecond part still getting truncted?
Please advice if there is something that am missing.
Find below two different outputs of same timestamp.
Below is from tLogRow
.-----------------------.
|       tLogRow_4       |
|=---------------------=|
|LASTEST_TIMESTAMP      |
|=---------------------=|
|2015-04-22 13:31:07.749|
'-----------------------'

Below is what stored in variable
LASTEST_TIMESTAMP: Wed Apr 22 13:31:07 AST 2015

Re: Load DATE type into an Oracle TIMESTAMP column

I am using Talend DI 6.2.1.
Database :- Oracle 12c
Input is 
select TO_TIMESTAMP ((TO_CHAR(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MISmiley FrustratedS.FF')), 'YYYY-MM-DD HH24:MISmiley FrustratedS.FF')as LOAD_DATETIME from dual
and want to store output in Oracle table with tOracle output.
However Talend is not able to load milisecond part of the input. 
On tLogRow it shows with millisecond part however on target table it is not displaying miliseconds.
Is the bug previously mentioned for version 5.4 is still present in 6.2.1?
One Star

Re: Load DATE type into an Oracle TIMESTAMP column

I am using Talend DI 6.2.1.
Database :- Oracle 12c
Input is 
select TO_TIMESTAMP ((TO_CHAR(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MISmiley FrustratedS.FF')), 'YYYY-MM-DD HH24:MISmiley FrustratedS.FF')as LOAD_DATETIME from dual
and want to store output in Oracle table with tOracle output.
However Talend is not able to load milisecond part of the input. 
On tLogRow it shows with millisecond part however on target table it is not displaying miliseconds.
Is the bug previously mentioned for version 5.4 is still present in 6.2.1?

Hi Amit,
    Did you find any resolution?
    I too find the same issue in 6.2.1.
Regards,
HarishBabu D
Twelve Stars

Re: Load DATE type into an Oracle TIMESTAMP column

I am using Talend DI 6.2.1.
Database :- Oracle 12c
Input is 
select TO_TIMESTAMP ((TO_CHAR(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MISmiley FrustratedS.FF')), 'YYYY-MM-DD HH24:MISmiley FrustratedS.FF')as LOAD_DATETIME from dual
and want to store output in Oracle table with tOracle output.
However Talend is not able to load milisecond part of the input. 
On tLogRow it shows with millisecond part however on target table it is not displaying miliseconds.
Is the bug previously mentioned for version 5.4 is still present in 6.2.1?

Hi Amit,
    Did you find any resolution?
    I too find the same issue in 6.2.1.
Regards,
HarishBabu D
there are no any issue, Oracle date - not support milliseconds 
if You want store milliseconds - use TIMESTAMP datatype for oracle columns
proper format for convert string to timestamp - 
TalendDate.parseDate("yyyy-MM-dd HH:mm:ss.SSS",row1.dateColumn) 
-----------