External Tables in SQL

 

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.

First create a sample text file named test.txt which contains

CHINA,CHI,
CANADA,CA,
BANGLADESH,BD,
INDIA,IND,
AUSTRALIA,AUS,

Next create a directory object which points to the location of the files:

> conn / as sysdba;

SQL>CREATE OR REPLACE DIRECTORY DUMP_DIR AS 'C:\temp\';
SQL> GRANT READ,WRITE ON DIRECTORY DUMP_DIR TO scott;

Next create the metadata for the external table using the CREATE TABLE..ORGANIZATION EXTERNAL syntax:


SQL> CREATE TABLE countries(
country_name VARCHAR2(50),
country_code VARCHAR2(5)
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY dump_dir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(
country_name CHAR(50),
country_code CHAR(5)
)
)
LOCATION ('test.txt')
)
PARALLEL 5
REJECT LIMIT UNLIMITED;

Table created.


SQL> select * from countries;

COUNTRY_NAME COUNT
-------------------------------------------------- -----
CHINA CHI
CANADA CA
BANGLADESH BD
INDIA IND
AUSTRALIA AUS

SQL>

TO drop the table

SQL > drop table countries;