External Table with Multiple Files in Oracle

To create an external table in Oracle that reads from multiple files, you simply list all the files under the LOCATION clause of the ORGANIZATION EXTERNAL clause.

---

✅ Example: External Table with Multiple Files

Assume the files:

• data1.csv
• data2.csv
• data3.csv

All are located in the directory /u01/data/extfiles that is mapped to Oracle directory object ext_dir.

---

🔹 Step 1: Create Directory and Grant Access

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

---

🔹 Step 2: Create External Table with Multiple Files

CREATE TABLE ext_multi_file_data (
  id        NUMBER,
  name      VARCHAR2(100),
  city      VARCHAR2(50),
  country   VARCHAR2(50)
)
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
    (
      id,
      name,
      city,
      country
    )
  )
  LOCATION (
    'data1.csv',
    'data2.csv',
    'data3.csv'
  )
)
REJECT LIMIT UNLIMITED;

---

🔸 Notes:

• The table reads data from all listed files, as long as they follow the same structure.
• You can include wildcards using PREPROCESSOR or switch to external partitions for large datasets (in Oracle 12c and above).
• If files are added frequently, consider using a directory listing preprocessor to dynamically read file names.

---