This is the sample script to create the oracle external table to access the data from the file.
prompt
prompt creating TABLE XX_SC_EXT …..
CREATE TABLE “XX”.”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),
“BILLING_DATE” VARCHAR2(50 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 ,
billing_date ,
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_SERVICE_CONTRACT_DATA_SHEET.csv’
)
)
REJECT LIMIT UNLIMITED ;
Recent Posts