Loading Objects, Collections, and LOBs - Oracle 8i

You can use SQL*Loader to bulk load objects, collections, and LOBs. It is assumed that you are familiar with the concept of objects and with Oracle’s implementation of object support.

Supported Object Types
SQL*Loader supports loading of the following two object types:

When a column of a table is of some object type, the objects in that column are referred to as column-objects. Conceptually such objects are stored in entirety in a single column position in a row. These objects do not have object identifiers and cannot be referenced.

row objects
These objects are stored in tables, known as object tables, that have columns corresponding to the attributes of the object. The object tables have an additional system-generated column, called SYS_NC_OID$, that stores system-generated unique identifiers (OIDs) for each of the objects in the table. Columns in other tables can refer to these objects by using the OIDs.

Supported Collection Types
SQL*Loader supports loading of the following two collection types:

Nested Tables
A nested table is a table that appears as a column in another table. All operations that can be performed on other tables can also be performed on nested tables.

VARRAYs are variable sized arrays. An array is an ordered set of built-in types or objects, called elements. Each array element is of the same type and has an index, which is a number corresponding to the element’s position in the VARRAY. When creating a VARRAY type, you must specify the maximum size. Once you have declared a VARRAY type, it can be used as the datatype of a column of a relational table, as an object type attribute, or as a PL/SQL variable.

Supported LOB Types
A LOB is a large object type. This release of SQL*Loader supports loading of four LOB types:

  • BLOB: a LOB containing unstructured binary data.
  • CLOB: a LOB containing single-byte character data.
  • NCLOB: a LOB containing fixed-size characters from a national character set.
  • BFILE: a BLOB stored outside of the database tablespaces in a server-side OS file.

LOBs can be column datatypes, and with the exception of the NCLOB, they can be an object’s attribute datatypes. LOBs can have an actual value, they can be NULL, or they can be "empty."

SQL*Loader DDL Behavior and Restrictions
In order to provide object support, the behavior of certain DDL clauses and certain restrictions is different starting with release 8.1.5. The following list describes these changes. The changes apply in all cases, not just when you are loading objects, collections, or LOBs.

  1. Records:
    • 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 physical record size.
    • Logical records must fit completely into the client’s available memory. This excludes any data that is part of a particular record, but which is read from a secondary datafile. This logical record size restriction also applies to subrecords within secondary data files (SDFs).
  2. Record formats:
    • Stream record format In stream record format, the newline character marks the end of a physical record. Starting with release 8.1, you can specify a custom record separator in the operating system file-processing string (os_file_proc_clause).
    • Variable record format The usual syntax of following the INFILE directive with the "var" string has been extended to include the number of characters, at the beginning of each record, which are to be interpreted as the record length specifiers. If no value is specified, the default is 5 characters. The maximum size of a variable record is 2^32-1; specifying larger values will result in an error.
  4. If the field_condition is true, the DEFAULTIF clause initializes the LOB or collection to empty (not null). If the field_condition is true, the NULLIF clause initializes the LOB or collection to null, as it does for other datatypes.

    You can chain field_condition arguments using the AND logical operator. Note the following:

    • A NULLIF or DEFAULTIF clause cannot refer to a field in a secondary data file (SDF) unless the clause is on a field in the same secondary data file.
    • NULLIF or DEFAULTIF field conditions cannot be based on fields read from LOBFILEs.
  5. Field delimiters
  6. In previous versions of SQL*Loader, you could load fields that were delimited (terminated or enclosed) by a character. Beginning with release 8.1.5, the delimiter can be one or more characters long. The syntax to specify delimited fields remains the same, except that you can specify entire strings of characters as delimiters.

    As with single-character delimiters, when you specify string delimiters, you should consider the character set of the datafile. When the character set of the datafile is different than that of the control file, you can specify the delimiters in hexadecimal (that is, X’<hexadecimal string>’). If the delimiters are specified in hexadecimal notation, the specification must consist of characters that are valid in the character set of the input datafile. In contrast, if hexadecimal specification is not used, the delimiter specification is considered to be in the client’s (that is, the control file’s) character set. In this case, the delimiter is converted into the datafile's character set before SQL*Loader searches for the delimiter in the datafile.

    Note the following:

    • Stutter syntax is supported with string delimiters as it was with single-character delimiters (that is, the closing enclosure delimiter can be stuttered).
    • Leading whitespaces in the initial multicharacter enclosure delimiter are not allowed.
    • If a field is terminated by WHITESPACE, the leading whitespaces are trimmed.
  7. SQL strings
  8. SQL strings are not supported for LOBs, BFILEs, object columns, nested tables, or VARRAYS; therefore, you cannot specify SQL strings as part of a FILLER field specification.

  9. Filler fields
  10. To facilitate loading, you have available a new keyword, FILLER. You use this keyword to specify a FILLER field, which is a datafile mapped field that does not correspond to a database column.

    The FILLER field is assigned values from the datafield to which it is mapped. The FILLER field can be used as an argument to a number of functions, for example, NULLIF.

    The syntax for a FILLER field is same as that for a column-based field, except that a FILLER field's name is followed by the keyword FILLER.

    Filler fields can be used in field condition specifications in NULLIF, DEFAULTIF, and WHEN clauses. However, they cannot be used in SQL strings.

    Filler field specifications cannot contain a NULLIF/DEFAULTIF clause.

    Filler fields are initialized to NULL if the TRAILING NULLCOLS is specified and applicable. If another field references a nullified FILLER field, an error is generated.

    SQL*Loader DDL Support for LOBFILES and Secondary Data Files (SDFs)
    The data to be loaded into some of the new datatypes, like LOBs and collections, can potentially be very lengthy. Consequently, it is likely that you will want to have such data instances out of line from the rest of the data. LOBFILES and secondary data files (SDFs) provide a method to separate lengthy data.

  12. LOBFILES are relatively simple datafiles that facilitate LOB loading. The attribute that distinguishes LOBFILEs from the primary datafiles is that in LOBFILEs there is no concept of a record. In LOBFILEs the data is in any of the following type fields:

    • Predetermined size fields (fixed length fields)
    • Delimited fields (that is, TERMINATED BY or ENCLOSED BY)
    • Note: The clause PRESERVE BLANKS is not applicable to fields read from a LOBFILE.

    • Length-value pair fields (variable length fields)—VARRAW, VARCHAR, or VARCHARC loader datatypes—are used for loading from this type of field
    • A single LOB field into which the entire contents of a file can be read

    Note: A field read from a LOBFILE cannot be used as an argument to a clause (for example, the NULLIF clause).

  13. Secondary Data Files (SDFs)
  14. Secondary data files (SDFs) are similar in concept to primary datafiles. Like primary datafiles, SDFs are a collection of records, and each record is made up of fields. The SDFs are specified on a per control-file-field basis.

    You use the SDF keyword to specify SDFs. The SDF keyword can be followed by either the file specification string, or a FILLER field that is mapped to a datafield containing one or more file specification strings.

    As for a primary datafile, the following can be specified for each SDF:

    • The record format (fixed, stream, or variable). Also, if stream record format is used, you can specify the record separator.
    • The character set for a SDF can be specified using the CHARACTERSET clause.
    • A default delimiter (using the delimiter specification) for the fields that inherit a particular SDF specification (all member fields/attributes of the collection that contain the SDF specification, with exception of the fields containing their own LOBFILE specification).
    • To load SDFs larger than 64K, you must use the READSIZE parameter to specify a larger physical record size. You can specify the READSIZE parameter either from the command line or as part of an OPTIONS directive.

