I receive the following error on all my rows:
Record 1: Rejected - Error on table INSPECTIONS, column TIMESTAMP_CREATED.
ORA-01858: a non-numeric character was found where a numeric was expected
when I'm attempting to load data from a CSV file into 1 table.
My CTL file is:
LOAD DATA
INFILE *
BADFILE 'U:\PONYA\LOG\load_inspections_no_ID.bad'
DISCARDFILE 'U:\PONYA\LOG\load_inspections_no_ID.dis'
INTO TABLE INSPECTIONS
TRUNCATE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(INSPECTION_DATE "TO_DATE (:INSPECTION_DATE,'MM/DD/YYYY')",
STATUS CHAR,
COMMENTS CHAR,
INSPECTION_ID INTEGER,
INSPECTOR_USERNAME CHAR,
CREATED_BY CHAR,
TIMESTAMP_CREATED CHAR "TO_DATE (:TIMESTAMP_CREATED,'MM/DD/YYYY')",
UPDATED_BY CHAR,
TIMESTAMP_UPDATE CHAR "TO_DATE (:TIMESTAMP_UPDATE,'MM/DD/YYYY')",
FACILITY CHAR,
PLATE CHAR,
STICKER CHAR)
The DATA file is in CSV format. An except of the data is:
1/7/1982,P,,1028,unknown,unknown,1/7/1982,,,EWR,Z98850*,Z98850*01/07/1982
1/8/1982,P,,1,unknown,unknown,1/8/1982,,,EWR,A50045*,A50045*01/08/1982
1/8/1982,P,,5,unknown,unknown,1/8/1982,,,EWR,A50208*,A50208*01/08/1982
The table definition is:
CREATE TABLE "INSPECTIONS"
( "ID" NUMBER NOT NULL ENABLE,
"VEH_ID" NUMBER,
"INSPECTION_DATE" DATE,
"STATUS" VARCHAR2(1 BYTE),
"COMMENTS" VARCHAR2(1000 BYTE),
"INSPECTION_ID" NUMBER,
"INSPECTOR_USERNAME" VARCHAR2(40 BYTE),
"STICKER_ID" NUMBER,
"CREATED_BY" VARCHAR2(100 BYTE),
"TIMESTAMP_CREATED" TIMESTAMP (6),
"UPDATED_BY" VARCHAR2(100 BYTE),
"TIMESTAMP_UPDATE" TIMESTAMP (6),
"FACILITY" VARCHAR2(4 BYTE),
"PLATE" VARCHAR2(7 BYTE),
"STICKER" VARCHAR2(17 BYTE),
CONSTRAINT "INSPECT_STATUS_CHECK" CHECK (STATUS in ('P','F')) ENABLE,
CONSTRAINT "INSPECTIONS_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PONYADATA" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PONYADATA" ;
The ID field is populated from a sequence in a Before Insert Trigger.
Why am I getting the error? The data matches the format mask. I've used the same format mask in another CTL for a different table
Robert