ORA-01722 – Invalid number with SQL*Loader

ORA-01722 – Invalid number while using  SQL*Loader

Requirement:-
How to remove or generate the ORA-01722 – Invalid number OR invalid Character Error in  SQL*Loader.

Restrictions:- NO

Reasion:-
This error occurs only when
any special character or any undesired value comes in picture.
so friend to understand this

See my video on my you tube Channel "ORACLE  TECHNICAL SHOOTER"

Solution:-
1. first identify the special character or undesired character.
then write logic for replace that character.

2.write that code in .CTL file.
3.check whether it working or not .

First we genrate the error :- 
1. Create Table:-

CREATE TABLE XXSD.XXSD_INVALID_NUMBER_TL
(
  HDR_ID                    NUMBER,
  INVOICE_TYPE              VARCHAR2(100 BYTE)  NOT NULL,
  SUPPLIER_NAME             VARCHAR2(500 BYTE)  NOT NULL,
  SUPPLIER_NUMBER           VARCHAR2(500 BYTE)  NOT NULL,
  SUPPLIER_SITE             VARCHAR2(15 BYTE)   NOT NULL,
  INVOICE_DATE              VARCHAR2(15 BYTE)   NOT NULL,
  INVOICE_NUMBER            VARCHAR2(500 BYTE)  NOT NULL,
  INVOICE_AMOUNT             NUMBER              NOT NULL,
  CREATION_DATE             DATE,
  LAST_UPDATED_BY           NUMBER              DEFAULT '-1',
  LAST_UPDATE_DATE          DATE                DEFAULT SYSDATE
  )

2. CTL file:-

OPTIONS (SKIP=1)
LOAD DATA
INFILE '$XXSD_TOP/bin/XXSD_INVALID_NUMBER_FILE.csv'
APPEND
INTO TABLE XXSD.XXSD_INVALID_NUMBER_TL
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
INVOICE_TYPE  "TRIM (:INVOICE_TYPE)",
SUPPLIER_NAME "TRIM (:SUPPLIER_NAME)",
SUPPLIER_NUMBER "TRIM (:SUPPLIER_NUMBER)",
SUPPLIER_SITE "TRIM (:SUPPLIER_SITE)",
INVOICE_DATE "TRIM (:INVOICE_DATE)",
INVOICE_NUMBER "TRIM (:INVOICE_NUMBER)",
INVOICE_AMOUNT "TRIM (:INVOICE_AMOUNT)",
CREATION_DATE  SYSDATE,
HDR_ID  "XXSD_INVALID_NUMBER_SEQ.NEXTVAL"
)


3. Excel File data:-

Invoice Type	Supplier Name	Supplier Number	Supplier Site	Invoice Date	Invoice Number	Invoice Amount
STD	Manoj Kumar	1006	DELHI	10-Feb-19	31264455	299
STD	Manoj Kumar	1006	DELHI	10-Feb-19	31264474	1097
STD	Manoj Kumar	1006	DELHI	10-Feb-19	31265151	510
STD	Manoj Kumar	1006	DELHI	10-Feb-19	31265441	677
STD	Manoj Kumar	1006	DELHI	10-Feb-19	31265771	435
STD	Manoj Kumar	1006	DELHI	10-Feb-19	31266859	426
STD	Manoj Kumar	1006	DELHI	10-Feb-19	31266623	229
STD	Manoj Kumar	1006	DELHI	10-Feb-19	31266707	669
STD	Manoj Kumar	1006	DELHI	10-Feb-19	31266901	364

4. Transfer File:-

5. Run Report:-

6. See the Error:-

Analyses the error :- Find the Junk or special character which creates the problem

1. Create Table:-

CREATE TABLE XXSD.XXSD_INVALID_NUMBER_TL
(
  HDR_ID                    NUMBER,
  INVOICE_TYPE              VARCHAR2(100 BYTE)  NOT NULL,
  SUPPLIER_NAME             VARCHAR2(500 BYTE)  NOT NULL,
  SUPPLIER_NUMBER           VARCHAR2(500 BYTE)  NOT NULL,
  SUPPLIER_SITE             VARCHAR2(15 BYTE)   NOT NULL,
  INVOICE_DATE              VARCHAR2(15 BYTE)   NOT NULL,
  INVOICE_NUMBER            VARCHAR2(500 BYTE)  NOT NULL,
  INVOICE_AMOUNT             VARCHAR2(500 BYTE)  NOT NULL,
  CREATION_DATE             DATE,
  LAST_UPDATED_BY           NUMBER              DEFAULT '-1',
  LAST_UPDATE_DATE          DATE                DEFAULT SYSDATE
  )

2. CTL file:-  no change

3. Excel  file:-  no change

4. Run The Program  file:-  and now program is successful

5. check the data into the TABLE and find that character:- 

4. Solution to remove the error :- 

1. Create Table:-

CREATE TABLE XXSD.XXSD_INVALID_NUMBER_TL
(
  HDR_ID                    NUMBER,
  INVOICE_TYPE              VARCHAR2(100 BYTE)  NOT NULL,
  SUPPLIER_NAME             VARCHAR2(500 BYTE)  NOT NULL,
  SUPPLIER_NUMBER           VARCHAR2(500 BYTE)  NOT NULL,
  SUPPLIER_SITE             VARCHAR2(15 BYTE)   NOT NULL,
  INVOICE_DATE              VARCHAR2(15 BYTE)   NOT NULL,
  INVOICE_NUMBER            VARCHAR2(500 BYTE)  NOT NULL,
  INVOICE_AMOUNT             NUMBER              NOT NULL,
  CREATION_DATE             DATE,
  LAST_UPDATED_BY           NUMBER              DEFAULT '-1',
  LAST_UPDATE_DATE          DATE                DEFAULT SYSDATE
  )

2. CTL file:-

OPTIONS (SKIP=1)
LOAD DATA
INFILE '$XXSD_TOP/bin/XXSD_INVALID_NUMBER_FILE.csv'
APPEND
INTO TABLE XXSD.XXSD_INVALID_NUMBER_TL
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
INVOICE_TYPE  "TRIM (:INVOICE_TYPE)",
SUPPLIER_NAME "TRIM (:SUPPLIER_NAME)",
SUPPLIER_NUMBER "TRIM (:SUPPLIER_NUMBER)",
SUPPLIER_SITE "TRIM (:SUPPLIER_SITE)",
INVOICE_DATE "TRIM (:INVOICE_DATE)",
INVOICE_NUMBER "TRIM (:INVOICE_NUMBER)",
INVOICE_AMOUNT "TO_NUMBER(REPLACE(:INVOICE_AMOUNT,CHR(13),'') )", 
CREATION_DATE  SYSDATE,
HDR_ID  "XXSD_INVALID_NUMBER_SEQ.NEXTVAL"
)

3. Run the program:-
 

Friend My Youtube channel                   @              Oracle Technical Shooter