Oracle SQL*Loader: The Definitive Guide

Chapter 12. Loading Objects and Collections

Oracle Corporation is continually adding new features to their database. Some of the more recent additions, which debuted with Oracle8, include support for objects and collections. To keep up with these database enhancements, SQL*Loader has also been enhanced. If you have object tables, or tables with object columns, you can populate them with data using SQL*Loader. SQL*Loader can also load data into columns containing data in the form of nested tables and varying arrays—the two collection types.

Tip

In Oracle8i, objects and collections can only be loaded via a conventional path load. In Oracle9i, under most circumstances, you’ll also be able to load objects and collections in a direct path load.

Loading Object Tables and Columns

The release of Oracle8 saw the introduction of objects into the Oracle database. Support of objects was such a revolutionary event that Oracle Corporation started referring to their database as an object-relational database . You now have the ability to define an object type , which is the equivalent of what you would term a class in a Java or C++ environment, and to then create tables or columns based on that type. For example, the following SQL statement creates a simple object type named feature_type :

CREATE OR REPLACE TYPE feature_type AS OBJECT (
   feature_name VARCHAR2(60),
   feature_type VARCHAR2(12),
   feature_elevation NUMBER
   );
/

This type can now be used as the basis for creating objects in the Oracle database. ...