Four Stars

Problem timestamp tOracleOutputBulkExec

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

  • Big Data
  • Data Integration
7 REPLIES
Moderator

Re: Problem timestamp tOracleOutputBulkExec

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

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Four Stars

Re: Problem timestamp tOracleOutputBulkExec

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:

Immagine.jpgImmagine2.jpgImmagine3.jpg

 

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

Thanks again.

 

Four Stars

Re: Problem timestamp tOracleOutputBulkExec

Can you help me? thanks!
Moderator

Re: Problem timestamp tOracleOutputBulkExec

Hi,

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

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.
Four Stars

Re: Problem timestamp tOracleOutputBulkExec

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.
Nine Stars

Re: Problem timestamp tOracleOutputBulkExec


livio218526 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 

-----------
Four Stars

Re: Problem timestamp tOracleOutputBulkExec

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.