5 Ответы Последний ответ: 23.12.2008 19:27, автор: Walter Fernández

    Invalid Number Error for Decimal Field While Loading Data

    585578
      I am loading a delimited text file using the SQL* loader however I am reciving an error in my decimal fields. When a decimal field only has leading zeros before the decimal point I receive invalid number error. Below will clarify:

      i.e.) 00000000.30 [*Invalid number*]
      i.e.) 00046567.45 [*Valid number*]
      i.e.) 00000001.00 [*Valid number*]

      I've tried setting the precision/scale in the table, tried declaring it a decimal field instead of number, none of these methods fixed the issue. Any help I would really appreciate.
        POLICY_NUMBER             NUMBER,
        EFFECTIVE_DATE     DATE "YYYYMMDD"          NULLIF EFFECTIVE_DATE = '',
        TRANSACTION_DATE     DATE "YYYYMMDD",
        TRANSACTION_AMOUNT     DECIMAL EXTERNAL,   -- Tried TRANSACTION_AMOUNT DECIMAL EXTERNAL (10)  & TRANSACTION_AMOUNT NUMBER
        MF_TRX_CODE          NUMBER,
        USER_ID          CHAR,
        GROUP_NUMBER          NUMBER,
        EXPIRATION_DATE     DATE "YYYYMMDD"          NULLIF EXPIRATION_DATE = '',
        BILL_NUMBER          NUMBER,
      Any help is greatly appreciated. Thanks before hand.
        • 1. Re: Invalid Number Error for Decimal Field While Loading Data
          Walter Fernández
          Hi,

          Could you post some data? And don't forget this forum [Export/Import/SQL Loader & External Tables|http://forums.oracle.com/forums/forum.jspa?forumID=732]

          Regards,
          • 2. Re: Invalid Number Error for Decimal Field While Loading Data
            585578
            08873851| |20081220| 00011000.00|412| |0082| |J516904|
            11362811| |20081220| 00000293.37|412| |0493| |J516930|
            11704905| |20081220| 00002000.00|412| |0090| |J516934|
            13415672| |20081220| 00001134.32|412| |0505| |J516976|
            14112357| |20081220| 00000000.30|412| |0090| |J517000|



            Error Output

            Record 5: Rejected - Error on table NYSFMS.NYSIF_WCF_PREMIUM_TRANS, column TRANSACTION_AMOUNT.
            ORA-01722: invalid number
            • 3. Re: Invalid Number Error for Decimal Field While Loading Data
              Walter Fernández
              Hi,
              Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 
              Connected as hr
               
              SQL> 
              SQL> CREATE TABLE emp_load
                2    (TRANSACTION_AMOUNT number(9,2))
                3  ORGANIZATION EXTERNAL
                4    (TYPE ORACLE_LOADER
                5     DEFAULT DIRECTORY ext_files
                6     ACCESS PARAMETERS
                7       (RECORDS DELIMITED BY NEWLINE
                8        FIELDS (
                9                TRANSACTION_AMOUNT   CHAR(11)
               10               )
               11       )
               12     LOCATION ('test1.dat')
               13    );
               
              Table created
               
              SQL> SELECT * FROM EMP_LOAD;
               
              TRANSACTION_AMOUNT
              ------------------
                        11000,00
                          293,37
                         2000,00
                         1134,32
                            0,30
               
              SQL> 
              And my dat file is...
              00011000.00
              00000293.37
              00002000.00
              00001134.32
              00000000.30
              Regards,
              • 4. Re: Invalid Number Error for Decimal Field While Loading Data
                585578
                It is required I load through a SQL* Loader. Here is a larger snipet of my loader program. The program executes via concurrent request in Oralce Financials.
                OPTIONS (ROWS=1000, ERRORS=10000)
                Load Data
                INFILE *
                APPEND
                INTO TABLE NYSFMS.NYSIF_WCF_PREMIUM_TRANS
                WHEN (MF_TRX_CODE <> '548')
                FIELDS TERMINATED BY '|'
                TRAILING NULLCOLS
                (
                  POLICY_NUMBER             NUMBER,
                  EFFECTIVE_DATE     DATE "YYYYMMDD"          NULLIF EFFECTIVE_DATE = '',
                  TRANSACTION_DATE     DATE "YYYYMMDD",
                  TRANSACTION_AMOUNT     DECIMAL EXTERNAL,   -- Tried TRANSACTION_AMOUNT DECIMAL EXTERNAL (10)  & TRANSACTION_AMOUNT NUMBER
                  MF_TRX_CODE          NUMBER,
                  USER_ID          CHAR,
                  GROUP_NUMBER          NUMBER,
                  EXPIRATION_DATE     DATE "YYYYMMDD"          NULLIF EXPIRATION_DATE = '',
                  BILL_NUMBER          NUMBER,
                . 
                .
                .
                .
                )
                • 5. Re: Invalid Number Error for Decimal Field While Loading Data
                  Walter Fernández
                  Hi,
                  Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 
                  Connected as hr
                   
                  SQL> SELECT * FROM TEST;
                   
                  TRANSACTION_AMOUNT
                  ------------------
                   
                  SQL> SELECT * FROM TEST;
                   
                  TRANSACTION_AMOUNT
                  ------------------
                            11000,00
                              293,37
                             2000,00
                             1134,32
                                0,30
                   
                  SQL>
                  Between the selects I loaded the table with sql*loader using...
                  Load Data
                  INFILE *
                  APPEND
                  INTO TABLE TEST
                  FIELDS TERMINATED BY '|'
                  TRAILING NULLCOLS
                  (
                    TRANSACTION_AMOUNT     DECIMAL EXTERNAL
                  )
                  BEGINDATA
                  00011000.00
                  00000293.37
                  00002000.00
                  00001134.32
                  00000000.30
                  The log is
                  SQL*Loader: Release 10.2.0.1.0 - Production on Tue Dec 23 17:23:47 2008
                  
                  Copyright (c) 1982, 2005, Oracle.  All rights reserved.
                  
                  Control File:   test.ctl
                  Data File:      test.ctl
                    Bad File:     test.bad
                    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 TEST, loaded from every logical record.
                  Insert option in effect for this table: APPEND
                  TRAILING NULLCOLS option in effect
                  
                     Column Name                  Position   Len  Term Encl Datatype
                  ------------------------------ ---------- ----- ---- ---- ---------------------
                  TRANSACTION_AMOUNT                  FIRST     *   |       CHARACTER            
                  
                  
                  Table TEST:
                    5 Rows successfully loaded.
                    0 Rows not loaded due to data errors.
                    0 Rows not loaded because all WHEN clauses were failed.
                    0 Rows not loaded because all fields were null.
                  
                  
                  Space allocated for bind array:                  16512 bytes(64 rows)
                  Read   buffer bytes: 1048576
                  
                  Total logical records skipped:          0
                  Total logical records read:             5
                  Total logical records rejected:         0
                  Total logical records discarded:        0
                  
                  Run began on Tue Dec 23 17:23:47 2008
                  Run ended on Tue Dec 23 17:23:50 2008
                  
                  Elapsed time was:     00:00:02.86
                  CPU time was:         00:00:00.06
                  Regards,