External tables | Oracle DBA Tutorial pdf

External tables

By creating external tables, you can run SQL statements on simple delimited text files! However, external tables are read only i.e. you can’t update their data using SQL (you need to modify them in text editor).
To create an external table, first you need to create a folder in your hard disk where you will place the text files.
CREATE OR REPLACE DIRECTORY EXT_TABLES AS
'E:\ORACLE\ORADATA\EXTERNAL_TABLES'
The user, who will access external table, should have CREATE ANY DIRECTORY system privilege.
Now create the external table in SQL Plus as:
CREATE TABLE AIRPORT_CODE
(
CODE VARCHAR2(3),
CITY VARCHAR2(100),
COUNTRY VARCHAR2(50)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXT_TABLES
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL
)
LOCATION ('AIRPORT.TXT')
)
REJECT LIMIT UNLIMITED
You can now run SQL on the table.