4 Replies Latest reply: Mar 9, 2012 10:31 AM by Jason Lim RSS

    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...
        • 1. Re: SQL Loader invalid number error
          19426
          ... the AMT column is shown as CHARACTER, while my control file designates DECIMAL EXTERNAL
          That's correct, numeric external means number represented in characters,not in binary format (in other words human readable).

          http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_field_list.htm#sthref1037

          What's your default decimal delimiter, '.' or ','? Your external numbers show '.', but when your delimiter is ',', ORA-01722 is thrown.

          Werner
          • 2. Re: SQL Loader invalid number error
            834126
            Thanks, I already got this question answered in a different forum, and forgot to close this one. It turns out I had hidden newline characters at the end of each line. I loaded the numbers as VARCHAR2, stripped off the final newline, and then converted to NUMBER.

            I still don't know why the newline characters were there though. I created my .csv via Excel 2007, which I have done many times before with no problems.
            • 3. Re: SQL Loader invalid number error
              user8352403
              How you stripped off the final newline ?????

              Edited by: user8352403 on Jul 5, 2011 8:54 PM
              • 4. Re: SQL Loader invalid number error
                Jason Lim
                try adding TERMINATED BY WHITESPACE at the end of your last column in the ctl file.