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





Comments

Post a Comment

Popular posts from this blog

E-Text Report In Fusion | Types of E-Text reports

 WHAT IS Oracle Fusion E-Text Report ? An eText template is an RTF-based template that is used to generate text output  for Electronic Funds Transfer (EFT) and Electronic Data Interchange (EDI). How it generate the output for User or Bank? At runtime, BI Publisher applies this template to an input XML data file to create an output text file. that can be transmitted to a bank or other customer.  Because the output is intended for electronic communication, the eText templates must follow very specific format instructions for exact placement of data. Example :- Bank payment file , Bank MT940 File .. etc what is look and feel of this file ? Note:  There are two types of eText templates:  1. fixed-position based (EFT templates)  2. Delimiter-based (EDI templates) What IS EFT File? An EFT is an electronic transmission of financial data and payments to banks in a specific fixed-position format flat file (text). Link for download:- EFT :-  https://docs.google....
Read more

Supplier API's

API's for Create Supplier, Create Supplier Site, Create Supplier Site Contacts --======================================================================= -------------Supplier API ------------- 1. First Write a CURSOR for Required Data 2. FND_GLOBAL.APPS_INITIALIZE 3. Pass required Values in Proper way. 4. API :- For Supplier Create (pos_vendor_pub_pkg.create_vendor)    API :- For Supplier Site Create (pos_vendor_pub_pkg.create_vendor_site)    API :- For Supplier Create Contact (ap_vendor_pub_pkg.create_vendor_contact) 5. Run API 6. Checking ------------------------------  Query For APPS_INITIALIZE   --------------------------------------------- select 'USER_ID' REMARKS , user_name NAME, user_id ID from fnd_user where user_name like 'DEVELOPER' union select 'RESPONSIBILITY_ID' NAME, responsibility_name , RESPONSIBILITY_ID from fnd_responsibility_tl where responsibility_name like 'Payables Manager' union s...
Read more

How to pass default Parameter in cursor

--==================================================== --Parameterized Cursors /* Syntax of Parameterized Cursor :- CURSOR cur _ name (parameter list may be one, two, or more or Default ) IS SELECT statement; OPEN cur _ name (Parameter value) */ EMPNO ENAME HIREDATE DOB GENDER SAL DEPTNO COMM 1000 Sachin 11-Sep-85 01-Mar-20 M 25000 20 1002 Rena 01-Aug-82 11-Mar-20 F 125000 20 1008 Sonu 11-Sep-85 01-Mar-20 M 2500 10 100 1003 Chavi 25-Mar-81 15-Feb-20 F 5000 20 1006 Ravi 11-Sep-89 01-Mar-19 M 2500 10 100 1005 Vinod 18-Sep-86 01-Mar-20 ...
Read more