## Create Directory.  You need to have create any directory privilege to be able to create a directory
CREATE DIRECTORY ext_tab_dir AS 'F:\DBA\externaltable';

## Create a user account that will be accessing this table, skip this part if you already have a user account
CREATE USER vtest IDENTIFIED BY vtest;

## grant the required privileges
GRANT connect, resource TO vtest;
GRANT READ ON DIRECTORY ext_tab_dir TO vtest;
GRANT WRITE ON DIRECTORY ext_tab_dir TO vtest;

## copy the following and save as a .dat file into the directory that was created
## in this example the directory is 'F:\DBA\externaltable'
## I also used comma delimited to separate the columns and a new line for each row and the filename  is info.dat

Alvin,Tolliver,1976
Kenneth,Baer,1963
Mary,Dube,1973


## create the table that references the .dat file
## you can run this as the user vtest or as a dba

CREATE TABLE vtest.mike_extern (first_name CHAR(15), last_name CHAR(20), year_of_birth CHAR(4))
  ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
                         ACCESS PARAMETERS (FIELDS TERMINATED BY ',')
                         LOCATION ('info.dat'));

## query the table that was just created. :)
## I logged on as vtest

SQL> select * from mike_extern;

FIRST_NAME      LAST_NAME            YEAR
--------------- -------------------- ----
Alvin           Tolliver             1976
Kenneth         Baer                 1963
Mary            Dube                 1973

SQL> select first_name from mike_extern;

FIRST_NAME
---------------
Alvin
Kenneth
Mary

SQL> desc mike_extern;
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 FIRST_NAME                                         CHAR(15 CHAR)
 LAST_NAME                                          CHAR(20 CHAR)
 YEAR_OF_BIRTH                                      CHAR(4 CHAR)




## Background info on external table: 
## 
## When using the external table, the syntax/statement is CREATE TABLE...ORGANIZATION EXTERNAL 
## 
## You need to specify 3 parameters:
## 1. The DEFAULT DIRECTORY clause, which specifies the default directory to use for all input and output files that do not explicitly name a directory object.
## 2. The ACCESS PARAMETERS clause where output files are named. 
## 3. The LOCATION clause, which lists all of the datafiles for the external table. The files are named in the form directory:file. The directory portion is optional. If it is missing, the default directory is used as the directory for the file.
## 
## What about the TYPE ORACLE_LOADER in the statement??? Well by default, it is ORACLE_LOADER so in theory you dont have to specify this but it is good practice to be explicit.
## 
## There are two types that can be specified with external tables:
## 1. ORACLE_LOADER - only if you want to load data into your database (input only)
## 2. ORACLE_DATAPUMP - if you want to load and unload data to and from your databases (you don't need this for your concept); also the file is in binary mode