0

There are records in my source text file with invalid date values. The invalid date values are inconsistent in format due to manual entry. I still want to load all of these records, but I want to replace the invalid date value with a null.

Please let me know if/how this is possible via SQLLDR control file commands. I want to avoid creating any custom functions. Something simple that generally refers to errors/exceptions and that works (unlike the below) is ideal:

DATE "MM/DD/YYYY" NULLIF (FROM_DOS=EXCEPTION)

Thanks!

Beemer12
  • 1
  • 2
  • Some actual sample data would be useful here to see what we are dealing with. Edit the original post to include both good and bad date data. – Gary_W Jun 14 '18 at 02:41

1 Answers1

0

As far as I can tell, that won't go in a single pass. I'd suggest you to try a relatively simple approach:

  • load the original data "as is"
    • rows with invalid dates won't be loaded, but will end in the .BAD file
  • then modify the control file:
    • source will now be the .BAD file
    • load NULL into the date column (FILLER might help)

Alternatively, you might use the source file as an external table and write (PL/)SQL against it to load data into the target table. It allows you to actually code whatever you want, but - as you said you don't want to create a custom function (which would decide whether the input data is - or is not - a valid DATE value), I presume you'd rather skip that option.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • This is the solution that I decided to use. It addresses any dates with an invalid format (though does not address an invalid value in the appropriate format). "CASE WHEN :FROM_DOS NOT LIKE '##/##/####' THEN NULL ELSE to_date(:FROM_DOS, 'MM/DD/YYYY') END" – Beemer12 Jun 14 '18 at 13:22
  • If the target column is of the DATE datatype, this will fail for input data in valid format (##/##/####) but invalid value (such as 67/43/0987), so ... – Littlefoot Jun 14 '18 at 18:42