Trailing spaces were preserved the county and short fields

	,ch04.24702 Page 88 Wednesday, April 11, 2001 1:44 PM
	88

	As we were researching this entire issue of how SQL*Loader handles whitespace, it occurred to us to try mixing the use of the PRESERVE BLANKS and TERMINATED BY WHITESPACE clauses. The TERMINATED BY WHITESPACE clause applies to delimited data, and is described in Chapter 5.

SQL*Loader’s default behavior

When you are loading columnar, character-based data, SQL*Loader’s default behavior is to recognize whitespace as representing a null value. Take a look at the following two rows of data:

	This is the Title of the Book, eMatter Edition
	Copyright © 2001 O’Reilly & Associates, Inc. All rights reserved.

		89
	Handling Anomalous Data

3. SQL*Loader checks to see if the resulting field length is zero.

If a character field consists entirely of spaces, then after those spaces have been trimmed off, the resulting field length will be zero. SQL*Loader interprets zero-length character fields as nulls.

	as	INTEGER	EXTERNAL	and	DECIMAL	EXTERNAL	are

	None of what we’ve said here applies when you are loading binary data, or when
	you are using nonportable datatypes. SQL*Loader only trims whitespace on character
	fields. Nonportable datatypes such as INTEGER or FLOAT represent binary data—

whitespace won’t be trimmed, and the length will never be zero. The only way to derive null values from such fields is to use the NULLIF and DEFAULTIF clauses.

The effect of PRESERVE BLANKS

	This is the Title of the Book, eMatter Edition
	Copyright © 2001 O’Reilly & Associates, Inc. All rights reserved.