Using SQL*Loader

You can use SQL*Loader to bulk load LOBs.

See: Chapter 4, "Managing LOBs", "Using SQL*Loader to Load LOBs", for a brief description and examples of using SQL*Loader. Oracle9i Utilities for a detailed description of using SQL*Loader to load LOBs.

Loading XML Documents Into LOBs With SQL*Loader

Because LOBs can be quite large, SQL*Loader can load LOB data from either the main datafile (inline with the rest of the data) or from LOBFILEs. Figure 9-1 shows the LOBFILE syntax.

Figure 9-1 The LOBFILE Syntax

Text description of the illustration apa30.gif

LOB data can be lengthy enough that it makes sense to load it from a LOBFILE. In LOBFILEs, LOB data instances are still considered to be in fields (predetermined size, delimited, length-value), but these fields are not organized into records (the concept of a record does not exist within LOBFILEs). Therefore, the processing overhead of dealing with records is avoided. This type of organization of data is ideal for LOB loading.

There is no requirement that a LOB from a LOBFILE fit in memory. SQL*Loader reads LOBFILEs in 64K chunks. To load physical records larger than 64K, you can use the READSIZE parameter to specify a larger size.

It is best to load XMLType columns or columns containing XML data in CLOBs, using LOBFILEs.

• When the XML is valid. If the XML data in the LOBFILE is large and you know that the data is valid XML, then use direct-path load since it bypasses all the XML validation processing.

• When the XML needs validating. If it is imperative that the validity of the XML data be checked, then use conventional path load, keeping in mind that it is not as efficient as a direct-path load.

A conventional path load executes SQL INSERT statements to populate tables in an Oracle database. A direct path load eliminates much of the Oracle database overhead by formatting Oracle data blocks and writing the data blocks directly to the database files.

A direct-path load does not compete with other users for database resources, so it can usually load data at near disk speed. Considerations inherent to direct path loads, such as restrictions, security, and backup implications, are discussed in Chapter 9 of Oracle9i Utilities.

Figure 9-2 illustrates SQL*Loader's direct-path load and conventional path loads.

Tables to be loaded must already exist in the database. SQL*Loader never creates tables. It loads existing tables that either already contain data or are empty.

The following privileges are required for a load:

• You must have INSERT privileges on the table to be loaded.

• You must have DELETE privilege on the table to be loaded, when using the REPLACE or TRUNCATE option to empty out the table's old data before loading the new data in its place.

  See Also: Oracle9i Utilities. Chapters 7 and 9 for more information about loading and examples.

Figure 9-2 SQL*Loader: Direct-Path and Conventional Path Loads

Text description of the illustration sut81018.gif