External Tables

 external tables

External tables allow Oracle to query data that is stored outside the database in flat files.

 The ORACLE_LOADER driver can be used to access any data stored in any format that can be loaded by SQL*Loader.

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 process of data warehouses since the data doesn't need to be staged and can be queried in parallel. 

They should not be used for frequently queried tables.

-- 

create two file Countries1.txt and Countries2.txt in the folder  

This is to be done on the file system

E:\data .

conn / as sysdba

CREATE OR REPLACE DIRECTORY ext_tab_data AS 'E:\data'; 

CREATE TABLE countries_ext (

      country_code      VARCHAR2(5),

      country_name      VARCHAR2(50),

      country_language  VARCHAR2(50)

    )

    ORGANIZATION EXTERNAL (

      TYPE ORACLE_LOADER

      DEFAULT DIRECTORY ext_tab_data

      ACCESS PARAMETERS (

       RECORDS DELIMITED BY NEWLINE

       FIELDS TERMINATED BY ','

       MISSING FIELD VALUES ARE NULL

       (

         country_code      CHAR(5),

         country_name      CHAR(50),

         country_language  CHAR(50)

       )

     )

     LOCATION ('Countries1.txt','Countries2.txt')

   )

   REJECT LIMIT UNLIMITED;

check the data that has been read from external tables( file system)

select * from countries_ext;