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

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

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.