First, create a directory object that points to the location of the file on disk:

SQL> create directory example_dir as ‘/oradata/sales’;

Grant READ and WRITE on the directory object to the user (your account or application account) that is accessing the directory object.

SQL> grant read, write on directory example_dir to app_user;

Create Table

Then, fashion the script that creates the external table that will reference the file. The CREATE TABLE…ORGANIZATION EXTERNAL statement provides the database with the following information:

•     How to interpret data in the file and mapping of the data in the file to column definitions in the database

•     A DEFAULT DIRECTORY clause that identifies the directory object, which in turn specifies the directory of the file on disk

•     The LOCATION clause, which identifies the name of the file

The next statement creates a database object that looks like a table but that is able to retrieve data directly from the file:

SQL> create table example_salesdata ( sale_id   number, sale_type number, customer_id number , device varchar2(30), payment_type varchar2(30) , sale_date date, sale_price number )organization external ( type   oracle_loader default directory example_dir access parameters(records delimited by newline Fields terminated by ‘|’missing field values are null (sale_id, sale_type, customer_id , device, payment_type, sale_date char date_format date mask “mm/dd/yyyy” , sale_price))location (‘example_sales.csv’) )reject limit unlimited;

An external table named example_salesdata is created when you execute this script. Now, use SQL*Plus to view the contents of the file:

SQL> select sale_id,customer_id,payment_type, sale_date, sale_price from example_salesdata;

SALE_ID  CUSTOMER_ID PAYMENT_TYPE   SALE_DATE     SALE_PRICE