Database Reference
In-Depth Information
columnarrayrows -- number of rows for direct path column array (Default 5000)
streamsize -- size of direct path stream buffer in bytes (Default 256000)
multithreading -- use multithreading in direct path
resumable -- enable or disable resumable for current session (Default FALSE)
resumable_name -- text string to help identify resumable statement
resumable_timeout -- wait time (in seconds) for RESUMABLE (Default 7200)
date_cache -- size (in entries) of date conversion cache (Default 1000)
no_index_errors -- abort load on any index errors (Default FALSE)
...
For technical definitions of each individual parameter, please refer to Chapter 8 of the
Oracle Database Utilities
manual. I will demonstrate the usage of a few of these parameters in this chapter.
To use SQLLDR, you will need a
control file
. A SQLLDR control file simply contains information describing the
input data—its layout, datatypes, and so on—as well as information about the target table(s).
■
Don't confuse a SQLLDr control file with a database control file. recall from Chapter 3 that a database control
file is small binary file that stores a directory of the files oracle requires along with other information such as checkpoint
data, the name of the database, and so on.
Note
The control file can even contain the data to load. In the following example, we'll build a simple control file in a
step-by-step fashion, and I'll provide an explanation of the commands. (Note that the parenthetical numbers to the
left in the code are
not
part of this control file; they are just there for reference.) I'll refer to the file later as
demo1.ctl
.
(1) LOAD DATA
(2) INFILE *
(3) INTO TABLE DEPT
(4) FIELDS TERMINATED BY ','
(5) (DEPTNO, DNAME, LOC)
(6) BEGINDATA
(7) 10,Sales,Virginia
(8) 20,Accounting,Virginia
(9) 30,Consulting,Virginia
(10) 40,Finance,Virginia
LOAD DATA
(1): This tells SQLLDR what to do (in this case, load data). The other thing SQLLDR
can do is
CONTINUE_LOAD
to resume a load. You would use this latter option only when
continuing a multitable direct path load.
•
INFILE *
(2): This tells SQLLDR the data to be loaded is actually contained within the control
file itself as shown on lines 6 through 10. Alternatively, you could specify the name of another
file that contains the data. You can override this
INFILE
statement using a command-line
parameter if you wish. Be aware that
command-line options override control file settings
.
•
INTO TABLE DEPT
(3): This tells SQLLDR to which table you are loading data (in this case, the
DEPT
table).
•
FIELDS TERMINATED BY ','
(4): This tells SQLLDR that the data will be in the form of
comma-separated values. There are dozens of ways to describe the input data to SQLLDR;
this is just one of the more common methods.
•
