Re: date mask in SQL*Loader

On Feb 11, 1:12 pm, DA Morgan <damorgan_at_psoug.org> wrote:

> different from the situation in which
> the OP found him or her self. And, quite frankly, that is a front-end
> issue that should have been dealt with by front-end coders who should
> be required to pass information to the database in a consistent manner
> that supports data integrity.

The original poster's upstream system was an Excel spreadsheet. There is probably not much (if any) front-end coding going on. In fact, Excel has input filters that try to "recognize" when you are entering a date, and puts in a common format (e.g., "m/d/yyyy") precisely so it will be recognized as a data when viewed. That's perhaps why the values are being exported that way. One is also at the mercy of how the Excel user has his default date set.

In my expereicne, the appropriate thing to do is try to deal with the data ("can do" approach), especially if a small amount of effort will serve to load much "good" data. Rejecting the data ("no can do" approach), particularly over some detail like "2/11/2006" instead of "02112006" might be seen as unreasonable, unless of course some hard agreement exist in advance as to what comes out of Excell (i'd be surprised).

More constructive advice is that e.g., from Vince (vinnyop_at_y'hoo.com) to use the CASE statement to insert some "cleaning" logic inline with the SQL*Loader control. Even better would be to call a function or package that does thorough "recognition" of a string to date.

I've got date libraries I've used for a long time (ported to "C", Java, perl and Lisp) that have a routine that takes a string and tries very hard to make sense of it as a date. Maybe I can port it to PL/SQL and post it. But that is the kind of thing to stick in-line here.

Loading data is an ugly, thankless, arduous task everybody hates, but that is not a reason to refuse to do it, if there is value at the end.

There is only so much pushing back you can do "upstream".

--
John Hinsdale