External Table - Excel Base Table - External Source table

To Im going to discuss how to load data from excel (CSV) to oracle table directly, In other word External tables allow users to query data that is stored outside the database in a flat file.

 Requirement

1.Database directry to park the external file.(need to create db directry in oracle as well ex. XX_STG) .here is the table script for the table

cheers !!!

CREATE TABLE XX_AR_INV_STG

(   HOTEL             VARCHAR2(30 BYTE),

    CUST_ACCOUNT_CODE VARCHAR2(30 BYTE),     

    CUST_SITE_CODE    VARCHAR2(30 BYTE),

    INVOICE_DATE      VARCHAR2(30 BYTE),

    INVOICE_NUMBER    VARCHAR2(30 BYTE),

    DISCOUNTED_AMT_WITHOUT_TAX  Number, 

    VAT_AMOUNT                  Number,  

    NBT_AMOUNT                  Number,                      

    TDL_AMOUNT                  Number,    

    SERVICE_CHRG_AMT            Number,    

    INV_TOTAL                   Number,

    TOUR_ID         VARCHAR2(30 BYTE),

    ARRIVAL_DATE    VARCHAR2(30 BYTE),

    DEPARTURE_DATE  VARCHAR2(30 BYTE),  

    PAX_COUNT       VARCHAR2(30 BYTE),

    FIT_CUST_NAME   VARCHAR2(150 BYTE), 

    REGION_CHANNL   VARCHAR2(30 BYTE) 

)

ORGANIZATION EXTERNAL

  (  TYPE ORACLE_LOADER

     DEFAULT DIRECTORY KHC_STG

     ACCESS PARAMETERS 

       ( RECORDS DELIMITED BY NEWLINE

   FIELDS TERMINATED BY ','

  )

     LOCATION (XX_STG:'xxxx.csv')

  )

REJECT LIMIT 0

NOPARALLEL

NOMONITORING;

/

alter table DMSORA.XX_AR_INV_STG reject limit unlimited;