External table Data load with Delimiter
Introduction:
to upload the data with the delimiter and special character.
Cause of the issue:
In the control file having issue to load the record from file to table with the delimiter and some special character instead of control file we can use the external table logic.
How do we solve:
create TABLE XXFA_SC_ext (
CONTRACT_TYPE VARCHAR2(90 BYTE),
START_DATE VARCHAR2(50 BYTE),
END_DATE VARCHAR2(50 BYTE),
COGNOMEN VARCHAR2(90 BYTE),
SHORT_DESCRIPTION VARCHAR2(90 BYTE),
CUSTOMER VARCHAR2(90 BYTE),
CUST_ACCOUNT VARCHAR2(90 BYTE),
CUSTOMER_SHIP_TO_NUMBER VARCHAR2(90 BYTE),
CUSTOMER_BILL_TO_NUMBER VARCHAR2(90 BYTE),
PAYMENT_TERM VARCHAR2(90 BYTE),
CONTACT_ROLE VARCHAR2(90 BYTE),
CONTACT_NAME VARCHAR2(90 BYTE),
CONTACT_ROLE2 VARCHAR2(90 BYTE),
CONTACT_NAME2 VARCHAR2(90 BYTE),
LINE_START_DATE VARCHAR2(50 BYTE),
LINE_END_DATE VARCHAR2(50 BYTE),
LINE_PRICE NUMBER,
LINE_ITEM VARCHAR2(90 BYTE),
LINE_QTY NUMBER,
LINE_CUST_PO_NUMBER VARCHAR2(90 BYTE),
LINE_INVOICE_TEXT VARCHAR2(2000 BYTE),
LINE_BILLING_FREQ VARCHAR2(90 BYTE)
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY XX_SC_dir
ACCESS PARAMETERS (RECORDS DELIMITED BY ‘\r’
SKIP 1
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
CONTRACT_TYPE ,
START_DATE ,
END_DATE ,
COGNOMEN ,
SHORT_DESCRIPTION ,
CUSTOMER ,
CUST_ACCOUNT ,
CUSTOMER_SHIP_TO_NUMBER ,
CUSTOMER_BILL_TO_NUMBER ,
PAYMENT_TERM ,
CONTACT_ROLE ,
CONTACT_NAME ,
CONTACT_ROLE2 ,
CONTACT_NAME2 ,
LINE_START_DATE ,
LINE_END_DATE ,
LINE_PRICE ,
LINE_ITEM ,
LINE_QTY ,
LINE_CUST_PO_NUMBER ,
LINE_INVOICE_TEXT ,
LINE_BILLING_FREQ ))
LOCATION (‘XX_SERVICE_CONTRACT_DATA_SHEET.csv’))
REJECT LIMIT UNLIMITED;
