- External tables allow Oracle to query data that is stored outside the database in flat files. The ORACLE_LOADER driver can be used to access any data stored in any format that can be loaded by SQL*Loader. No DML can be performed on external tables but they can be used for query, join and sort operations. Views and synonyms can be created against external table
- Create oracle directory name like DATA_PUMP_DIR.
Create table structure like below
CREATE TABLE countries_ext (
ORGANIZATION EXTERNAL (
DEFAULT DIRECTORY DATA_PUMP_DIR
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ‘,’
MISSING FIELD VALUES ARE NULL
REJECT LIMIT UNLIMITED;
If the load files have not been saved in the appropriate directory the following result will be displayed.
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file Countries1.txt in DATA_PUMP_DIR not found
First text file with data
Second text file with data
USA,Unites States of America,English
By default, a log of load operations is created in the same directory as the load files, but this can be changed using the LOGFILE parameter.
retrieve data from external table
select * from countries_ext
CREATE OR REPLACE VIEW english_speaking_countries AS
WHERE country_language = ‘English’
ORDER BY country_name;