topic Load DATE type into an Oracle TIMESTAMP column in Talend Studio

Load DATE type into an Oracle TIMESTAMP column

Anonymous — Fri, 01 Mar 2013 17:23:58 GMT

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

Re: Load DATE type into an Oracle TIMESTAMP column

Anonymous — Fri, 01 Mar 2013 18:12:28 GMT

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

Re: Load DATE type into an Oracle TIMESTAMP column

Anonymous — Sat, 02 Mar 2013 08:44:50 GMT

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

Re: Load DATE type into an Oracle TIMESTAMP column

Anonymous — Mon, 04 Mar 2013 09:54:02 GMT

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

Re: Load DATE type into an Oracle TIMESTAMP column

Anonymous — Mon, 04 Mar 2013 10:12:33 GMT

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

Re: Load DATE type into an Oracle TIMESTAMP column

Anonymous — Mon, 04 Mar 2013 10:43:40 GMT

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

Re: Load DATE type into an Oracle TIMESTAMP column

Anonymous — Mon, 04 Mar 2013 15:03:16 GMT

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

Re: Load DATE type into an Oracle TIMESTAMP column

Anonymous — Tue, 05 Mar 2013 04:07:06 GMT

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

Re: Load DATE type into an Oracle TIMESTAMP column

Anonymous — Tue, 05 Mar 2013 09:02:10 GMT

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

Re: Load DATE type into an Oracle TIMESTAMP column

Anonymous — Tue, 05 Mar 2013 10:48:54 GMT

Hi Sabrina,
look at this url
https://jira.talendforge.org/browse/TDI-24412?page=com.atlassian.jira.plugin.system.issuetabpanels%3Achangehistory-tabpanel#issue-tabs
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

Re: Load DATE type into an Oracle TIMESTAMP column

Anonymous — Wed, 06 Mar 2013 03:45:56 GMT

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

Re: Load DATE type into an Oracle TIMESTAMP column

Anonymous — Wed, 06 Mar 2013 10:00:31 GMT

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

Re: Load DATE type into an Oracle TIMESTAMP column

Anonymous — Thu, 07 Mar 2013 02:38:30 GMT

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

Re: Load DATE type into an Oracle TIMESTAMP column

Anonymous — Tue, 12 Mar 2013 07:00:11 GMT

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

Re: Load DATE type into an Oracle TIMESTAMP column

Anonymous — Tue, 12 Mar 2013 08:49:04 GMT

Thank you Sabrina!
Best Regards,
michelangelo

Re: Load DATE type into an Oracle TIMESTAMP column

Anonymous — Tue, 12 Mar 2013 09:10:26 GMT

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

Re: Load DATE type into an Oracle TIMESTAMP column

Anonymous — Wed, 27 Mar 2013 20:27:53 GMT

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.

Re: Load DATE type into an Oracle TIMESTAMP column

Anonymous — Tue, 02 Apr 2013 08:49:50 GMT

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

Re: Load DATE type into an Oracle TIMESTAMP column

_AnonymousUser — Thu, 23 Apr 2015 08:02:29 GMT

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

Anonymous — Tue, 20 Sep 2016 17:44:57 GMT

I am using Talend DI 6.2.1.
Database :- Oracle 12c
Input is
select TO_TIMESTAMP ((TO_CHAR(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI S.FF')), 'YYYY-MM-DD HH24:MI S.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?