Database Reference
In-Depth Information
The resulting data in the database will be as follows:
EODA@ORA12CR1> select * from dept;
DEPTNO DNAME LOC LAST_UPDA ENTIRE_LINE
---------- -------------- ------------- --------- -----------
10 SALES VIRGINIA 01-MAY-14
20 ACCOUNTING VIRGINIA 21-JUN-14
30 CONSULTING VIRGINIA 05-JAN-13
40 FINANCE VIRGINIA 15-MAR-14
Notice how you are able to easily uppercase the data just by applying the
UPPER
function to a bind variable. It
should be noted that the SQL functions could refer to any of the columns, regardless of the column the function is
actually applied to. This means that a column can be the result of a function on two or more of the other columns. For
example, if you wanted to load the column
ENTIRE_LINE
, you could use the SQL concatenation operator. It is a little
more involved than that, though, in this case. Right now, the input data set has four data elements in it. Let's say that
you were to simply add
ENTIRE_LINE
to the control file like this:
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
(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
You would find this error in your log file for each input record:
Record 1: Rejected - Error on table DEPT, column ENTIRE_LINE.
Column not found before end of logical record (use TRAILING NULLCOLS)
