Original subtitles

In this lesson, we're going to look at external tables.

And external tables are a pretty interesting and useful feature

of Oracle that have been around for a few versions.

And an external table is a way to view a flat, delimited data

file in a table format.

So why would this be useful to us?

Well, instead of having to directly load data

into a table, external tables let

us create a table structure with no storage in the database,

but actually looks out at the operating system,

out in the file system, and sees that data.

So anytime you replace or change the data

that's in that base data file, then

Oracle can see that as long as it

conforms to the structure of the table that you've created.

And so this is going to require a couple of different things,

but it's actually very easy to use

and really quite a bit simpler than using something

like SQL Loader, which would require a number of parameter

and control files to be set up.

So it requires the use of what we call a directory object.

This is just going to be a database

object that points to a particular directory

on the file system.

And then we're also going to need a specially created table.

So first and foremost, let's take a look at our directory

that we have here.

So in our base/loader directory, we have a data file here,

dept_load.dat, and it has data that was exported out

of the Dept table that Scott uses.

So I'm going to grab that path, go into SQL Plus as Scott.

I'm going to connect as sysdba here

to give Scott the proper permissions,

and create directory depth external as, and then paste

in that directory location.

So now anytime that we refer to a directory,

the directory dept_external, it's going to look

in the e:/app/sries/loader directory.

So we're really just creating a link in the database

out to a folder on the operating system.

Now, Scott needs to have permission

to read and write from this directory,

so we're going to say grant read on directory

dept external to Scott.

So we have our directory object, and now we

need our specially created table.

And so let's step through what's special about this create table

statement.

So the first part, here, we're calling

the table dept external.

And it's structured just as the dept table in the Scott schema

is-- same column names, data types just for simplicity.

So all of this is just a basic table.

Nothing special about that.

It's the second section that makes this an external table.

So it says organization external,

and then in parentheses, we use a number of different flags

for the table to know exactly where to look for its data--

type, Oracle loader.

So that means Oracle's internal loading mechanism

is going to be used to read data from the file into the table.

Default directory is dept external,

and that's the directory object that we just created.

And then we give it some access parameters.

It says fields terminated by, comma.

So it says when you read the file, the delimiters

in the file will be the commas.

And then location is dept_load.dat,

and that is specified in the dept external directory.

So it looks at the directory object, finds the path,

and then uses this location to find the actual data file.

So we're going to create this.

Now notice that we haven't done any INSERT statements into dept

external or anything like that.

Let me just do select star from dept external.

And notice that it reads the data directly

from the file itself.

What if we were to change a value here, and save it,

and rerun that SELECT statement?

So right now, it's a deptno of 40 as operations in Boston.

And notice that it reads the updated value.

Anytime we do a select, we're not

selecting from a table that's stored in the database.

We're selecting from data that's actually stored out

on the operating system.

So that's a little bit of how useful

an external table can be.