-1

I am trying to load data into some tables using sql loader. One of the columns, EXPIRATION_DATE, contains a date where most of the entries are in the form of mm/dd/yyyy, but there are a few that are in the form of dd-mon-yy. My current .ctl file defines the incoming data as EXPIRATION_DATE DATE "MM/DD/YYYY". When I run it I get an error Record 1: Rejected - Error on table ORD9_DISC_PER_FEAT_PLAN_IMPORT, column EXPIRATION_DATE. ORA-01858: a non-numeric character was found where a numeric was expected

How can I fix this problem?

Here is a snap shot of the data

cdm
  • 1
  • 1
  • Your sample data includes date format dd-mon-yyyy, but you don't mention it. There may be a way to handle multiple date formats within the same column, but its probably easier to get it into a consistent format before running through SQLLDR. – alexherm Jul 17 '20 at 17:51
  • Ideally whoever is creating the data file should correct the data to be a consistent format. If that isn't possible, then you may have to do something like load the data into a staging table as varchar2 (i.e. text), then massage it into the right format using a series of "insert as select" statements with different to_date filters. You could also have SQLLoader put the "bad" records into a reject file and then process it separately with a secondary control file with the different date format. – pmdba Jul 17 '20 at 17:53
  • 1
    Lesson: when you keep DATE as any kind of character type, there simply is no way, zero, nada, null, way of guaranteeing accuracy. Given the "date" of '11/12/10', what is the day, what is the month, and what is the year? How can you be sure? Even given '05/04/2020', is that May 4, or April 5? Even if you have a "standard" of what is _should_ be, there simply are _no_ guarantees. – EdStevens Jul 17 '20 at 18:38
  • I would recommend to create your own to_date function, so you could use it in your data load – KOBER Jul 21 '20 at 09:48

1 Answers1

0

This is how I ultimately got around the problem

    EXPIRATION_DATE "DECODE(INSTR(:EXPIRATION_DATE, '- 
    '),0,to_date(:EXPIRATION_DATE,'mm/dd/yyyy'),to_date(:EXPIRATION_DATE,'dd-mon- 
    yyyy'))"
cdm
  • 1
  • 1