Mapping the Data to Oracle Format

The Concept of Mapping Data

• specifications for loading logical records into tables

• field condition specifications

• column and field specifications

• data field position specifications

• datatype specifications

• bind array size specifications

• specifications for setting columns to null or zero

• specifications for loading all-blank fields

• specifications for trimming blanks and tabs

• specifications to preserve whitespace

• specifications for applying SQL operators to fields

The Data Definition Language (DDL)

The syntax and semantics of DDL is explained in detail in Chapter 5.

SQL*Loader DDL is upwardly compatible with the DB2 Load Utility from IBM. If you have a control file for the DB2 Load Utility, you can also use it with SQL*Loader. See Appendix C, "Notes for DB2/DXT Users," for differences in syntax.

DDL Definitions

A single DDL definition is composed of one or more keywords and the arguments and options that modify that keyword's functionality. An example of a control file containing several definitions defining how SQL*Loader should interpret a simple datafile might look like this:

	LOAD DATA

	INFILE 'example.dat'

	INTO TABLE emp

	(empno         POSITION(01:04)  INTEGER EXTERNAL,

	ename          POSITION(06:15)  CHAR,

	job            POSITION(17:25)  CHAR,

	mgr            POSITION(27:30)  INTEGER EXTERNAL,

	sal            POSITION(32:39)  DECIMAL EXTERNAL,

	comm           POSITION(41:48)  DECIMAL EXTERNAL,

...

The keywords are LOAD DATA, INFILE, INTO TABLE, POSITION, etc. (shown in all capital letters).

The Control File

How you store control files depends on how your operating system organizes data. For example, in UNIX environments, control files are stored in files; in MVS environments, they can be stored as members in a partitioned dataset. They must be located where SQL*Loader has access to them.

Some DDL definitions are mandatory, such as where to find the data and how it corresponds to the database tables. However, many options are also available to describe and manipulate the file data. For example, the instructions can include directions on how to format or filter the data, or to generate unique ID numbers.

A control file can also contain the data itself as well as the DDL definitions, as shown in Case 1 , or in separate files, as shown in Case 2 . Detailed information on creating control files using DDL definitions is found .

Control File Guidelines

• The file is written in free format. That is, statements can continue from line to line with new lines beginning at any word.

• The file is written in uppercase or lowercase. Case is not significant except in strings specified with single or double quotation marks.

• Comments can be included by prefixing them with two hyphens, as in the following example:

		--This is a comment

• The double hyphens can appear anywhere on a line; SQL*Loader ignores anything from the double hyphens to the end of line. Comments should not appear in the datafile or the data portion of the control file. If a double dash appears in this area, it is treated as data.

• SQL*Loader reserved words must be enclosed in quotation marks if you want to use them for table or column names. (See Appendix B for a complete list of SQL*Loader reserved words.)

The Data

Binary versus Character Format Data

Binary data is one example of native datatypes -- datatypes that are implemented differently on different operating systems. For more information on these and other native datatypes, see "Native Datatypes" . SQL*Loader cannot handle binary data in variable record format. See also the section called "Loading Data Across Different Operating Systems" .

Data in character format can be included in both fixed-format and variable-format files. For more information on the character datatypes, see "Character Datatypes" .

Fixed versus Variable Format

Figure 3 - 1. Fixed Format Records

In this example, columns 1 to 6 contain a character variable while columns 7 to 10 contain an integer for all the records. The fields are the same size in each record, regardless of the length of the data. The fields are fixed length, rather than variable length. In consequence, the record size is also fixed at 10 characters for each of the records.

In variable format (sometimes called stream format), each record is only as long as necessary to contain the data. Figure 3 - 2 shows variable length records containing one varying-length character fields and one fixed length integer field.

Figure 3 - 2. Variable Format Records

In addition, the type of data in each record may vary. One record may contain a character string, the next may contain seven integers, the third may contain three decimals and a float, and so on. Operating systems use a record terminator character (such as newline) to mark where variable records end.

Data Fields

There are two types of delimited fields: terminated and enclosed. Terminated fields are followed by a specified character (called a termination delimiter), such as the commas in the following example:

1,1,2,3,5,8,13

Enclosed fields are both preceded and followed by specified characters (called enclosure delimiters), such as the quotation marks in the following example:

"BUNKY"

Case 2 shows fixed-length records. Case 1 shows delimited fields. For more details on delimited data, see "Specifying Delimiters" .

Logical versus Physical Records

Logical records, on the other hand, correspond to a row in a database table. Sometimes the logical and physical records are equivalent. Such is the case when only a few short columns are being loaded. However, sometimes several physical records must be combined to make one logical record. For example, you could have a file containing 24 10-character columns in a format of 80-character, fixed-length records. In this case, three physical records would constitute a single logical record.

SQL*Loader allows you to compose logical records from multiple physical records using continuation fields. Physical records are combined into a single, logical record when some condition of the continuation field is true. You can specify that a logical record should be composed of multiple, physical records in the following ways:

• A fixed number of physical records are concatenated to form a logical record (no continuation field is used).

• Physical records are appended if the continuation field contains a specified string (or another test, such as "not equal", succeeds when applied to the continuation field).

• Physical records are appended if they contain a specified character as their last non-blank character.