I try to load data from a file into a table in Oracle, but I get an error only in the first record, the second record gets Inserted. Record 1: Rejected - error on the table Ing_Details, column INGREDIENT_ID. ORA-01722: invalid number
This is a management file
LOAD DATA
INFILE 'C:\Users\Appdata\Desktop\SQl Loader\ing1.txt'
BADFILE 'C:\Users\Appdata\Desktop\SQl Loader\bad_orders.txt'
INTO TABLE Ing_Details
FIELDS
TERMINATED BY "\t"
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
INGREDIENT_ID NULLIF INGREDIENT_TYPE_ID=BLANKS,
INGREDIENT_TYPE_ID NULLIF INGREDIENT_TYPE_ID=BLANKS,
Status NULLIF Status=BLANKS,
Organic NULLIF Organic=BLANKS
)
Table structure
Ingredient_ID NUMBER NOT NULL,
Ingredient_Type_ID NUMBER NOT NULL,
Status NUMBER (10) NOT NULL,
Organic NUMBER (10) NOT NULL
log file:
SQL*Loader: Release 11.2.0.2.0 - Production on Mon Jul 11 12:25:59 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Control File: C:\Users\Appdata\Desktop\ingredient2.ctl
Data File: C:\Users\Appdata\Desktop\SQl Loader\ing1.csv
Bad File: C:\Users\Appdata\Desktop\SQl Loader\bad_orders.txt
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table Ing_Details, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
INGREDIENT_ID FIRST * , O(") CHARACTER
NULL if INGREDIENT_TYPE_ID = BLANKS
INGREDIENT_TYPE_ID NEXT * , O(") CHARACTER
NULL if INGREDIENT_TYPE_ID = BLANKS
STATUS NEXT * , O(") CHARACTER
NULL if STATUS = BLANKS
ORGANIC NEXT * , O(") CHARACTER
NULL if ORGANIC = BLANKS
Record 1: Rejected - Error on table Ing_Details, column INGREDIENT_ID.
ORA-01722: invalid number
source
share