Like LOBs, collections can be loaded either from a primary data file (data inline) or from secondary data files (data out of line). See "Secondary Data Files (SDFs)" for details about SDFs.
When you load collection data, a mechanism must exist by which SQL*Loader can tell when the data belonging to a particular collection instance has ended. You can achieve this in two ways:
To specify the number of rows or elements that are to be loaded into each nested table or VARRAY instance, use the DDL COUNT function. The value specified for COUNT must either be a number or a character string containing a number, and it must be previously described in the control file before the COUNT clause itself. This positional dependency is specific to the COUNT clause. COUNT(0) or COUNT(cnt_field), where cnt_field is 0 for the current row, results in a empty collection (not null), unless overridden by a NULLIF clause. See "count_spec".
If the COUNT clause specifies a field in a control file and if that field is set to null for the current row, then the collection that uses that count will be set to empty for the current row as well.
Use the TERMINATED BY and ENCLOSED BY clauses to specify a unique collection delimiter. This method cannot be used if an SDF clause is used.
In the control file, collections are described similarly to column objects. See "Loading Column Objects". There are some differences:
Collection descriptions employ the two mechanisms discussed in the preceding list.
Collection descriptions can include a secondary data file (SDF) specification.
A NULLIF or DEFAULTIF clause cannot refer to a field in an SDF unless the clause is on a field in the same SDF.
Clauses that take field names as arguments cannot use a field name that is in a collection unless the DDL specification is for a field in the same collection.
The field list must contain only one nonfiller field and any number of filler fields. If the VARRAY is a VARRAY of column objects, then the attributes of each column object will be in a nested field list.