Pre-26ai Collection Configuration

Pre-26ai collections use default metadata unless custom metadata is passed to the create collection operation. The metadata is stored separately from the collection in the data dictionary.

Note: Default metadata can vary depending on the Oracle Database release, COMPATIBLE setting, and whether the database is Autonomous or conventional.

Default Pre-26ai Collection

For a conventional (i.e. non-Autonomous) 19c database, the default metadata is as follows:

{
  "schemaName" : "mySchemaName",
  "tableName" : "myTableName",
  "keyColumn" :
  {
    "name" : "ID",
    "sqlType" : "VARCHAR2",
    "maxLength" : 255,
    "assignmentMethod" : "UUID"
  },
  "contentColumn" :
  {
    "name" : "JSON_DOCUMENT",
    "sqlType" : "BLOB",
    "compress" : "NONE",
    "cache" : true,
    "encrypt" : "NONE",
    "validation" : "STANDARD"
  },
  "versionColumn" :
  {
    "name" : "VERSION",
    "method" : "SHA256"
  },
  "lastModifiedColumn" :
  {
    "name" : "LAST_MODIFIED"
  },
  "creationTimeColumn" :
  {
    "name" : "CREATED_ON"
  },
  "readOnly" : false
}

This metadata describes the five columns of the backing table and how they are maintained by SODA:

Column	Stores	Maintained by SODA
ID	Unique document key	Generated on insert
JSON_DOCUMENT	Document content	—
VERSION	Document version	Automatically updated on every write; supports optimistic locking
CREATED_ON	Creation timestamp	Set on insert
LAST_MODIFIED	Last-modified timestamp	Automatically updated on every write

On an Autonomous database, the default metadata differs in two ways:

• The version column uses UUID rather than SHA256, which is more efficient.
• The content column uses OSON, a binary JSON format optimized for Oracle Database.

Custom Pre-26ai Collections

You can override the default configuration by passing custom JSON metadata to the create collection operation.

The following SODA for PL/SQL example creates a pre-26ai collection with client-assigned keys and UUID-based versioning, and all the other metadata components at default settings:

DECLARE
    collection SODA_COLLECTION_T;
    metadata   VARCHAR2(4000) :=
      '{"keyColumn" :
         {"name" : "ID",
          "assignmentMethod" : "CLIENT"},
        "contentColumn" :
         {"name" : "JSON_DOCUMENT"},
        "versionColumn" :
         {"name" : "VERSION",
          "method" : "UUID"},
        "lastModifiedColumn" :
         {"name" : "LAST_MODIFIED"},
        "creationTimeColumn" :
         {"name" : "CREATED_ON"}}';
BEGIN
    collection := DBMS_SODA.create_collection('myCustomCollection', metadata);
END;
/

Pre-26ai collections can also be configured to store non-JSON documents by adding a media type column, as shown in the following example:

DECLARE
    collection SODA_COLLECTION_T;
    metadata   VARCHAR2(4000) :=
      '{"keyColumn" :
         {"name" : "ID",
          "assignmentMethod" : "UUID"},
        "contentColumn" :
         {"name" : "JSON_DOCUMENT"},
        "mediaTypeColumn" :
         {"name" : "MEDIA_TYPE"},
        "versionColumn" :
         {"name" : "VERSION",
          "method" : "UUID"},
        "lastModifiedColumn" :
         {"name" : "LAST_MODIFIED"},
        "creationTimeColumn" :
         {"name" : "CREATED_ON"}}';
BEGIN
    collection := DBMS_SODA.create_collection('myHeterogeneousCollection',
                                              metadata);
END;
/