Originally posted by: SystemAdmin

Has anyone had any success writing to a TIMESTAMP column in Oracle 9i?

No matter how one defines the column in the type tree (Text/ DateTime), the adapter refuses to handle a column defined as TIMESTAMP in Oracle.

The returned message is always:
" Column n (TMSTMPFLD1) type is <Unknown>"
" Column n+1 (TMSTMPFLD2) type is <Unknown>"
"Unsupported datatype returned is being treated as text"
"Error in OCIStmtExecute"
"ORA-01843-: not a valid month"
.
.
"The following values were being inserted:
Column n TMSTMPFLD1 : 082923
Column n+1 TMSTMPFLD2 : 08:29:23"

I would appreciate any input on this issue.
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

Looks like you haven't set up the date part in column n. Both columns seem to contain the time.
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

The column is defined as TIMESTAMP(6) in Oracle
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: SystemAdmin

TIMESTAMP
(fractional_seconds_
precision)
Year, month, and day values of date, as well as hour, minute, and
second values of time, where fractional_seconds_
precision is the number of digits in the fractional part of the
SECOND datetime field. Accepted values of fractional_
seconds_precision are 0 to 9. The default is 6

NLS_TIME_FORMAT HH.MI.SSXFF AM
Time format
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
Time stamp format
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
Time with timezone format
NLS_TIMESTAMP_TZ_FOR DD-MON-RR HH.MI.SSXFF AM TZR
MAT
Timestamp with timezone format
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: SystemAdmin

Pardon my example (and delay in response)

The map fails even with date part included in it. I am more concerned with the error:
"Unsupported datatype returned is being treated as text"

We are reconsidering using the DATE format as it supports our need at this point of time. I am still curious about how the INSERT statement would like like.
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange