Home > Databases/Embedded Sql > Oracle External Tables are great

Oracle External Tables are great

Tasked to scrape data from a fix width text file on a website and store this data in a table for further massaging and joining with other data. The UTL_HTTP package enabled retrieval of the file by going out to the website, navigating to the address where the file was located and read the file into a clob.

After obtaining the contents of the file, it was time to parse the clob line by line in order to extract data into appropriate columns in a table. The steps entailed:

  1. Loop through the clob breaking on each End of line
  2. Write a series of expressions that would substring out each column from the line

Although this process works it ends up embedding data in the code and if the flat file changes would then result in cracking open the PL/SQL package to make the mod.

A better way to do this would be to use an external table. An external table defines a file as a data source and allows querying of the file as if it is a table.

Using the same UTL_HTTP process to pull the file down to a clob, but instead of parsing clob, would then write this clob out to a flat file on disk and then define an external table that would use the file as its source.

Steps to setting up an external table:

Assume that we have a flat file of this structure:
Start  End   Width  Format   Description

1              16            16             A             Last Name
17            17            1               X
18            25            8               A             First Name
26            26            1               X
27            27            1              A             Middle Initial
28            28            1               X
29            37            9              N             Social Security Number
38            38            1              X
39            48            10            A             Admin Date (mm/dd/yyyy)
49            49            1              X
50            50           1              A              Program (See below)
51            54            4              X
55            57           3               I              Test Code (See below)
58            58            1              X
59            59            1              A             Status (P=Pass or F=Did Not Pass)
60            60            1             X
61            63            3               I              Institution Code (see below)

  1. As sys, map a directory in oracle to the filesystem:
    create or replace directory [directory_name] as [directory on filesystem]
  2. As sys grant access to the directory where the file is stored:

Grant  read on directory [directory_name] to [user]

Grant  write on directory [directory_name] to [user]

  1. Define external table:

create table [schema_name].[table_name]


last_name varchar2(16)

,first_name varchar2(8)

,Middle_Initial varchar2(1)

,National_ID varchar2(9)

,Admin_Date date

,PROGRAM varchar2(1)

,test_code varchar2(3)

,Status varchar2(1)

,Institution_Code varchar2(3)


organization external(

type oracle_loader

default directory [directory_name]



BADFILE  ‘badfile.bad’

LOGFILE  ‘logfile.log’



last_name (1:16) char(16)

,first_name (18:25) char(8)

,Middle_Initial (27:27) char(1)

,National_ID (29:37)char(9)

,Admin_Date (39:48) char(10)  DATE_FORMAT DATE MASK “MM/DD/YYYY”

,PROGRAM (49:49) char(1)

,test_code (55:57) char(3)

,Status (59:59) char(1)

,Institution_Code (61:63) char(3)



LOCATION (‘datafile.dat’)





  1. Now select queries can be written on the table [schema_name].[table_name]

Note: No DML (Data Manipulation Language) is allowed on external tables. This means that you can’t change or add data through insert, update, delete, etc.

  1. No comments yet.
  1. No trackbacks yet.

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: