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)
        )
        ORGANIZATION EXTERNAL
       ( DEFAULT DIRECTORY random_dir ß This points to the oracle directory where file is located on the database server
         ACCESS PARAMETERS
         ( RECORDS DELIMITED BY NEWLINE
           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.
SELECT
  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).

Links:


Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Musings

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 oracle blog

Sharing experience (by Bertrand Drouvot)

Frits Hoogland Weblog

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

Vishal desai's Oracle Blog

Just another WordPress.com weblog

%d bloggers like this: