External Tables

An external table looks like a regular Oracle table but its data lives in files on the operating system, not inside the database. You can SELECT * FROM ext_csv_data and get back rows parsed from a CSV file on disk — without ever loading the data into the database.

External tables are Oracle's clean alternative to running SQL*Loader, file scraping scripts, or external ETL tools for many common load and read workloads.

What External Tables Are Good For

1. Loading data from CSV / flat files

The classic ETL pattern: dump from one system into a flat file, copy the file to an Oracle directory, define an external table over it, then INSERT INTO target SELECT * FROM ext_table. Simpler than SQL*Loader, no separate utility to run.

2. Querying log files without importing

Tail a CSV produced by some other process and run ad-hoc SQL against it. The file stays on disk; no storage cost inside the database.

3. Sharing data between databases

Database A exports a file using ORACLE_DATAPUMP, copies it to a directory accessible by database B, which then reads it via an external table. No DB link required.

4. Backing data into the database lazily

External tables can be the source of a materialised view that refreshes on demand — incremental data loads without the SQL*Loader machinery.

The Two Access Drivers

Oracle ships two drivers for external tables:

ORACLE_LOADER is read-only (no INSERT into the external table). ORACLE_DATAPUMP can write — useful for exporting query results into a file Oracle can re-read elsewhere.

Directory Objects

External tables can only read files inside a directory object — a named alias for an OS path. Directories are how Oracle controls which OS paths the database may touch.

-- DBA creates the directory:
CREATE OR REPLACE DIRECTORY ext_data_dir AS '/u01/data/external';

-- Grant read access to a specific user:
GRANT READ ON DIRECTORY ext_data_dir TO hr_user;

-- For writing (Datapump or BAD/LOG files):
GRANT WRITE ON DIRECTORY ext_data_dir TO hr_user;

A directory is just a name pointing at a path. It does not create the OS folder — the folder must already exist with appropriate permissions for the Oracle OS user.

Inspect directories:

SELECT directory_name, directory_path FROM all_directories;

Defining an External Table with ORACLE_LOADER

The minimal form for a CSV file:

CREATE TABLE ext_employees (
  employee_id   NUMBER,
  first_name    VARCHAR2(50),
  last_name     VARCHAR2(50),
  email         VARCHAR2(100),
  hire_date     DATE,
  salary        NUMBER
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_data_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    SKIP 1                              -- skip header row
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    MISSING FIELD VALUES ARE NULL
    (
      employee_id,
      first_name,
      last_name,
      email,
      hire_date DATE 'YYYY-MM-DD',
      salary
    )
  )
  LOCATION ('employees.csv')
)
REJECT LIMIT UNLIMITED;

Now SELECT * FROM ext_employees parses employees.csv from /u01/data/external and returns rows.

Key Access Parameters

Field Type Conversions

The column types defined in the CREATE TABLE come from Oracle's type system; the source file is always text. Specify conversion patterns when needed:

(
  order_id      INTEGER EXTERNAL,
  order_date    DATE 'YYYY-MM-DD HH24:MI:SS',
  amount        DECIMAL EXTERNAL,
  description   CHAR(200)
)

INTEGER EXTERNAL and DECIMAL EXTERNAL mean "the file has the value as a text string; convert to numeric". Without EXTERNAL, ORACLE_LOADER expects the value in binary format.

Handling Bad Records

By default, parsing errors abort the query. Three files capture rejected data:

CREATE TABLE ext_employees (
  ...
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_data_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    BADFILE      ext_data_dir : 'employees.bad'
    LOGFILE      ext_data_dir : 'employees.log'
    DISCARDFILE  ext_data_dir : 'employees.dis'
    FIELDS TERMINATED BY ','
    ( ... )
  )
  LOCATION ('employees.csv')
)
REJECT LIMIT 100;       -- tolerate up to 100 bad rows

Per row that fails: the parsed input goes to BADFILE, an error message to LOGFILE. Inspect these after every load.

Loading from External Table into a Real Table

The canonical ETL pattern:

-- Once: define the external table over the file format
CREATE TABLE ext_employees (...);

-- For each load:
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary)
SELECT employee_id, first_name, last_name, email, hire_date, salary
FROM   ext_employees;

COMMIT;

For large loads, use the APPEND hint to bypass the buffer cache and direct-path insert:

INSERT /*+ APPEND */ INTO employees SELECT * FROM ext_employees;

This is significantly faster but the rows aren't visible until COMMIT and the table is briefly locked.

Multiple Files in One External Table

You can union multiple files into one table:

ORGANIZATION EXTERNAL (
  ...
  LOCATION ('part1.csv', 'part2.csv', 'part3.csv')
)

The external table presents all three files as a single rowset. Useful when your source system splits a daily dump into multiple files.

You can also point to different directories:

LOCATION (
  data_dir_1 : 'jan.csv',
  data_dir_2 : 'feb.csv'
)

ORACLE_DATAPUMP — Binary Read/Write

ORACLE_DATAPUMP is the binary equivalent. It can both read existing Datapump files and write a Datapump file from a SELECT statement.

Write a query result to a Datapump file:

CREATE TABLE emp_export
ORGANIZATION EXTERNAL (
  TYPE ORACLE_DATAPUMP
  DEFAULT DIRECTORY ext_data_dir
  LOCATION ('emp_export.dmp')
)
AS
SELECT * FROM employees WHERE department_id = 50;

