You can create one logical record from multiple physical records by using the CONCATENATE or the CONTINUEIF keyword.
If the number of records to combine remains constant throughout the data file, you can use the CONCATENATE keyword, followed by an integer, which indicates the number of records to combine. An example of CONCATENATE is shown in Figure 16.10.
If the number of physical records varies, you must use the CONTINUEIF keyword, followed by a condition that is evaluated for each record as it is
Examples of CONTINUEIF are shown in Figure 16.11.
In all three cases, with the THIS, NEXT, and LAST keywords, the condition is evaluated in the current physical record. If the condition is true, SQL*Loader reads the next physical record, concatenating it to the current physical record and continuing until the condition is false. If the condition
Be aware of the following when using CONTINUEIF:
The INTO TABLE clause is the section of the LOAD DATA statement that contains the bulk of the control file syntax. The INTO TABLE clause contains these items:
The INTO TABLE clause begins with the keywords INTO TABLE, followed by the name of the table into which the data is to be loaded. (You must have previously created the table.)
You can include a table-specific loading method in the INTO TABLE clause. If you use the INSERT, APPEND, REPLACE, or TRUNCATE method, it
You can include a table-specific OPTIONS clause in the INTO TABLE clause. The OPTIONS clause is valid only for parallel loads, and it overrides the FILE specification (the only option you can specify at the table level) in the global OPTIONS clause at the beginning of the control file.
You specify record selection criteria through the use of a WHEN clause, which is followed by field conditions. The WHEN clause can contain multiple comparisons; they must be separated by an AND. SQL*Loader determines the values of the fields in the logical record and then evaluates the WHEN clause. The row is inserted only if the WHEN clause evaluates to true. Examples of usage of the WHEN clause are shown in Figure 16.12.
Listing 16.5. Usage of the FIELDS clause.
INTO TABLE emp WHEN empno > 1000 FIELDS TERMINATED BY WHITESPACE TRAILING NULLCOLS ( emp position(1) integer external terminated by `', ename char terminated by whitespace, deptno integer external terminated by `' )
When more fields are specified in the control file than are present in the physical record, you must instruct SQL*Loader to either treat the missing fields as null columns or generate an error. When you use relative positioning, the record might end before all the fields are found. The TRAILING NULLCOLS clause instructs SQL*Loader to treat any relatively positioned columns not present in the record as null columns. See Listing 16.5 for usage of the TRAILING NULLCOLS clause.
If you load the data using the direct
A field condition is an expression about a field in a logical record that evaluates to true or false. You use field conditions with the NULLIF and DEFAULTIF keywords, as well as the WHEN clause. NULLIF sets the column to null if the expression evaluates to true, whereas DEFAULTIF sets the column to zero or blank. The BLANKS keyword enables you to easily compare any length field to determine if it is entirely blank. Listing 16.6 contains examples of NULLIF and DEFAULTIF.
Listing 16.6. NULLIF and DEFAULTIF.
dept no POSITION (1:2) integer external NULLIF (dept = BLANKS) comm POSITION (50:57) integer external DEFAULTIF (hiredate > '01-jan-94')
The datatype specifications in the column specification of the control file tell SQL*Loader how to interpret the data in the data file. The column definition in the data dictionary defines the datatype for each column in the database. The link between the data in the data file and the database column is the column name specified in the control file.
Any number of a table's columns can be loaded, providing the unloaded columns were not created with NOT NULL constraints. Columns defined for the table but not specified in the control file are assigned null values when the row is inserted.
The column specification is the column name followed by a specification for the value to be put into the column. The list of columns is
You can set the column value in one of two ways. The value can be read from the data file by specifying either an explicit position notation (start:end) for fixed-format files or a relative position notation for variable-format files. The second way is to generate the value using the SQL*Loader functions CONSTANT, RECNUM, SYSDATE, or SEQUENCE. You can use these SQL*Loader functions for both the conventional path and the direct path. Figure 16.14 shows the syntax for the SQL*Loader functions.