Database Reference
In-Depth Information
Command Line Overrides Control File
Many of the SQLLDR options may be either placed in the control file or used on the command line. For example,
I can use
INFILE FILENAME
as well as
SQLLDR ... DATA=FILENAME
. The command line overrides any options in the
control file. You cannot count on the options in a control file actually being used, as the person executing SQLLDR can
override them.
SQLLDR Summary
In this section, we explored many areas of loading data. We covered the typical, everyday issues we will encounter:
loading delimited files, loading fixed-length files, loading a directory full of image files, using functions on input data
to transform the input, and so on. We did not cover massive data loads using the direct path loader in any detail;
rather, we touched lightly on that subject. Our goal was to answer the questions that arise frequently with the use of
SQLLDR and that affect the broadest audience.
Flat File Unload
One thing SQLLDR does not do, and that Oracle supplies no command-line tools for, is unloading data in a format
understandable by SQLLDR or other programs. This would be useful for moving data from system to system without
using Data Pump
EXPDP
/
IMPDP
. Using
EXPDP
/
IMPDP
to move data from system to system works fine for moderate
amounts of data—as long as both systems are Oracle.
■
apeX provides a data export feature as part of its SQL Workshop as does SQL Developer. You may export the
information in a CSV format easily. this works well for a few megabytes of information, but it is not appropriate for many
tens of megabytes or more.
Note
We will develop a small PL/SQL utility that may be used to unload data on a server in a SQLLDR-friendly
format. Also, equivalent tools for doing so in Pro*C and SQL*Plus are provided on the Ask Tom web site at
http://tkyte.blogspot.com/2009/10/httpasktomoraclecomtkyteflat.html
.
The PL/SQL utility will work fine in
most small cases, but better performance will be had using Pro*C. Note that Pro*C and SQL*Plus are also useful if you
need the files to be generated on the client and not on the server, which is where PL/SQL will create them.
