Thursday, November 4, 2010

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:

External Tables Concepts: http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/et_concepts.htm
External Tables: http://www.orafaq.com/node/848


at

No comments :

Post a Comment

Subscribe to: Post Comments ( Atom )