I've a table which is being loaded through Informatica ETL tool. One column in the table is defined as PERIOD(TIMESTAMP(6) WITH TIME ZONE). There is no such datatype i found in the ETL tool. As the field is straight move from the source(flat file), I've made the datatype CHAR(72) in the ETL Tool.
Now the ETL tool is making the MLOAD script dynamically(Loader Connection) and it's declaring the datatype of the column in the script as CHAR(72). During MLOAD, the conversion from CHAR to PERIOD should be done appropriately but it is throwing the error : "UTY0805 RDBMS failure, 6996: Invalid conversion or assignment operation on a Period."
So MLOAD is not able to convert the data.
Note: We can insert the data manually through SQLA in the same table with same value. That ensure the data is correct and the DDL is also correct. TD version: 14.10.01.01
Could you please help me regarding this error?
I think it happens with few tools. You can think of loading as char or varchar as normal process in a job. Then at the end you can run task, in a session calling a script or unix script to load from that table with char/varchar to a period data type.
I did that in SAP DS few years back.
Thanks Raja for your response. Yes obviously I can do this to get this thing done. But I've no clue that why it is happening. Because in TD13, the same etl code/TD Mload is able to do this task. The problem starts when Our system upgraded from TD13 to TD14.
Yes..there is no difference in the mload script created by informatica. Also the log file has no difference other than the failure message.
Just for sharing information can you please, share the diff? Also with that diff maybe you can call informatica tool support folks to get it resolved. Just my suggestion.