Database Reference
In-Depth Information
The log file tells us about many of the aspects of our load. We can see the options we used (defaulted or
otherwise). We can see how many records were read, how many loaded, and so on. The log files specify the locations
of all
BAD
and
DISCARD
files. They even tell us how long it took. These log files are crucial for verifying that the load was
successful, as well as for diagnosing errors. If the loaded data resulted in SQL errors (i.e., the input data was “bad” and
created records in the
BAD
file), these errors would be recorded here. The information in the log files is largely
self-explanatory, so we will not spend any more time on it.
Loading Data with SQLLDR FAQs
We will now cover what I have found to be the most frequently asked questions with regard to loading data in an
Oracle database using SQLLDR.
Why Do I Receive “exceeds maximum length” in My Log File?
This is perhaps the most frequently recurring question I've heard with SQLLDR: Why does my log file contain
something similar to (the following)?:
Record 4: Rejected - Error on table DEPT, column DNAME.
Field in data file exceeds maximum length
This is due to the fact that the default datatype in SQLLDR for processing an input record is a
char(255)
. If you
have any string datatypes in your table that exceed that, you'll have to explicitly tell SQLLDR that the input record can
contain more than 255 characters.
For example, suppose you add a column that can hold more than 255 characters:
EODA@ORA12CR1> alter table dept modify dname varchar2(1000);
Table altered.
And you had a control file such as the following (the line with more text repeated is a single line in that input file):
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ','
(DEPTNO, DNAME, LOC)
BEGINDATA
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance more text more text more text more ... <repeated many times> ...more text,Virginia
When you ran SQLLDR, you would receive the previous error message. The solution is rather simple:
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ','
(DEPTNO, DNAME
char(1000)
, LOC)
BEGINDATA ...
That's it! Just tell SQLLDR the maximum width of the field in the input record—in this case 1,000.
