0380-0383

Previous Table of Contents Next

Page 380

CONCATENATION Clause

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.


Figure 16.10.
An example of
CONCATENATE.

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.


Figure 16.11.
Examples of
CONTINUEIF.

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:

  • The continuation fields are removed from the physical records before the logical record is created when you use the THIS and NEXT keywords.

  • The continuation character is not removed from the physical record when you use the LAST keyword; it remains a part of the logical record.

  • Any trailing blanks in the physical record are part of the logical record.

Page 381

INTO TABLE Clause

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:

  • Table name into which the data is to be loaded
  • Table-specific loading method
  • Table-specific OPTIONS clause
  • WHEN clause
  • FIELDS clause
  • TRAILING NULLCOLS clause
  • Index options
  • Field conditions
  • Relationship between data file fields and database columns

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.)

Table-Specific Loading Method

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.

Table-Specific OPTIONS 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.

WHEN Clause

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.

FIELDS Clause

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


Figure 16.12.
Examples of the WHEN
clause.

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 `'      ) 
TRAILING NULLCOLS Clause

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.

Index Options

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.

Field Conditions

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') 
Column Specifications

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.


Figure 16.13.
Column specifications.

Setting the Column Values

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.

Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net