Database Software

Suppose we have a file containing city populations in our /tmp directory and we wish to access this data from within our database.

Tokyo,Japan,33600000
Seoul,South Korea,23400000
Mexico City,Mexico,22400000
New York,USA,21900000
Bombay,India,21600000
Delhi,India,21500000
Sao Paulo,Brazil,20600000
Los Angeles,USA,18000000
Shanghai,China,17500000O
saka,Japan,16700000

First, we create a DIRECTORY object which will reference the /tmp directory in our file system.

CREATE OR REPLACE DIRECTORY tmp_dir AS '/tmp';

Directory created




Now, we create an external table by specifying the name of the file we wish to query and the directory where it can be located. We will rely on the default values of comma for a field separator and new line as the record delimiter, to describe our data.

CREATE TABLE pop_ext ( city VARCHAR2(30),
country VARCHAR2(30),
population NUMBER)
ORGANIZATION EXTERNAL
(DEFAULT DIRECTORY tmp_dir
LOCATION('pop.txt'));


Table created.

But when run the below query

SELECT * FROM pop_ext;


I am getting following error

ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52