Loading data into an Oracle Database through External Tables

Frequently, I am asked to run a script which has numerous insert statements. These are generated from flat file(s), and their size can be quite large (in the MB’s).

A useful and practical alternative is using External Tables. These are tables (for all intents and purposes), which use delimited flat files as their source.

Here’s what I recommended to my developers (on 10gR2).

File Name: MyFile.csv

Since you would know the number of columns (and hopefully their intelligent names) you can create an external table like so:
CREATE TABLE mmme_external
        ( entitycode VARCHAR2(1000),
          customercode VARCHAR2(1000),
          entitytype VARCHAR2(1000),
          entityname VARCHAR2(1000),
          address  VARCHAR2(1000),
          city VARCHAR2(1000),
          state VARCHAR2(1000),
          zipcode VARCHAR2(1000),
          country VARCHAR2(1000),
          longitude VARCHAR2(1000),
          latitude VARCHAR2(1000),
          phonenumber  VARCHAR2(1000)
       ( DEFAULT DIRECTORY random_dir ß This points to the oracle directory where file is located on the database server
           FIELDS TERMINATED BY ‘,’ ß Specify the delimiter
         LOCATION (‘MyFile.csv’)    ß Actual file name
The only caveat is that the source file will need to be placed on the database server by someone who has access to it.

Once the table is created, it can be queried with ease.
  FROM mme_external;

External tables are a flexible and efficient way to load data into the database vs lets say SQL Loader (see Tom Kyte’s comments).



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s


Things I see and learn!

Thoughts from James H. Lui

If you Care a Little More, Things Happen. Bees can be dangerous. Always wear protective clothing when approaching or dealing with bees. Do not approach or handle bees without proper instruction and training.

bdt's blog

Sharing stuff (by Bertrand Drouvot)

Frits Hoogland Weblog

IT Technology; Yugabyte, Postgres, Oracle, linux, TCP/IP and other stuff I find interesting

Vishal desai's Oracle Blog

Just another WordPress.com weblog

%d bloggers like this: