Original subtitles

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.