Previous | Table of Contents | Next

Page 238

Using PL/SQL is a typical method for converting data. This method requires disk space to hold the temporary file and it requires the data to be read twice. You may find that using SQL*LOADER to rearrange your conversion data is preferable.

Other Control File Keywords and Clauses

A control file can have these parts:

The load data clause is the first clause needed in the control file. It indicates that the purpose of the procedure is to load data and that additional settings will follow the keywords. The infile clause holds the name of the data file with the logical records to be loaded.

When the data file is part of the control file, the keyword infile is followed by an asterisk (*). (The load style options were covered earlier in this chapter.) The remaining clauses are discussed in the following sections.

The Options and Recoverable/Unrecoverable Clauses

The options clause is another part of a control file. When it's used in a control file, the options clause is usually the first statement. It specifies runtime settings that affect the loading of data.

Table 10.1 shows the settings available.

Table 10.1 Load Options


Option Description
Bindsize = n Number of bytes in the bind array.
Direct = This option specifies the load path method. The options are true or false.
True causes a direct load path method. The direct path method disables all
database triggers and some of the table constraints during the loading
process. The other method is the conventional path. This method applies all
integrity constraints and insert triggers.

Page 239


Option Description
Errors = n The number of errors allowed before the procedure is terminated.
Load = n The number of records to load before the procedure is terminated.
Parallel = Tells SQL*LOADER Oracle to run multiple loading sessions. The values are true or false.
Rows = n The number of rows in the bind array used when doing a conventional load. The number of rows between saves during a direct load procedure.
Silent = Suppresses messages. Message options are header, feedback, discards, and all.
Skip = n Number of records to skip before loading the table.

You can set most of these options in the SQL*LOADER dialog box shown in Figure 10.3. You can't set the parallel option in this dialog box—you have to by using the options clause. This option allows you to load multiple input files concurrently into tablespace files.

The unrecoverable/recoverable clauses are used in direct load path method. When you specify the recoverable mode, the loaded data is written to the redo log. This means the data in the table can be restored.

The Into Table Clause

The into table clause is a necessary part component of the control file. It can contain a number of items, including the name of the table receiving data, the load style option, a when clause (discussed in the previous "Defining Condition Logic in your control file" section), a fields clause, a trailing nullcols clause, an index option, a fields condition expression, and a data-specification section.

The Fields ClauseThe fields clause is used to load variable-format data records such as comma-delimited records. The fields clause indicates the characters that identify the field boundaries.

Since the values don't have a fixed position, you have to specify how to identify the values. The fields clause uses two different sets of keywords. The first is terminated by and the second is enclosed by.

The terminated by keywords indicate the character that denotes the end of the value. In the case of comma-delimited records, the comma terminates the field.

The enclosed by keywords indicate the characters that denote the beginning and end of the value.

Figure 10.11 shows several versions of the fields clause.

The first example in the figure is used for comma-delimited records. The fields clause indicates that the value is completed by a comma so SQL*LOADER knows where the field values begin and end, based on the comma.

Page 240

The termination character can be other character types, including white space (space bar). The second fields example shows that white space terminates the field value.

FIG. 10.11
Examples of the Fields
Control File clause.




Whenever SQL*LOADER encounters white space, it knows to begin placing the next characters scanned into the next field in the data definition.

The third example uses the enclosed by keywords. This is used for physical records that are enclosed by a character.

In this example, the data record values are enclosed by double quotation marks. The fields clause has to specify the double quotes as the character that denotes the start and stop of the value.

The fourth example uses both the terminated by and enclosed by keywords. In this example, the fields can be terminated by a comma and enclosed by the pipe symbol (|). When SQL*LOADER encounters the pipe symbol, it knows that all the characters scanned until the next pipe symbol are part of the current value.

If SQL*LOADER encounters a comma before it reaches the concluding pipe symbol, it considers the comma a part of the value and not a field-termination character.

NOTE
Optionally enclosing a value is handy when some of the fields can't use the same delimiting device as the rest of the fields. For instance, values that contain multiple words such as an address (9814 Adams Road) are hard to load using a white-space as a termination character unless it can be enclosed.n

The Trailing Nullcols Clause, Index Options, and Field Condition ExpressionsSome of the figures shown in this chapter use the keywords called trailing nullcols. These keywords indicate that when it encounters a physical record that doesn't have as many values as fields in the data definition, SQL*LOADER should treat the missing fields as null columns. Without this clause, SQL*LOADER Oracle will consider the record as erroneous and put it in the bad file.

The Index option is used when the load data is sorted. This is specified by using the sorted indexes keywords. The table indexes are built directly from the incoming data; sorting routines are used on the index.

Previous | Table of Contents | Next