Dynamic column error on date data type

mbennitt — Sat, 16 Nov 2024 06:38:56 GMT

I'm working on a generic Talend job that will allow us to process hundreds of tables. The goal is to take the CSV files from S3 and insert the data into each Oracle table. Since each table can have different columns and different data types this process needs to be completely dynamic.

My current process is tS3Get -> tFileInputDelimieted -> tMap -> tOracleOutput

The issue occurs in the last component when inserting data into Oracle. I am getting "ORA-01861: literal does not match format string". I believe the issue occurs when trying to insert into the data field. When looking at the schema for the tOracleOutput I have one column for results, type of Dynamic, and it forces me to pick a type for DB Type which defaults to varchar2 (highlighted in orange). I believe that it's trying to force a varchar2 into a date field which obviously won't work. I thought dynamic worked with any and all columns and can flow freely no matter what data types they are.

Things I've tried:

1) Changing the date format in the tMap to different combinations.

2) Dropping and creating the table in Oracle. This works but defines every single column as a varchar2 which is not correct. We have several different data types including date fields that need to maintain their data type.

Re: Dynamic column error on date data type

Anonymous — Thu, 07 Feb 2019 14:32:56 GMT

Hi,
My question is - why for insert data you use tOracleInput ? and not tOracleOutput.

May be check with function isDate :

System.out.println(TalendDate.isDate("2010-02-09 00:00:00","yyyy-MM-dd HH:mm:ss"));

and convert if needed.. maybe it will help you

Thanks

Re: Dynamic column error on date data type

mbennitt — Thu, 07 Feb 2019 14:44:20 GMT

Sorry, I meant tOracleOutput. I've tried modifying date formats but it seems like its trying to insert all the data as varchar2 which will not work.

topic Re: Dynamic column error on date data type in Talend Studio

Dynamic column error on date data type

Re: Dynamic column error on date data type

Re: Dynamic column error on date data type