Data often arrives as files: CSV exports, partner feeds, logs. An external table lets SQL query such a file as if it were a table, without loading it first. The file stays where it is and is read each time the table is queried, so you can inspect, filter, and join it, or load it into a real table with INSERT ... SELECT.
Code for This Guide
The main examples are in the examples/special-tables folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
create table table (columns)
organization external (
type {oracle_loader | oracle_datapump | oracle_bigdata}
default directory directory
access parameters (...)
location ('file' [, ...]))
[reject limit {n | unlimited}]The directory is a database DIRECTORY object pointing to a folder on the server, which the user needs READ privilege on. For ORACLE_LOADER, the access parameters describe the records and fields in SQL*Loader syntax. Rows that fail to convert go to a bad file, up to the REJECT LIMIT.
Query a CSV File
The file fuel_prices.csv, in the folder of the directory NIMBUS_FILES, has a header line and three columns. The access parameters skip the header, split fields at commas, and read the date with a mask.
Example:
create table fuel_prices_ext (
airport_code char(3),
price_date date,
usd_per_gallon number(5,2)
)
organization external (
type oracle_loader
default directory nimbus_files
access parameters (
records delimited by newline
skip 1
fields terminated by ','
(airport_code, price_date char(10) date_format date mask "YYYY-MM-DD", usd_per_gallon)
)
location ('fuel_prices.csv')
)
reject limit unlimited;
select airport_code, round(avg(usd_per_gallon), 2) as avg_price, count(*) as readings
from fuel_prices_ext
group by airport_code
order by avg_price;Output:
Table FUEL_PRICES_EXT created. AIRPORT_CODE AVG_PRICE READINGS _______________ ____________ ___________ DXB 2.41 3 SIN 2.52 3 JFK 2.69 3 LHR 2.91 3
The query aggregates the file like any table. Drop the table afterward with drop table fuel_prices_ext purge; the file is not affected.
Inline External Tables
EXTERNAL (...) in the FROM clause queries a file without creating a table, with the same definition inside the query.
Example:
select *
from external (
(airport_code char(3), price_date varchar2(10), usd_per_gallon number)
type oracle_loader default directory nimbus_files
access parameters (records delimited by newline skip 1 fields terminated by ',')
location ('fuel_prices.csv')
reject limit unlimited)
where airport_code = 'LHR';Output:
AIRPORT_CODE PRICE_DATE USD_PER_GALLON _______________ _____________ _________________ LHR 2026-01-05 2.88 LHR 2026-02-02 2.91 LHR 2026-03-02 2.95
This suits one-off checks of a file before deciding how to load it.
Things to Know
- External tables are read-only with ORACLE_LOADER; ORACLE_DATAPUMP can also write a file with CREATE TABLE ... AS SELECT.
- To load the data, insert into a regular table from the external table; the load can use direct path with the APPEND hint.
- Changing the LOCATION with ALTER TABLE points the same definition at a new file, such as each day's feed.
Related Guides
Conclusion
An external table queries a file on the database server as a table, using a directory object and access parameters that describe the file. Use it to inspect and load files with SQL, and EXTERNAL (...) inline for one-off queries without creating a table.
