Oracle SQL*Loader: The Definitive Guide

Chapter 3. Fields and Datatypes

The subject of fields and datatypes cuts to the very core of SQL*Loader’s capabilities. The whole purpose of the tool is to extract information from a file and load it into a database. SQL*Loader’s power comes from its ability to handle a wide range of datatypes, and also from its flexibility in describing and manipulating the different fields in an input record.

Field Specifications

A field specification is that portion of an INTO TABLE clause describing a field to be extracted from the input record. Syntactically, each INTO TABLE clause contains a comma-delimited list of field specifications in the manner shown here:

INTO TABLE table_name
   (
   field_specification,
   field_specification,
   field_specification,
   ...
   )

This comma-delimited list of field specifications is always the last component of an INTO TABLE clause, and it’s always enclosed within parentheses. Field specifications typically describe fields to be extracted from records in a data file, but they can also describe values that you want SQL*Loader to generate for you. SQL*Loader supports the following five field types:

• Scalar fields

• Filler fields

• Generated fields

• Collection fields

• Column object fields

Scalar fields are the type you deal with most often on a daily basis. Character strings and numbers are typical examples of scalar fields. Filler fields function somewhat as placeholders. They are fields that you define for use within SQL*Loader, but that are not ultimately loaded into the database. ...