Database Reference
In-Depth Information
How Do I Call SQLLDR from a Stored Procedure?
The short answer is that you cannot do this. SQLLDR is not an API; it is not something that is callable. SQLLDR is
a command-line program. You can definitely write an external procedure in Java or C that runs SQLLDR, but that
won't be the same as “calling” SQLLDR. The load will happen in another session, and it will not be subject to your
transaction control. Additionally, you will have to parse the resulting log file to determine if the load was successful
or not, and how successful (i.e., how many rows got loaded before an error terminated the load) it may have been.
Invoking SQLLDR from a stored procedure is not something I recommend doing.
In the past, before Oracle9
i
, you might have implemented your own SQLLDR-like process. For example, the
options could have been as follows:
BFILES
to read binary data or
UTL_FILE
to
•
Write a mini-SQLLDR in PL/SQL. It can use either
read text data to parse and load.
•
Write a mini-SQLLDR in Java. This can be a little more sophisticated than a PL/SQL-based
loader and can make use of the many available Java routines.
•
Write a SQLLDR in C, and call it as an external procedure.
I'd like to finish up the topic of SQLLDR by discussing a few topics that are not immediately intuitive.
SQLLDR Caveats
In this section, we will discuss some things to have to watch out for when using SQLLDR.
TRUNCATE Appears to Work Differently
The
TRUNCATE
option of SQLLDR might appear to work differently than
TRUNCATE
does in SQL*Plus, or any other tool.
SQLLDR, working on the assumption you will be reloading the table with a similar amount of data, uses the extended
form of
TRUNCATE
. Specifically, it issues the following:
truncate table t reuse storage
The
REUSE STORAGE
option does not release allocated extents—it just marks them as free space. If this were not
the desired outcome, you would truncate the table prior to executing SQLLDR.
SQLLDR Defaults to CHAR(255)
This issue comes up so often, I've decided to talk about it twice in this chapter. The default length of input fields is 255
characters. If your field is longer than this, you will receive an error message:
Record N: Rejected - Error on table T, column C.
Field in data file exceeds maximum length
This does not mean the data will not fit into the database column; rather, it indicates that SQLLDR was expecting
255 bytes or less of input data, and it received somewhat more than that. The solution is to simply use
CHAR(N)
in the
control file, where
N
is big enough to accommodate the largest field length in the input file. Refer to the very first item
in the earlier section “Loading Data with SQLLDR FAQs” for an example.
