topic Re: Problem timestamp tOracleOutputBulkExec in Talend Studio

Problem timestamp tOracleOutputBulkExec

Anonymous — Sat, 16 Nov 2024 09:39:29 GMT

Hi everyone,

I'm trying to load Oracle with a table that contains timestamp, but tOracke loads me with hours, minutes, and seconds set to 0. The format I use is ddMMMyyyy hh: mm: ss and I charge, for example: 25-MAY- 1999 00: 00: 00.000000. Thanks a lot

Re: Problem timestamp tOracleOutputBulkExec

Anonymous — Tue, 13 Jun 2017 04:10:37 GMT

Hi,

Could you please also post your component setting screenshots into forum which will be helpful for us to address your issue?

Best regards

Sabrina

Re: Problem timestamp tOracleOutputBulkExec

Anonymous — Tue, 13 Jun 2017 07:32:01 GMT

Hi,

i have this setting on Oracle:

PARAMETER VALUE

------------------------------ ----------------------------------------

NLS_LANGUAGE AMERICAN

NLS_TERRITORY AMERICA

NLS_CURRENCY $

NLS_ISO_CURRENCY AMERICA

NLS_NUMERIC_CHARACTERS .,

NLS_CHARACTERSET WE8ISO8859P1

NLS_CALENDAR GREGORIAN

NLS_DATE_FORMAT DD-MON-RR

NLS_DATE_LANGUAGE AMERICAN

NLS_SORT BINARY

NLS_TIME_FORMAT HH.MI.SSXFF AM

PARAMETER VALUE

------------------------------ ----------------------------------------

NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY $

NLS_COMP BINARY

NLS_LENGTH_SEMANTICS BYTE

NLS_NCHAR_CONV_EXCP FALSE

NLS_NCHAR_CHARACTERSET AL16UTF16

NLS_RDBMS_VERSION 11.2.0.4.0

My component have this setting:

In oracle my timestamp output is, for example: 13-JUN-2017 00:00:00.00000000 without hour,minutes and second.

Thanks again.

Re: Problem timestamp tOracleOutputBulkExec

Anonymous — Wed, 14 Jun 2017 13:59:27 GMT

Can you help me? thanks!

Re: Problem timestamp tOracleOutputBulkExec

Anonymous — Thu, 15 Jun 2017 10:21:27 GMT

Hi,

Did you use a tMap component in your work flow? On which build version you got this issue?

Best regards

Sabrina

Re: Problem timestamp tOracleOutputBulkExec

Anonymous — Thu, 15 Jun 2017 10:49:50 GMT

Hello, The problem is that I have records in date format (ddMMMyyy: HH: mm: ss) I want to save in an oracle database as a timestamp, but these are saved with hours, minutes, and seconds set to 00. For example: 15-MAY- 2017 00: 00: 00.00000000.

Re: Problem timestamp tOracleOutputBulkExec

vapukov — Thu, 15 Jun 2017 11:14:28 GMT

@livio.ventura wrote:
Hello, The problem is that I have records in date format (ddMMMyyy: HH: mm: ss) I want to save in an oracle database as a timestamp, but these are saved with hours, minutes, and seconds set to 00. For example: 15-MAY- 2017 00: 00: 00.00000000.

If You look at the left side of You picture You will see

DATA_INS have length == 9

which is exactly 15MAY2017 ... but not 15MAY2017 23:15:45

Start from source - and check all formats and lengths, plus - better do not trust for auto conversion, when need convert and format date - use tMap and functions

Re: Problem timestamp tOracleOutputBulkExec

Anonymous — Thu, 15 Jun 2017 15:52:13 GMT

Hello, i changed the length, but the problem is because my date format is ddMMMyyyy: HH: mm: ss and maybe Oracle does not accept it. The timestamp format that is to oracle is ddMMMyyyy: hh: mm: ss but it does not accept it. Maybe the ":" after the year? If so, how can I make my date format ddMMMyyyy HH: mm: ss? I use the tConvert node to convert the date from string to date (format ddMMMyyyy: HH: mm: ss) and then I execute the next steps, use tMap only to map the fields and pass them to the DB Oracle.