This creates a file emp_export.dmp containing the selected rows in Datapump's binary format.

Read a Datapump file on another database:

-- On the destination database:
CREATE TABLE emp_import (
  employee_id NUMBER,
  first_name  VARCHAR2(50),
  ...
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_DATAPUMP
  DEFAULT DIRECTORY ext_data_dir
  LOCATION ('emp_export.dmp')
);

-- Query it:
SELECT * FROM emp_import;

Compared to ORACLE_LOADER, Datapump:

• Reads/writes binary — much faster, smaller files
• Both directions supported
• Less flexible for ad-hoc CSV ingestion

External Tables vs SQL*Loader

Both exist for loading flat files. The trade-offs:

Default to external tables in modern Oracle. SQL*Loader retains a niche when you need to load from a network-connected client without granting filesystem access on the server. External tables are easier to use, integrate with SQL, and are the recommended approach for new code.

Limitations

External tables can't do everything a regular table can:

• Read-only with ORACLE_LOADER — you can't INSERT, UPDATE, or DELETE through it
• No indexes — every query is a full scan of the underlying file
• No constraints — except NOT NULL on columns
• Slower for repeated access — every query re-reads and re-parses the file
• No partitioning (though you can have multiple files as separate locations)
• File must be on the database server's filesystem — not a remote URL

For frequently-queried data, load it into a real table once and query the table. External tables are best for one-shot loads and infrequent queries.

Inspecting External Tables

-- Confirm a table is external and see its driver:
SELECT table_name, type_name, default_directory_name
FROM   user_external_tables;

-- See all access parameters:
SELECT * FROM user_external_tables WHERE table_name = 'EXT_EMPLOYEES';

-- See the file locations:
SELECT * FROM user_external_locations WHERE table_name = 'EXT_EMPLOYEES';

Worked Example — Loading a Daily CSV with Validation

Requirements:

• A vendor drops a daily CSV orders_YYYYMMDD.csv into /u01/incoming
• Bad rows must be logged but not fail the load
• Only rows with status = 'CONFIRMED' should be loaded
• After load, the file should be archived

Setup (one-time, DBA):

CREATE OR REPLACE DIRECTORY incoming AS '/u01/incoming';
CREATE OR REPLACE DIRECTORY archive  AS '/u01/incoming/archive';
GRANT READ, WRITE ON DIRECTORY incoming TO etl_user;
GRANT WRITE       ON DIRECTORY archive  TO etl_user;

External table definition:

CREATE TABLE ext_orders (
  order_id    NUMBER,
  customer_id NUMBER,
  order_date  DATE,
  status      VARCHAR2(20),
  total       NUMBER
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY incoming
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    BADFILE     incoming : 'orders.bad'
    LOGFILE     incoming : 'orders.log'
    SKIP 1
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    MISSING FIELD VALUES ARE NULL
    (
      order_id     INTEGER EXTERNAL,
      customer_id  INTEGER EXTERNAL,
      order_date   DATE 'YYYY-MM-DD',
      status       CHAR(20),
      total        DECIMAL EXTERNAL
    )
  )
  LOCATION ('orders_current.csv')
)
REJECT LIMIT 100;

Daily load procedure:

BEGIN
  -- Insert only confirmed orders into the real table
  INSERT /*+ APPEND */ INTO orders (order_id, customer_id, order_date, status, total)
  SELECT order_id, customer_id, order_date, status, total
  FROM   ext_orders
  WHERE  status = 'CONFIRMED';

  COMMIT;

  -- Audit log
  INSERT INTO load_audit (file_name, loaded_at, row_count)
  VALUES ('orders_current.csv', SYSDATE, SQL%ROWCOUNT);
  COMMIT;
END;
/

A scheduled job runs the procedure nightly. The vendor only needs to copy each day's file as orders_current.csv.

Common Errors

Interview Corner

DROP TABLE ext_orders;

CREATE TABLE ext_orders (
  ...new schema or new positions...
)
ORGANIZATION EXTERNAL (
  ...new ACCESS PARAMETERS...
);

ALTER TABLE ext_orders ACCESS PARAMETERS (
  RECORDS DELIMITED BY NEWLINE
  FIELDS TERMINATED BY ';'                -- changed from ','
  ...
);

ALTER TABLE ext_orders LOCATION ('orders_v2.csv');

CREATE TABLE orders_local AS SELECT * FROM ext_orders;
CREATE INDEX orders_date_idx ON orders_local(order_date);
-- Now query orders_local instead

CREATE TABLE ext_orders (...) ORGANIZATION EXTERNAL (...) PARALLEL 8;
-- Or per-query:
SELECT /*+ PARALLEL(ext_orders 8) */ * FROM ext_orders WHERE ...;

Related Topics

• DDL Commands — CREATE TABLE ... ORGANIZATION EXTERNAL is the entry point
• DML Commands — INSERT INTO target SELECT * FROM ext_table is the standard load pattern
• Performance — INSERT /*+ APPEND */ and parallelism for large external loads
• Partitioning — EXCHANGE PARTITION paired with external tables for fastest ETL
• Data Dictionary — USER_EXTERNAL_TABLES and USER_EXTERNAL_LOCATIONS reference views