All language subtitles for [SubtitleTools.com] SQL Loader - Learning Oracle 12c [Video]

In this lesson, we're
going to look at data

migration using SQL Loader.

So data migration is
the act of moving data

from one place to another and
often from an external source

into an Oracle database.

So there are numerous ways
to load data into a database.

So we're going to take
a look at SQL Loader.

So SQL Loader is somewhat
of an older tool,

but it's still very
commonly used today

because it's really, really
good at taking file data, just

straight delimited data,
let's say, or even raw data,

and loading it into tables.

There's quite a bit of
work in setting it up.

But once you do that
work, it's something

that doesn't have to be repeated
unless your table changes,

and it can be very, very fast.

So SQL Loader is going to read
data from a delimited file

and load it into a table.

So a delimited file would be
just a file where the data is

delimited by some delimiter,
like a comma-delimited file,

or pipe delimited, or
any number of characters,

and you just need to specify
what the delimiter is.

It can also read
data from flat files

and load it into tables
based on character positions.

So it can say, take the first
five characters of the file

and load that into the first
column, and then the next 10,

and load that into the second
column, so on and so forth.

And often this is used
with mainframe data,

in conjunction with mainframe
data, where the Oracle

database is in the mid tier,
kind of, of the enterprise

and receives data that's
stored in a mainframe system.

So SQL Loader can run using
two different methods.

The first is the
conventional path,

and all that the
conventional path does

is simply construct
INSERT statements using

the delimited or raw
data and then just run

those INSERT statements.

So conventional
path is the safest,

if you would, but
only in terms of being

able to use all kinds
of different data types.

There's nothing inherently
unsafe about either way,

or either method, except that
the second method, direct path,

can't necessarily use some of
the more esoteric data types

that people sometimes
have in a database.

Direct path, however,
constructs blocks

from that data that are written
directly into the database.

And it actually bypasses
the database buffer cache

and the caching
that needs to occur.

And for that reason,
direct path is much faster.

So it doesn't have to construct
and run INSERT statements.

It just constructs the blocks
that are directly written.

And so whenever
possible, we definitely

want to use the direct path.

And we should be able
to in most cases,

unless there's something
sort of unusual

about the table that
we're loading into

or the data that we have.

So there are a number of files
that are used in a SQL Loader

operation.

So all these have to be
prepared before we run our SQL

Loader, which the statement
itself is quite simple,

but we have to compose
all of these files.

So the first one is the
input data file itself.

So that's going to be the
delimited data or raw data

that we're trying to
load into a table.

The second is the
parameter file,

and that's a file that
just has a location for all

the other files, so the input
data file, the output files,

like the log, and those kind
of things, the control file.

The parameter file is what knows
all about those other files.

And it's actually
the parameter file

that we're going to directly
call from the SQL Loader

application, and then it's
going to find everything else.

And the third is
the control file.

So that's actually
a specification

on how the data is to be loaded.

So let's take a look here.

I've got a little
bit of setup here

already in the
Oracle-based directory.

I've created a
directory called loader.

All right.

Click that.

And then I have a
dept load.dat file.

If we take a look at
that, that's simply

data from the dept table.

So we just poured that
out to a DAT file.

And then, so it's comma
delimited-- three columns,

comma-delimited file.

So that's what we're going to
use for our loader, our load

data.

Let's go into Scott and create
a table to receive this data.

So we'll create a table that's
just like the dept table.

So if we select from
the dept_loader table,

no rows selected.

So we have no data in there yet.

So let's take a look
at the PAR file.

So the PAR file is going to
drive the location of all

the other files.

So in here, we put
the user ID for Scott

and then the name
of the control file.

And we also-- if
we're not running

all of this out
of one directory,

we'll need to put the directory
paths in there as well.

And we have a log
file specified here.

And we have a bad file.

So if there's some reason
that SQL Loader attempts

to load a line of data,
a row, into a table

and that doesn't fit within the
column parameters or whatever,

then it will be listed
in the bad file.

But it will not be
put into the table.

The name of our data file--

so that's the dep_load.dat,
so the data that we're loading

in, and then direct equal true
so that we'll use the direct

path load instead
of conventional.

So let's take a look
at our control file.

So our control file is going
to be the specification on how

the data goes into the table.

So it says load data into table
and the name of the table.

It's doing an
insert as opposed to

an append or other operation.

Fields terminated by--
and then in double quotes,

we put a comma.

So that shows this
that our comma

is our delimiter character--

and then the three
columns that line up

with our dept_loader table.

All right.

So here I am from our command
line in the loader directory

that I've created, and I'm ready
to go ahead and run our SQL

Loader.

So SQL Loader is invoked
using the sqlldr command.

And since we've
constructed all our files,

such as our PAR file
and our control file,

we simply need to say parfile
equal dept_loader.par.

All right.

So it says load completed,
logical record count four.

So it did four rows.

And then we can look
at our director here.

And notice that there's no bad
file that's been generated.

But there is a log
file that tells us

exactly how the data was put
in, which ones were successfully

loaded, and if there were
any rows that weren't loaded

from errors, and those kind
of things, elapsed time,

a very detailed log.

So for our purposes,
let's go in as Scott

and select star
from dept_loader.

And if we recall, it didn't
have any data before.

But it should have
four rows now.

And there they are.

So that's a little
about how to use

SQL Loader to
construct those files

and to load data into tables.