External Tables In Oracle

External tables is a very useful feature in Oracle that allows Oracle to query data that is stored outside the database in flat files.It uses the ORACLE_LOADER driver which can 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 Data Warehousing Enviroments since the data doesn't need to be staged but can be queried in parallel.
- They should not be used for frequently queried tables.

To use External Tables in Oracle:

1- You must first create a directory to place the files you want to load into database in.

SQL> CONNECT / AS SYSDBA
SQL> CREATE OR REPLACE DIRECTORY EXTENAL_TABLES_DIR AS 'C:\TEST_EXT_TABLES';

2- Create the metadata for the External Table using the CREATE TABLE..ORGANIZATION EXTERNAL command

SQL> CREATE TABLE SCOTT.EMP_EXT_TABLE (
EMP_NAME VARCHAR2 (128),
EMP_SALARY NUMBER,
EMP_JOB VARCHAR2 (128)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXTENAL_TABLES_DIR
ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ' ' )
LOCATION ( 'emp_data.log' )
)
REJECT LIMIT UNLIMITED;

Where:
- DEFAULT DIRECTORY: the directory name you created in Step1.
- ACCESS PARAMETERS: the column and line separators for data in the file.
- LOCATION: the name of the file containing data.
- REJECT LIMIT: is set to UNLIMITED for SELECT on table not to fail.

3- You can query the data in the SCOTT.EMP_EXT_TABLE table
SQL> SELECT * FROM SCOTT.EMP_EXT_TABLE;

4- Now you can create views against this table like this:
SQL> CREATE OR REPLACE VIEW SCOTT.EMP_EXT_TABLE_VU AS
SELECT * FROM SCOTT.EMP_EXT_TABLE;