Previous | Table of Contents | Next

Page 234

continued
SQL*LOADER SQL*LOADER-926: Oci error while executing deleting (due to REPLACE keyword) from table for table DEPARTMENT
ORA-02292: integrity constraint (SCOTT.SYS_C00388) violated _ child record found
This error message resulted in the attempt to delete the existing department records while the database is populated and constraints exist. Records cannot be deleted from the department table until the records are deleted from the employee table. A foreign key constraint exists on the fk_department column of the employee table.

FIG. 10.6
A control file that uses
the replace load style to
delete the table records
before loading new
ones.



Defining the Data Format

You may encounter many types of data formats, but three are the most common and are discussed in this section. The first is the fixed-position format (discussed earlier in this chapter). Second is a comma-delimited format, and third is quotation-delimited. Most software packages can output data in one of these delimited formats.

In the comma-delimited format, each field on the load row is separated from the other fields by a comma. This is a common data format for when converting data. You will often see files that contain this type of data format. Files using this format have an extension of .CSV.

In the quotation-delimited format, each field is enclosed by quotation marks. Most software packages have the ability to output their data in delimited formats. I find these format the easiest to define in a control file.

The load data format and table columns definitions follow the into clause. The definition of a fixed-position row consists of the field that will receive the data, followed by the data type and location on the physical record.

Delimited data formats don't need to have the positions recorded in the column definition. When using either of the delimited formats SQL*LOADER knows (by the mark) where to start and stop each field. This means you don't have to count columns to determine the start and stop position of fields.

When you're using the fixed-position data format, it's easy to make a mistake determining where the field starts and stops. It is especially easy to make errors if some records have null values because you can't visually identify the fields.

Page 235

The delimited records allow you to avoid this problem because they don't use column position. This means that you can document the column names in the same order that the values appear in the load row.

Finally, when specifying data fields, a date picture format is usually listed in the control file data specification. This lets Oracle know how the data is formatted.

Defining Condition Logic in Your Control Files

SQL*LOADER control files can have record-selection logic placed in them. This allows the developer to have the procedure evaluate each record in the data file and decide whether to allow the record to be added to the table. This is useful when the load table contains records with bad data.

You can use the control file to reject these records, correct them in the discard file, and add them to the table by using the append loading style. This is useful when you want to load several tables during one load procedure. You can use mutually exclusive conditions so the procedure loads only one table in a set of tables.

You write the logic by using a when conditional clause, which must evaluate the record as true for the record to be loaded. If the record is evaluated as false, the record is placed in the discard file.

Figure 10.7 shows a load program that will load records into the Tool file only for employee number 25.

FIG. 10.7
Using a when clause to
limit the records
entered into the Tools
file.



The when clause follows immediately after the into clause. The program illustrated in Figure 10.7 is slightly different from the ones you saw earlier in this chapter. In this example, the data is in the control file and the data is comma-delimited.

The when clause may also be used with fixed-position formatted data. Any of the SQL evaluation operators discussed earlier in this chapter may be used in this expression.

You can use multiple conditions in the when clause. The expressions should be separated by the and operator.

Note that the or operator isn't used between expressions in the when clause. This means that each of the expressions in the clause must be true.

Page 236

Figure 10.8 shows a control file that contains a when clause that contains multiple expressions.

FIG. 10.8
Using multiple
expressions in the when
clause to limit the
number of records
loaded into the table.



Loading Multiple Tables Using the Same Load Procedure

You can load several tables simultaneously by using the same physical record. This is done by using multiple into clauses in the control file. This is a handy feature when you load records that come from a de-normalized database.

Tables designed by inexperienced users often have multiple fields defined to hold repeating values. This is done because the developer found it easier to work with one table rather than several related tables.

An example is an employee record with four sets of fields to record eyeglass purchases. The multiple-table loading features of SQL*LOADER allow you to reorganize the data from the de-normalized physical record file into normalized logical records.

SQL*LOADER Oracle knows to load multiple tables because of the into clauses. Each of these clauses contains a data format description. These descriptions tell Oracle where the data begins.

In Figure 10.9, you can see a control file with a physical record that contains both an eyeglass purchase record and a tool purchase record. The file has two into clauses. The first clause is used to load a record into the Glasses table and the second clause loads records into the Tools table.

FIG. 10.9
Loading two tables
using the same control
file.


Page 237

As SQL*LOADER scans each comma-delimited data row, it places each value into the corresponding field in the data definition. When it reaches the end of the data definition, SQL*LOADER loads the record into the Glasses table.

SQL*LOADER then reaches the second into clause and begins scanning the logical record from the point where it left off. SQL*LOADER places each value into the corresponding field. When the end of the definition is reached, SQL*LOADER loads the record into the Tools table.

NOTE
Both the Glasses table and Tools table need a value for the fk_payroll_number fields. The logical record has only one value for the payroll number. In order to populate the fk_payroll_number field in the second into clause, the control file uses the position keyword to begin scanning from the indicated position.n

The fixed-position data format gives you more flexibility to create multiple records and add them to multiple tables or even to the same table. This is because you don't have to rely on sequentially scanning the logical record as you do when using delimited file formats.

SQL*LOADER expects the next value to follow the last one scanned. The values for fixed-position logical records allows you to specify the exact location of the data. You can use the same data values repeatedly or jump around the physical record to select the values you needed.

Figure 10.10 shows a control file that creates two eyeglass purchase records for each logical record read into the procedure.

FIG. 10.10
Using the fixed-position,
data format definition
to create multiple
records for the same
table.



In this example, the data definition for the second into clause shows that you can skip around the physical record, picking up values as needed. The fk_payroll_number field is listed third and is read from the beginning of the record.

This field was read once before in the previous into clause. In addition, the cost and check_number values are scanned before the optician value.

The functionality discussed in this section, coupled with the when clause, gives you a high degree of flexibility to rearrange data during the loading procedures. The alternative to using SQL*LOADER to rearrange the data is to load the physical record into a table and using
PL/SQL to load the various tables.

Previous | Table of Contents | Next