One Star

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

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 - - but it was closed with no remediation.
Would appreciate any help....
Seventeen Stars

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

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.
One Star

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

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...