External Tables

Some useful information on directories and external tables

External tables rely on database directories, so firstly create a database directory if required.

create or replace directory ... as '...'
note: don't use parameters such as $HOME (as oracle can't work out what these are).

grant privileges to the directory created above:

grant read on directory ... to ...;

Next, create a table (see below for an example) and then start querying the external table.

CREATE TABLE ... ( ... some columns ...) ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER DEFAULT DIRECTORY ...
ACCESS PARAMETERS
(
records delimited by newline
fields REJECT ROWS WITH ALL NULL FIELDS
)
LOCATION
(
'add_partition.cfg'
)
) REJECT LIMIT unlimited