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

This control file does not employ the FIELDS TERMINATED BY clause; rather, it uses POSITION to tell SQLLDR
where fields begin and end. Note that with the POSITION clause, we could use overlapping positions and go back and
forth in the record. For example, suppose we were to alter the DEPT table as follows:
EODA@ORA12CR1> alter table dept add entire_line varchar2(29);
Table altered.
And then we used the following control file:
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
( DEPTNO position(1:2),
DNAME position(3:16),
LOC position(17:29),
ENTIRE_LINE position(1:29)
)
BEGINDATA
10Accounting Virginia,USA
The field ENTIRE_LINE is defined as POSITION(1:29) . It extracts its data from all 29 bytes of input data, whereas
the other fields are substrings of the input data. The outcome of this control file will be as follows:
EODA@ORA12CR1> select * from dept;
DEPTNO DNAME LOC ENTIRE_LINE
---------- -------------- ------------- -----------------------------
10 Accounting Virginia,USA 10Accounting Virginia,USA
When using POSITION , we can use relative or absolute offsets. In the preceding example, we used absolute offsets.
We specifically denoted where fields begin and where they end. We could have written the preceding control file as
follows:
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
( DEPTNO position(1:2),
DNAME position(*:16),
LOC position(*:29),
ENTIRE_LINE position(1:29)
)
BEGINDATA
10Accounting Virginia,USA
The * instructs the control file to pick up where the last field left off. Therefore (*:16) is just the same as (3:16)
in this case. Notice that you can mix relative and absolute positions in the control file. Additionally, when using the
* notation, you can add to the offset. For example, if DNAME started 2 bytes after the end of DEPTNO , we could have used
(*+2:16) . In this example, the effect would be identical to using (5:16) .