CODEWITHSUNDEEP
oracle-database
pramod
pramod

Reputation: 51

SQL LOADER ERROR ON FIRST RECORD

I am trying to load data from a file into a table in Oracle but i am receiving a error only on first record ,second record is getting Inserted. Record 1: Rejected - Error on table Ing_Details, column INGREDIENT_ID. ORA-01722: invalid number

This is the Control 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

Upvotes: 5

Views: 2453

Answers (1)

Jason C.
Jason C.

Reputation: 104

Check the encoding on the data file. SQLLDR seems to have issues with BOM'd UTF-8 files, as it adds 3 bytes to the beginning of the file (UTF-8 vs UTF-8 w/o BOM). These bytes are included in the first defined field (in accordance with the .ctl file), which Oracle can then not convert into a NUMBER. By adding and skipping a blank header row in the data file, you have essentially nullified the effect of the 3 extra bytes.

An easy way to check encoding on the file is to open the file in Notepad++ and open the Encoding menu item.

Notepad++

If necessary, select Encode UTF-8 without BOM to change the file encoding.

Upvotes: 3

Related Questions