Oracle External Table

I am always being asked to get some data into a database. Most of the time it is just a matter of writing an import or using a bulk import tool to load the data. However, sometimes it’s a little more complicated than that. Well, I have been working with Oracle 10g database lately and love the external table feature.

Oracle external tables is a wonderful mechanism that allows you to take an CSV or fixed-length file and logically map it as a database table. The advantage of this over import is a lot of times you have external data that you want to “load” for reporting and scrubbing against, but once the data has been scrubbed, it is useless. This is especially useful where business demands require you to take external data and check against your system.

I could have really used this feature when I wrote a process to verify telephone number porting reports. Local Number Portability (LNP) and Wireless Number Portability (WNP) required us to verify “owner” of a number and make sure we turn off ported out numbers and was setup to bill ported in numbers.

Anyway, there is a couple of straight forward steps to making an external table to work. Since I run Oracle on Windows OS (I know, Linux is better – but that’s a topic for another blog), I have to just make sure of a few things up front.

1. The folder on the host OS file system must exist and the same user Oracle process runs under(localsystem for Windows) must have read/write access to the folder.




2. The file to be the source of the external table needs to reside in that folder.

Next login to Oracle and execute the following code:


create or replace directory ext_dir as 'c:\extern';


Now, ext_dir is the symbolic name for this folder and through SQL you will access this resource using the ext_dir reference.
Now, the file I am going to create the external table is a comma separated file aka CSV. Since its comma separated, all I have to do is define the columns and make the table.



create table MonthlyOrders (
invoice varchar2(255),
orderdate date,
description varchar2(255),
price number,
quanity number,
total number
)
organization external (
type oracle_loader
default directory
ext_dir --- this is my reference to the external directory
access parameters (
records delimited by newline
fields terminated by ',' --- comma seperated
missing field values are null --- if no data, make it null
)
location ('orders.csv') ---- this is the name of the external file
);



Notice this is actually quite straight forward way to create a “table” that is really stored in an external file. Do this next


SELECT * FROM MonthlyOrders;


and see your data list out!


Now, as long as the file format doesn’t change, you just have to change out the file each month to get the new records. Once you have this in place, then you can create processes to take the file, scrub away and give you feed back!