topic Dynamic Schema Oracle / CSV file issues (ORA-01843: not a valid month) in Talend Studio

Dynamic Schema Oracle / CSV file issues (ORA-01843: not a valid month)

willm1 — Sat, 16 Nov 2024 11:37:16 GMT

Hello,
I've designed a simple job to read a CSV file dynamically, use the dynamic schema option, and load the data into an Oracle database table.
But I'm running into "ORA-01843: not a valid month" error because the dynamic schema functionality is not correctly parsing the date field, even though my date pattern matches the format of the date in the CSV file. My date is of format "dd-MM-yyyy'T'HH:mm:ss", and my date matches that format. However, my job fails... I'm tried it all ways - dd-MM, MM-dd, yyyy-dd-MM, yyyy-MM-dd etc... - no luck...
I found a jira on this issue - https://jira.talendforge.org/browse/TDI-21970 - but it was closed with no remediation.
Would appreciate any help....
Thanks...

Re: Dynamic Schema Oracle / CSV file issues (ORA-01843: not a valid month)

Anonymous — Thu, 05 Jun 2014 20:17:06 GMT

At first I would send the output to a tLogRow to inspect what you get.
You example text let me think the date pattern does not need the T in the middle. "dd-MM-yyyy HH:mm:ss" should be fine.
It is a bit strange use case. Your read in a couple of files and creates the tables according to the file structure? What if the file change a little bit (e.g. one column more or less?).
I would never do such things. I would make a pre processing job which inspects the files and notifies structure changes and build all necessary tables.

Re: Dynamic Schema Oracle / CSV file issues (ORA-01843: not a valid month)

willm1 — Fri, 06 Jun 2014 19:02:31 GMT

Thanks @jlolling... I tried without the 'T' and it didn't work either... I'm going to implement some other workarounds suggested by Bekwam.
About this pattern - isn't that what dynamic schema are all about? Wouldn't you say the same of any job that uses built-in or repo schemas? We design to particular schemas in our jobs and our jobs would naturally break when the file schema differs from the one our job is using. So the expectation is that we have some sort of baseline... If we had to preprocess every kind of file, wouldn't that be overhead? In your case, you'd have the benefit of exiting gracefully with a nice clean message. In my case, it would be the nasty stacktrace but I'd have spared myself the effort of building and executing preprocessing...
Thanks for responding... I appreciate it...
Will

Re: Dynamic Schema Oracle / CSV file issues (ORA-01843: not a valid month)

Anonymous — Sun, 15 Mar 2020 11:24:10 GMT

Hi Team,

I am also getting same error , while loading data in oracle database .

For the testing purpose i extracted the data from the table and created a file.

in the next job i tried to load that file into the same table using dynamic schema but it saying Not a valid month.

My use case is - i am having 16 files with different different schemas and want to create a job which load these files in oracle.