Full Field Names
Be aware that with SQL*Loader support for complex datatypes like column-objects, the possibility arises that two identical field names could exist in the control file, one corresponding to a column, the other corresponding to a column object’s attribute. Certain clauses can refer to fields (for example, WHEN, NULLIF, DEFAULTIF, SID, OID, REF, BFILE, and so on), causing a naming conflict if identically named fields exist in the control file.

Therefore, if you use clauses that refer to fields, you must specify the full name. For example, if field fld1 is specified to be a COLUMN OBJECT and it contains field fld2, when specifying fld2 in a clause such as NULLIF, you must use the full field name fld1.fld2.

When to Use LOBFILEs or SDFs
An example situation in which you might use LOBFILES or SDFs would be if you needed to load employee names, employee IDs, and employee resumes. You could read the employee names and IDs from the main datafiles and you could read the resumes, which can be quite lengthy, from LOBFILEs.

Dynamic Versus Static LOBFILE and SDF Specifications
You can specify SDFs and LOBFILEs either statically (you specify the actual name of the file) or dynamically (you use a FILLER field as the source of the filename). In either case, when the EOF of an SDF or LOBFILE is reached, the file is closed and further attempts at sourcing data from that particular file produce results equivalent to sourcing data from an empty field.

In the case of the dynamic secondary file specification, this behavior is slightly different. Whenever the specification changes to reference a new file, the old file is closed and the data is read from the beginning of the newly referenced file.

The dynamic switching of the datasource files has a resetting effect. For example, when switching from the current file to a previously opened file, the previously opened file is reopened, and the data is read from the beginning of the file.

You should not specify the same SDF or LOBFILE as the source of two different fields. If you do so, typically, the two fields will read the data independently.


  • If a nonexistent SDF or LOBFILE is specified as a data source for a particular field, that field is initialized to empty. If the concept of empty does not apply to the particular field type, the field is initialized to null.
  • The POSITION directive cannot be used in fields that read data from LOBFILEs.
  • Table level delimiters are not inherited by fields that are read from an SDF or LOBFILE.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 8i Topics