SQL*Loader supports loading of the column and row object types.

• column objects
  When a column of a table is of some object type, the objects in that column are referred to as column objects.
• row objects
  These objects are stored in tables, known as object tables, that have columns corresponding to the attributes of the object.

SQL*Loader supports loading of nested tables and VARRAY collection types.

• Nested Tables
  A nested table is a table that appears as a column in another table.
• VARRAYs
  A VARRAY is a variable sized arrays.

SQL*Loader enables you to load external documents into SODA collections using the SQL*Loader utility in both control file and express modes.

Starting with Oracle Database 23ai, you can use SQL*Loader to load schemaless documents (documents that lack a fixed data structure, such as JSON or XML-based application data) into Oracle Database as SODA collections. A SODA (Simple Oracle Document Access) collection is a set of documents that is backed by an Oracle Database table or view. A document is stored in Oracle Database as a row in a table or view, with each component in its own column.

When you create a SODA document collection, the following is created in Oracle Database:

• Persistent default collection metadata.
• A table for storing the collection.

You can insert, append, and replace external documents into SODA collections in Oracle Database applications

To load a SODA collection, you supply one to three pieces of information to the SQL*Loader utility:

• $CONTENT: The content that you want to load (Required).

  This field can be an actual text document, or a secondary data file containing one or more documents. There are two types of content that you can specify:

  • RAW(*): Use the RAW(*) data field either when text documents are stored directly in the control or data file, or when the documents are specified in the INFILE clause.
  • CONTENTFILE(soda_filename): use the CONTENTFILE name to specify an secondary data file name (soda_filename) from which you want SQL*Loader to load the data. One or more documents can be contained in the secondary data file that you specify.

• $KEY: A key to identify the document (Optional)

  In a collection, each document must have a document key, which is unique for the collection. However, you do not need to provide a key if the SODA collection automatically generates keys. If $KEY is specified, then there is a one-to-one relationship between the key and the content.

• $MEDIA: A media type to describe the type of the content (Optional)

  $MEDIA is not required if the SODA collection is defined to hold documents of one media type. The default media type is JSON but this can be modified using the SODA_MEDIA keyword.

SQL*Loader supports multiple large object types (LOBs).

This release of SQL*Loader supports loading of four LOB data types:

• BLOB: a LOB containing unstructured binary data

• CLOB: a LOB containing character data

• NCLOB: a LOB containing characters in a database national character set

• BFILE: a BLOB stored outside of the database tablespaces in a server-side operating system file

LOBs can be column data types, and except for NCLOB, they can be an object's attribute data types. LOBs can have an actual value, they can be null, or they can be "empty."

JSON columns can be loaded using the same methods used to load scalars and LOBs