External Tables

External tables let Oracle query a flat file as if it were a database table — no load step required. They use the same sqlldr-style field specifications under the hood (ORACLE_LOADER access driver) but the data stays on disk until a query runs.

When to use external tables vs sqlldr

Need	External table	sqlldr
Query data without loading	✅	❌
Join source file to existing tables in SQL	✅	❌
INSERT … SELECT with transformations	✅	❌
Parallel query on the file	✅	✅ (parallel direct path)
Triggers must fire	❌	✅ (conventional)
Source file must stay on OS	✅	❌
DML against the data	❌ (read-only)	✅

Creating an external table

Step 1: Create an Oracle DIRECTORY object

-- DBA creates (or grants CREATE ANY DIRECTORY)
CREATE OR REPLACE DIRECTORY data_dir AS '/data/feeds';
GRANT READ ON DIRECTORY data_dir TO hr;

Step 2: CREATE TABLE … ORGANIZATION EXTERNAL

CREATE TABLE hr.employees_ext
(
  employee_id   NUMBER(6),
  first_name    VARCHAR2(20),
  last_name     VARCHAR2(25),
  email         VARCHAR2(100),
  hire_date     DATE,
  salary        NUMBER(8,2)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY data_dir
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE
    CHARACTERSET AL32UTF8
    BADFILE  data_dir:'employees_ext.bad'
    LOGFILE  data_dir:'employees_ext.log'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    MISSING FIELD VALUES ARE NULL
    (
      employee_id   INTEGER EXTERNAL,
      first_name    CHAR(20),
      last_name     CHAR(25),
      email         CHAR(100),
      hire_date     CHAR(10) DATE_FORMAT DATE MASK "YYYY-MM-DD",
      salary        DECIMAL EXTERNAL
    )
  )
  LOCATION ('employees.csv')
)
REJECT LIMIT UNLIMITED;

Key clauses:

• TYPE ORACLE_LOADER — uses the sqlldr access driver
• DEFAULT DIRECTORY — base directory for all file references
• ACCESS PARAMETERS — the inner block mirrors a sqlldr control file
• LOCATION (...) — the data file(s) relative to the directory
• REJECT LIMIT — rows with errors; UNLIMITED means never abort

Querying an external table

-- Direct query
SELECT * FROM hr.employees_ext WHERE salary > 50000;

-- Join to a regular table
SELECT e.employee_id, e.last_name, d.department_name
FROM   hr.employees_ext e
JOIN   hr.departments d ON d.department_id = e.department_id
WHERE  e.hire_date > DATE '2020-01-01';

-- Load into a regular table
INSERT /*+ APPEND */ INTO hr.employees
SELECT * FROM hr.employees_ext;
COMMIT;

-- Create internal table from external
CREATE TABLE hr.employees_loaded AS
SELECT * FROM hr.employees_ext;

Multiple files

LOCATION ('employees_jan.csv', 'employees_feb.csv', 'employees_mar.csv')

Oracle treats all listed files as one logical table, reading them in order. For parallel query, Oracle assigns different files to different parallel slaves.

Parallel processing

ALTER TABLE hr.employees_ext PARALLEL 4;

-- Query will use 4 parallel processes, one per file (or more)
SELECT /*+ PARALLEL(employees_ext, 4) */ COUNT(*) FROM hr.employees_ext;

Fixed-width external table

CREATE TABLE warehouse.mainframe_orders_ext
(
  order_id    NUMBER(10),
  order_date  DATE,
  amount      NUMBER(12,2)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY mf_dir
  ACCESS PARAMETERS
  (
    RECORDS FIXED 50
    FIELDS
    (
      order_id    POSITION(1:9)   INTEGER EXTERNAL,
      order_date  POSITION(10:19) DATE_FORMAT DATE MASK "YYYYMMDD",
      amount      POSITION(20:31) DECIMAL EXTERNAL
    )
  )
  LOCATION ('orders_mf.dat')
)
REJECT LIMIT UNLIMITED;

RECORDS FIXED 50 — each record is 50 bytes (equivalent to sqlldr's "FIX 50").

Preprocessor — decompress on the fly

Oracle 11gR2+ can run a preprocessor on the file before reading it:

CREATE OR REPLACE DIRECTORY exec_dir AS '/usr/bin';

CREATE TABLE hr.employees_gz_ext (...)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY data_dir
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE
    PREPROCESSOR exec_dir:'zcat'
    FIELDS TERMINATED BY ',' ...
  )
  LOCATION ('employees.csv.gz')
)
REJECT LIMIT UNLIMITED;

zcat decompresses the gzip file on the fly as Oracle reads it — no manual unzip step.

Modifying an external table

External tables support ALTER TABLE for metadata changes:

-- Change the data file
ALTER TABLE hr.employees_ext LOCATION ('employees_v2.csv');

-- Add a second file
ALTER TABLE hr.employees_ext LOCATION ('employees_p1.csv', 'employees_p2.csv');

-- Change parallel degree
ALTER TABLE hr.employees_ext PARALLEL 8;

-- Disable/enable
ALTER TABLE hr.employees_ext ACCESS PARAMETERS (REJECT LIMIT 10);

ORACLE_DATAPUMP access driver

For exporting and re-importing Oracle binary dumps (not sqlldr format):

CREATE TABLE hr.employees_dp_ext (...)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_DATAPUMP
  DEFAULT DIRECTORY data_dir
  LOCATION ('employees_dp.dmp')
);

-- Populate (export to file)
INSERT INTO hr.employees_dp_ext SELECT * FROM hr.employees;
COMMIT;

ORACLE_DATAPUMP is read-write; ORACLE_LOADER is read-only.

Best practices

• Create external tables for any file that is queried repeatedly without needing to load it — avoids the ETL step entirely
• Use REJECT LIMIT UNLIMITED during development; tighten to a low number in production
• Always specify BADFILE and LOGFILE so you can diagnose rejected rows
• For large files, ALTER TABLE … PARALLEL n where n equals the number of files — Oracle assigns one file per slave
• Use the preprocessor feature for compressed files rather than adding an unzip step to your pipeline
• When you do need to load an external table into an internal one, use INSERT /*+ APPEND */ INTO target SELECT * FROM ext_tbl — direct path insert from external table is very fast