Database Reference
In-Depth Information
With those exceptions in mind, in general I strongly recommend using external tables for their extended
capabilities. SQLLDR is a fairly simple tool that generates an
INSERT
statement and loads data. Its ability to
use SQL is limited to calling SQL functions on a row-by-row basis. External tables open the entire SQL set of
functionality to data loading. Some of the key functionality features that external tables have over SQLLDR, in my
experience, are as follows:
WHERE
conditions to selectively load data. SQLLDR has a
WHEN
clause to select rows to load, but you are limited to using only
AND
expressions and expressions
using equality—no ranges (greater than, less than), no
OR
expressions, no
IS NULL
, and so on.
•
The ability to use complex
MERGE
data. You can take an operating system file full of data and update existing
database records from it.
•
The ability to
•
The ability to perform efficient code lookups. You can join an external table to other database
tables as part of your load process.
sorted
by including an
ORDER BY
statement in the
CREATE TABLE
or
•
The ability to load data
INSERT
statement.
INSERT
. Starting in Oracle9
i
, an
INSERT
statement can insert
into one or
more
tables using complex
WHEN
conditions. While SQLLDR can load into multiple
tables, it can be quite complex to formulate the syntax.
•
Easier multitable inserts using
•
The ability to specify one or more operating system commands to be executed as the first step
(preprocess) when selecting data from an external table.
•
A shallower learning curve for new developers. SQLLDR is
yet another tool to learn
, in addition
to the programming language, the development tools, the SQL language, and so on. As long as
a developer knows SQL, he or she can immediately apply that knowledge to bulk data loading,
without having to learn a new tool (SQLLDR).
So, with that in mind, let's look at how to use external tables.
Setting Up External Tables
There are two simple methods for getting started with external tables:
•
Executing SQLLDR with the
EXTERNAL_TABLE
parameter
•
Starting with Oracle 12
c
, running SQLLDR in express mode
Interestingly, both techniques utilize the SQLLDR command-line tool. They are discussed in the following
sections.
