4 Replies Latest reply on Mar 9, 2012 4:31 PM by Jason Lim

    SQL Loader invalid number error

    834126
      I am trying to load a .csv using SQL*Loader and every row is rejected with an invalid number error. The .csv column being loaded contains only numeric characters, the control file specifies datatype DECIMAL EXTERNAL, and the datatype of the column in the target table is NUMBER(8,2). I can't figure out why it won't accept the data.

      Here is the control file:

      OPTIONS (SKIP=1)
      LOAD DATA
      INFILE 'shi_claims.csv'
      REPLACE INTO TABLE SHI_CLAIMS
      FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
      TRAILING NULLCOLS
      (
      ID CHAR
      , AMT DECIMAL EXTERNAL
      )

      Here is the first few lines of data in the .csv (note the control file skips the first line):

      EmployeeID,PaidAmnt
      100550393,0
      100709611,120.04
      100795648,90
      100795648,5.24

      Here is the structure of the table I am trying to load:

      CREATE TABLE UST_GLOBAL.SHI_CLAIMS
      (
      ID VARCHAR2(9 CHAR),
      AMT NUMBER(8,2)
      )

      Here is the .log file that results. Every row winds up in the .bad file.

      SQL*Loader: Release 10.2.0.4.0 - Production on Mon Mar 7 16:34:09 2011

      Copyright (c) 1982, 2007, Oracle. All rights reserved.

      Control File: /u02/sct/ust/shi_claims.ctl
      Data File: ././data.7609732
      Bad File: 7609732.bad
      Discard File: discard.7609732
      (Allow all discards)

      Number to load: ALL
      Number to skip: 1
      Errors allowed: 50
      Bind array: 64 rows, maximum of 256000 bytes
      Continuation: none specified
      Path used: Conventional

      Table SHI_CLAIMS, loaded from every logical record.
      Insert option in effect for this table: REPLACE TRAILING NULLCOLS option in effect

      Column Name Position Len Term Encl Datatype
      ------------------------------ ---------- ----- ---- ---- ---------------------
      ID FIRST * , O(") CHARACTER
      AMT NEXT * , O(") CHARACTER

      Record 1: Rejected - Error on table SHI_CLAIMS, column AMT.
      ORA-01722: invalid number

      Record 2: Rejected - Error on table SHI_CLAIMS, column AMT.
      ORA-01722: invalid number

      (...and so on for every row).

      I note that in the copy of the control file that appears in the .log file, the AMT column is shown as CHARACTER, while my control file designates DECIMAL EXTERNAL. Why, and could that be part of the problem?

      Thanks for any help...