Oracle SQL*Loader: The Definitive Guide

Chapter 5. Loading Delimited Data

Delimited data is the second of the two major classifications of data that you can load using SQL*Loader. Delimited data is usually textual in nature. Fields are separated by strings of characters referred to as delimiters. Issues you’ll encounter when loading delimited data include the following:

• Some fields, notably text fields, may be enclosed by characters such as quotation marks.

• Fields may contain leading and/or trailing spaces that need to be trimmed away.

• Records may not have all the fields that the LOAD statement calls for.

This chapter shows you how to use SQL*Loader’s support for delimited data. You’ll learn how to define delimiters and enclosing characters, and also to deal with nested occurrences of both delimited and fixed-width fields.

Common Datatypes Encountered

Delimited data is almost always textual. Thus, the SQL*Loader datatypes that you’ll most commonly use are the external types:

CHAR

DATE

DECIMAL EXTERNAL

ZONED

ZONED EXTERNAL

INTEGER EXTERNAL

FLOAT EXTERNAL

Example Data

The example data used in this chapter will be a delimited version of the same geographic feature name data that you saw in Chapter 4. The exact data format will vary with the particular aspect of SQL*Loader under discussion at any given time.

The destination table used for all examples can be created using the following statement:

CREATE TABLE michigan_features ( feature_name VARCHAR2(44), elevation NUMBER, population NUMBER, feature_type VARCHAR2(10), county VARCHAR2(15), ...