All language subtitles for [SubtitleTools.com] External Tables - Learning Oracle 12c [Video]

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.