External Tables in Oracle

✅ External Tables in Oracle – Overview & Usage

External tables in Oracle allow you to query data in flat files (CSV, pipe-delimited, etc.) as if they are regular database tables, without loading the data into the database. They're read-only and extremely useful for data staging, validation, or integration.

🔹 Key Characteristics

Feature	Details
Read-only	You can query, but not insert/update/delete data
Location	Data stays outside the database, typically on disk
SQL access	You can query it using `SELECT`, join with internal tables, etc.
Format	File can be delimited or fixed-width
Performance	Great for one-time loads or large flat-file processing

🔹 Components of an External Table

External data file (e.g. .dat, .csv)
Oracle directory – points to file path
Access parameters – define format (delimiter, date format, etc.)
Table definition – maps file structure to columns

🔹 Syntax Summary

-- Create directory (once)
CREATE OR REPLACE DIRECTORY ext_dir AS '/path/to/files';
GRANT READ, WRITE ON DIRECTORY ext_dir TO your_user;

-- Create external table
CREATE TABLE ext_sample (
  col1 VARCHAR2(100),
  col2 NUMBER,
  col3 DATE
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    (
      col1 CHAR(100),
      col2 CHAR(10),
      col3 CHAR(20) DATE FORMAT 'YYYY-MM-DD'
    )
  )
  LOCATION ('sample_file.csv')
)
REJECT LIMIT UNLIMITED;

🔹 Where to Use External Tables

ETL processes
Data migration/staging
Log file processing
Validating bulk files before permanent load
Handling one-time loads without inserting into base tables

🔸 Limitations

No DML (INSERT, UPDATE, DELETE)
Data must reside on accessible file system
Requires good control of file format

🛠 Related Views

ALL_EXTERNAL_TABLES
USER_EXTERNAL_TABLES
DBA_DIRECTORIES

Here's a set of realistic external table examples in Oracle, covering different scenarios like CSV files, pipe-delimited files, fixed-width files, date formatting, and handling optional fields.

🔹 1. Basic CSV File with Comma Separator

File: employees.csv
Contents:

1001,John Doe,IT,50000
1002,Jane Smith,HR,60000

CREATE OR REPLACE DIRECTORY ext_dir AS '/u01/data';
GRANT READ ON DIRECTORY ext_dir TO your_user;

CREATE TABLE ext_employees (
  emp_id     NUMBER,
  name       VARCHAR2(100),
  department VARCHAR2(50),
  salary     NUMBER
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    MISSING FIELD VALUES ARE NULL
    (
      emp_id,
      name,
      department,
      salary
    )
  )
  LOCATION ('employees.csv')
)
REJECT LIMIT UNLIMITED;

🔹 2. Pipe (`|`) Delimited File with Optional Fields

File: customers.dat

101|Alice|New York|USA
102|Bob||USA

CREATE TABLE ext_customers (
  cust_id     NUMBER,
  name        VARCHAR2(50),
  city        VARCHAR2(50),
  country     VARCHAR2(50)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY '|' 
    MISSING FIELD VALUES ARE NULL
    (
      cust_id,
      name,
      city,
      country
    )
  )
  LOCATION ('customers.dat')
)
REJECT LIMIT UNLIMITED;

🔹 3. Fixed-Width File (No Delimiters)

File: products.txt

0001iPhone     Electronics00500
0002WashingMachHomeAppli00900

CREATE TABLE ext_products (
  prod_id     CHAR(4),
  prod_name   CHAR(12),
  category    CHAR(10),
  price       CHAR(5)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_dir
  ACCESS PARAMETERS (
    RECORDS FIXED 31
    FIELDS (
      prod_id     POSITION(1:4),
      prod_name   POSITION(5:16),
      category    POSITION(17:26),
      price       POSITION(27:31)
    )
  )
  LOCATION ('products.txt')
)
REJECT LIMIT UNLIMITED;

🔹 4. Date Fields and Format Control

File: sales.csv

501,2023-12-01,10000
502,2023-12-05,15000

CREATE TABLE ext_sales (
  sale_id      NUMBER,
  sale_date    DATE,
  amount       NUMBER
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ',' 
    (
      sale_id,
      sale_date CHAR(10) DATE FORMAT 'YYYY-MM-DD',
      amount
    )
  )
  LOCATION ('sales.csv')
)
REJECT LIMIT UNLIMITED;

🔹 5. Large Record with Complex Field (e.g., Address or Nested Fields)

File: users.dat
Delimiter: ! within address

100|Raj|4!Main Street!Delhi!India!110001|IN

CREATE TABLE ext_users (
  user_id     NUMBER,
  name        VARCHAR2(50),
  address     VARCHAR2(200),
  country_code VARCHAR2(2)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY '|' 
    (
      user_id,
      name,
      address,
      country_code
    )
  )
  LOCATION ('users.dat')
)
REJECT LIMIT UNLIMITED;

⚠️ Tip: Use LOGFILE, BADFILE, and DISCARDFILE parameters if you want detailed logs for debugging rejects.

External Tables in Oracle