Introduction:
This blog has the SQL loader using external Table.
Cause of the issue:
Business wants to load the data with a field having multiple line we can use this .
How do we solve:
Create an oracle directory like below with sys user and give rights to the appropriate schema and move the file into the directory.
CREATE OR REPLACE DIRECTORY
XX_SC_DIR AS
‘/ebiz/GFINUAT/XX_SC_EXT/Service_Contracts’;
Create external table using the above directory.
CREATE TABLE APPS.XX_SC_EXT
(
COGNOMEN VARCHAR2(90 BYTE),
START_DATE VARCHAR2(50 BYTE),
END_DATE VARCHAR2(50 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),
CONTRACT_TYPE 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 CLOB,
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
(
cognomen ,
start_date ,
end_date ,
short_description ,
customer ,
cust_account ,
customer_ship_to_number ,
customer_bill_to_number ,
contract_type ,
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 char(4000) ,
line_billing_freq
) )
LOCATION (XX_SC_DIR:’XX_SERVICE_CONTRACT_DATA_SHEET.csv’)
)
REJECT LIMIT UNLIMITED
NOPARALLEL
By default this char(150) we can change if we need more.
Select the record using the below query
select * from apps.xx_sc_ext
