CODEWITHSUNDEEP
oracle-databasesql-loader
Sachin Sharma
Sachin Sharma

Reputation: 382

Resolving the "invalid number" error using SQL Loader

I'm trying to load the following data to into a table, using the .fmt file below.

600-0093889-000R06169024159082020190220000000067.36   

600-0134686-000R06169024159082620190214000000123.33  

600-0134686-000R06169024159069520190214000000123.33- 

600-0093889-000R06169024159082620190220000000067.36- 

600-0122474-000R06169024159069520190227000001000.00- 

500-0433040-000R06168993159082620190227000000250.00- 

550-0046451-000R06168984159069520190227000000200.00- 

600-0124821-000R06169024159069520190227000000925.00- 

600-0107803-000R06165180159082620190227000000372.12-

My .fmt file is as following: 

LOAD DATA 
APPEND INTO TABLE USB.INFOLEASE_CHG_OFF_0228
(
ID_NUMBER                  POSITION (  1 : 15) CHAR,
IDENTITY_CODE              POSITION ( 16 : 25) CHAR,
ID_RECORD_NBR              POSITION ( 26 : 27) CHAR,
DLY_AS_OF_DATE             POSITION ( 28 : 35) DATE 'YYYYMMDD',
AS_OF_DATE                 POSITION ( 36 : 43) DATE 'YYYYMMDD',
GL_ACCOUNT_ID          POSITION ( 68 : 74) DATE 'YYYYMMDD',
ORG_UNIT_ID            POSITION ( 64 : 67) CHAR,
ILC_CONTRACT_NUMBER        POSITION ( 44 : 58) CHAR,
ILC_CHGOFF_RCVY_IND        POSITION ( 59 : 59) CHAR,
ILC_BANK                   POSITION ( 60 : 63) CHAR,
ILC_OFFICE                 POSITION ( 64 : 67) CHAR,
ILC_ACCOUNT                POSITION ( 68 : 74) CHAR,
ILC_POSTING_DATE           POSITION ( 75 : 82) DATE 'YYYYMMDD',
ILC_AMOUNT                 POSITION ( 83 : 95) CHAR
)

Here's the table schema:

ColumnName  ColID   PK  IndexPos    Null    DataType
IDENTITY_CODE   1           Y   NUMBER (10)
ID_NUMBER   2           Y   VARCHAR2 (30 Byte)
ID_RECORD_NBR   3           Y   NUMBER (2)
GL_ACCOUNT_ID   4           Y   NUMBER (14)
ORG_UNIT_ID 5           Y   NUMBER (14)
AS_OF_DATE  6           Y   DATE
DLY_AS_OF_DATE  7           Y   DATE
ILC_CONTRACT_NUMBER 8           Y   CHAR (15 Byte)
ILC_CHGOFF_RCVY_IND 9           Y   CHAR (1 Byte)
ILC_BANK    10          Y   CHAR (4 Byte)
ILC_OFFICE  11          Y   CHAR (4 Byte)
ILC_ACCOUNT 12          Y   CHAR (7 Byte)
ILC_POSTING_DATE    13          Y   DATE
ILC_AMOUNT  14          Y   NUMBER (13

When I try to load the data, I get the following error:

Record 35: Discarded - all columns null. Record 1: Rejected - Error on table USB.INFOLEASE_CHG_OFF_0228, column IDENTITY_CODE. ORA-01722: invalid number

Record 2: Rejected - Error on table USB.INFOLEASE_CHG_OFF_0228, column IDENTITY_CODE. ORA-01722: invalid number

Record 3: Rejected - Error on table USB.INFOLEASE_CHG_OFF_0228, column IDENTITY_CODE. ORA-01722: invalid number

Record 4: Rejected - Error on table USB.INFOLEASE_CHG_OFF_0228, column IDENTITY_CODE. ORA-01722: invalid number

Record 5: Rejected - Error on table USB.INFOLEASE_CHG_OFF_0228, column IDENTITY_CODE. ORA-01722: invalid number

Record 6: Rejected - Error on table USB.INFOLEASE_CHG_OFF_0228, column IDENTITY_CODE. ORA-01722: invalid number

Record 7: Rejected - Error on table USB.INFOLEASE_CHG_OFF_0228, column IDENTITY_CODE. ORA-01722: invalid number

Record 8: Rejected - Error on table USB.INFOLEASE_CHG_OFF_0228, column IDENTITY_CODE. ORA-01722: invalid number

Record 9: Rejected - Error on table USB.INFOLEASE_CHG_OFF_0228, column IDENTITY_CODE. ORA-01722: invalid number

Record 10: Rejected - Error on table USB.INFOLEASE_CHG_OFF_0228, column IDENTITY_CODE. ORA-01722: invalid number

Record 11: Rejected - Error on table USB.INFOLEASE_CHG_OFF_0228, column IDENTITY_CODE. ORA-01722: invalid number

Record 12: Rejected - Error on table USB.INFOLEASE_CHG_OFF_0228, column IDENTITY_CODE. ORA-01722: invalid number

Upvotes: 1

Views: 2982

Answers (1)

Ditto
Ditto

Reputation: 3344

Because at position 16 .. you have the letter "R" which not a valid character for a NUMBER type:

Your data:

data: 600-0093889-000R06169024159082020190220000000067.36   
pos : 12345678901234567890
                     ^
                    position 16 has the letter R

Your SQL Loader definition .. shows :

IDENTITY_CODE              POSITION ( 16 : 25) CHAR,

that position 16 belongs to IDENTITY_CODE

and your table definition:

IDENTITY_CODE   1           Y   NUMBER (10)

is NUMBER .. which results in the error you are experiencing.

I'm not sure how to recommend fixing this, and I'm not entirely sure what you're doing, or the end purpose .. so I don't know if the data is correct/incorrect, or the table definition is correct/incorrect, etc.

Upvotes: 4

Related Questions