Data Loading and Unloading - Expert Oracle Database Architecture - page 732

Here, SQLLDR is telling you that it ran out of data in the record before it ran out of columns. The solution is easy
in this case, and in fact SQLLDR even tells us what to do: use TRAILING NULLCOLS . This will have SQLLDR bind a
NULL value in for that column if no data exists in the input record. In this case, adding TRAILING NULLCOLS will cause
the bind variable :ENTIRE_LINE to be NULL . So, you retry with this control file:
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED date 'dd/mm/yyyy',
ENTIRE_LINE ":deptno||:dname||:loc||:last_updated"
)
BEGINDATA
10,Sales,Virginia,1/5/2014
20,Accounting,Virginia,21/6/2014
30,Consulting,Virginia,5/1/2013
40,Finance,Virginia,15/3/2014
Now the data in the table is as follows:
EODA@ORA12CR1> select * from dept;
DEPTNO DNAME LOC LAST_UPDA ENTIRE_LINE
---------- -------------- ------------- --------- -----------------------------
10 SALES VIRGINIA 01-MAY-14 10SalesVirginia1/5/2014
20 ACCOUNTING VIRGINIA 21-JUN-14 20AccountingVirginia21/6/2014
30 CONSULTING VIRGINIA 05-JAN-13 30ConsultingVirginia5/1/2013
40 FINANCE VIRGINIA 15-MAR-14 40FinanceVirginia15/3/2014
What makes this feat possible is the way SQLLDR builds its INSERT statement. SQLLDR will look at the
preceding and see the DEPTNO , DNAME , LOC , LAST_UPDATED , and ENTIRE_LINE columns in the control file. It will set up
five bind variables named after these columns. Normally, in the absence of any functions, the INSERT statement it
builds is simply:
INSERT INTO DEPT ( DEPTNO, DNAME, LOC, LAST_UPDATED, ENTIRE_LINE )
VALUES ( :DEPTNO, :DNAME, :LOC, :LAST_UPDATED, :ENTIRE_LINE );
It would then parse the input stream, assigning the values to its bind variables, and then execute the statement.
When you begin to use functions, SQLLDR incorporates them into its INSERT statement. In the preceding example,
the INSERT statement SQLLDR builds will look like this:
INSERT INTO T (DEPTNO, DNAME, LOC, LAST_UPDATED, ENTIRE_LINE)
VALUES ( :DEPTNO, upper(:dname), upper(:loc), :last_updated,
:deptno||:dname||:loc||:last_updated );