External Tables

This is an alternative to using sql*Loader, .ctl files and batch files to load flat files into Oracle tables.

You define a directory on the database server:

create or replace directory external_dir as '/home/dev/tqi/data'

Give access to that folder to the oracle user, and then specify the structure of the flat file as follows, which then makes it look like a table you can select from!

create table x_emp
( empl_id varchar2(3),
last_name varchar2(50),
first_name varchar2(50)
)
organization external
( default directory external_dir
access parameters
( records delimited by newline
fields terminated by ','
)
location ('x_emp.csv')
);

Just put the file into this location and do this:

select * from x_emp