Page 380
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 processed .
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 evaluates to false in the current physical record, then it becomes the last physical record of the current logical record.
NOTE |
Be aware of the following when using CONTINUEIF: |
Page 381
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 overrides the global table-loading method you specified previously in the control; the override is valid only for the table referenced in the INTO TABLE clause.
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.
Fixed-format input data file fields are usually defined by explicit byte position notation, (start:end), whereas variable-format input data file fields are usually relative to each other and separated by field termination characters . You can define the default field termination character for the file in the FIELDS clause. You can override the default at the field level by specifying the field termination character after the column name. Listing 16.5 contains an example of the usage of the FIELDS clause.
Page 382
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 path and the data file was presorted on indexed columns, you can specify the SORTED INDEXES clause, followed by the names of the indexes in parentheses. The indexes listed in the SORTED INDEXES clause must be created before you begin the direct load, or SQL*Loader returns an error. If you specify an index in the SORTED INDEXES clause and the data was not properly sorted for that index, the index is left in the direct load state at the end of the load. You must drop and re-create the index before you can use it.
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.
Page 383
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 enclosed by parentheses and separated by commas. Each column name must correspond to a column in the table that was named in the INTO TABLE clause. Figure 16.13 shows examples of column specifications.
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.