Oracle7 Server Utilities

Data Conversion and Datatype Specification

Figure 3 - 4 shows the stages in which fields in the datafile are converted into columns in the database during a conventional path load. The top of the diagram shows a data record containing one or more fields. The bottom shows the database column in which the data winds up. It is important to understand the intervening steps when using SQL*Loader.

Figure 3 - 4 depicts the "division of labor" between SQL*Loader and the Oracle7 Server. The field specifications tell SQL*Loader how to interpret the format of the datafile. The Oracle7 Server then converts that data and inserts it into the database columns, using the column datatypes as a guide.

Figure 3 - 4. Field to Column Translation

Keep in mind the distinction between a field (in a datafile) and a column (in the database). It is also important to remember that the field datatypes defined in a SQL*Loader control file are not the same as the column datatypes.

SQL*Loader uses the field specifications in the control file to recognize data and creates a SQL insert statement using that data. The insert statement is then passed to the Oracle7 Server to be stored in the table. The Oracle7 Server uses the datatype of the column to convert the data into its final, stored form.

In actuality, there are two conversion steps:

1. SQL*Loader identifies a field in the datafile, interprets the data, and passes it to the Oracle7 Server.

2. The Oracle7 Server accepts the data and stores it in the database. In Figure 3 - 5, two CHAR fields are defined for a data record. The field specifications are contained in the control file. Note that the control file CHAR specification is not the same as the database CHAR specification. A data field defined as CHAR in the control file merely tells SQL*Loader how to create the row insert. The data could then be inserted into a CHAR, VARCHAR2, or even a NUMBER column in the database, with the Oracle7 Server handling any necessary conversions.

Figure 3 - 5. Example of Field Conversion

By default, SQL*Loader removes trailing spaces from CHAR data before passing it to the database. So, in Figure 3 - 5, both field A and field B are passed to the database as three-column fields. When the data is inserted into the table, however, there is a difference.

Column A is defined in the database as a fixed-length CHAR column of length 5. So the data (aaa) is left justified in that column, which remains five characters wide. The extra space on the right is padded with blanks. Column B, however, is defined as a varying length field with a maximum length of five characters. The data for that column (bbb) is left-justified as well, but the length remains three characters.

The name of the field tells SQL*Loader what column to insert the data into. Because the first data field has been specified with the name "A" in the control file, SQL*Loader knows to insert the data into column A of the target database table.

It will be useful to keep the following points in mind:

The name of the data field corresponds to the name of the table column into which the data is loaded.

The datatype of the field tells SQL*Loader how to read the data in the datafile. It is not the same as the column datatype.

Data is converted from the datatype specified in the control file to the datatype of the column in the database.