External Table Concept

Definition 
External tables allow Oracle to query data that is stored outside the database in flat files. Means it maybe a txt, excel file, .csv etc.

• The ORACLE_LOADER driver can be used to access any data stored in any format that can be loaded by SQL*Loader. 
• 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.

Prerequisite

• CREATE ANY DIRECTORY privilege require
• Create oracle directory to place your flat file
• Grant read write to that

NOTE:

In Oracle we can create external table two way

• Using Oracle Loader concept
• Using Oracle Datapump concept

• ORACLE_LOADER driver is the default. Il loads data from text-datafile only.It can not perform unload. 
• ORACLE_DATAPUMP driver can perform both loads and unloads. It takes dump binary file as input and also has capability to unloads from internal tables to external tables are done by populating the binary dump files of the external table.

Pros

• Data in external tables can be queried before it is loaded into the tables.
• External tables are suitable for large data loads that may have a onetime use in the database.
• External tables eliminate the need to create staging or temporary tables.
• No need of physical space even for the largest external tables. Once the data files are loaded into OS, external tables can be created and can execute SQL queries against them.
• An external table load allows modification of the data being loaded by using SQL functions and PL/SQL functions as part of the INSERT statement that is used to create the external table.

Cons

• Till Oracle 11g there is no option to execute DML against an external table. External table’s supports SELECT only.
• No index can be created on External tables.

Syntax
Level 1

Create a directory object pointing to the location of the files
CREATE OR REPLACE DIRECTORY ext_tab_data AS '/data';

Level 2

CREATE TABLE countries_ext (
country_code VARCHAR2(5),
country_name VARCHAR2(50),
country_language VARCHAR2(50)
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_tables
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')
)
PARALLEL 5
REJECT LIMIT UNLIMITED;

By default, a log of load operations is created in the same directory as the load files, but this can be changed using the LOGFILE parameter.

Level 3

SQL> SELECT *  FROM countries_ext
3         ORDER BY country_name;

COUNT    COUNTRY_NAME      COUNTRY_LANGUAGE
-----          ----------------------------      -----------------------------
ENG           England                            English
FRA            France                              French            
GER           Germany                          German
IRE             Ireland                              English
SCO           Scotland                           English
USA            Unites States of America English
WAL            Wales                               Welsh

7 rows selected.
SQL>

Level 4
If the load files have not been saved in the appropriate directory the following result will be displayed.

SQL> SELECT *
2 FROM countries_ext
3 ORDER BY country_name;
SELECT *
*

ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file Countries1.txt in EXT_TABLES not found
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1

SQL>

This caused due to file problem in Oracle directory.

Not Completed.......