Oracle SQL :External Tables

EXTERNAL TABLES
We can use external table feature to access external files as if they are tables inside the database.
When we create an external table, we define its structure and location within oracle.
When we query the table, oracle reads the external table and returns the results just as if the data had been stored within the database.
No DML can be performed on external tables but they can be used for query, join and sort operations. Views and synonyms can be created against external tables
They are useful in the ETL(Extraction,Transformation and Loading) process of data warehouses

ACCESSING EXTERNAL TABLE DATA

To access external files from within oracle, we must first use the create directory command to define a directory object pointing to the external file location

create or replace directory data_dir1 as 'c:\datadir\';
Users who will access the external files must have the read and write privilege on the directory
grant read, write on directory data_dir1 to apps;

CREATE TABLE emp_external (
empno NUMBER(10),
ename VARCHAR(20))
ORGANIZATION EXTERNAL
   ( type oracle_loader
     default directory data_dir1
     access parameters
       ( records delimited BY newline
         nologfile nobadfile
         fields terminated by ',' optionally enclosed BY "'"
         missing field VALUES are NULL
           ( empno,
             ename)
       )
     location ('file.csv')
   );