Resolving the "invalid number" error using SQL Loader

Question

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

Ditto · Accepted Answer · 2019-04-15 17:27:47Z

4

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.

answered Apr 15, 2019 at 17:27

Ditto

3,3793 gold badges16 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sachin Sharma Over a year ago

I'm trying to load it into oracle table from .dat file. Table's Column 'Identity type' is of Number(10) type and yes, data has R. Is there a way to fix this error and upload it.

Ditto Over a year ago

@Sachin: sure, but as I said, it depends .. is the R a valid represnetation of the data ? If YES, then the definition of the table as NUMBER(10) is incorrect, and you should change it to a more appropriate VARCHAR2(10). If NO, then fix the data, and remove the R from it.

Sachin Sharma Over a year ago

Thanks for kind help! I've changed datatype.

Collectives™ on Stack Overflow

Resolving the "invalid number" error using SQL Loader

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related