7 SODA Collection Metadata Components (Reference)

Collection metadata is composed of multiple components. A detailed definition of the components is presented.

Note:

The identifiers used for collection metadata components (schema name, table name, view name, database sequence name, and column names) must be valid Oracle quoted identifiers.^Foot 1 Some characters and words that are allowed in Oracle quoted identifiers are strongly discouraged. For details, see Oracle Database SQL Language Reference.

• Default Collection Metadata
  The kind of database you use determines the collection metadata that is used by default, and which of its field values you can modify for custom metadata.
• Schema
  The collection metadata component that specifies the name of the Oracle Database schema that owns the table or view to which the collection is mapped.
• Table or View
  The collection metadata component that specifies the name of the table or view to which the collection is mapped.
• Key Column Name
  The collection metadata component that specifies the name of the column that stores the document key.
• Key Column Type
  The collection metadata component that specifies the SQL data type of the column that stores the document key.
• Key Column Max Length
  The collection metadata component that specifies the maximum length of the key column in bytes. This component applies only to keys of type VARCHAR2.
• Key Column Assignment Method
  The collection metadata component that specifies the method used to assign keys to objects that are inserted into the collection.
• Key Column Path
  The collection metadata component that specifies the top-level document path to the field used as an embedded document key.
• Key Column Sequence Name
  The collection metadata component that specifies the name of the database sequence that generates keys for documents that are inserted into a collection if the key assignment method is SEQUENCE.
• Content Column Name
  The collection metadata component that specifies the name of the column that stores the database content.
• Content Column Type
  The collection metadata component that specifies the SQL data type of the column that stores the document content.
• Content Column Format
  The collection metadata component that specifies the format of the column that stores the document content.
• Content Column Max Length
  The collection metadata component that specifies the maximum length of the content column in bytes. This component applies only to content of type VARCHAR2.
• Content Column JSON Validation
  The collection metadata component that specifies the syntax to which JavaScript Object Notation (JSON) content must conform—strict or lax.
• Content Column SecureFiles LOB Compression
  The collection metadata component that specifies the SecureFiles LOB compression setting.
• Content Column SecureFiles LOB Cache
  The collection metadata component that specifies the SecureFiles LOB cache setting.
• Content Column SecureFiles LOB Encryption
  The collection metadata component that specifies the SecureFiles LOB encryption setting.
• Version Column Name
  The collection metadata component that specifies the name of the column that stores the document version.
• Version Column Generation Method
  The collection metadata component that specifies the method used to compute version values for objects when they are inserted into a collection or replaced.
• Last-Modified Time Stamp Column Name
  The collection metadata component that specifies the name of the column that stores the last-modified time stamp of the document.
• Last-Modified Column Index Name
  The collection metadata component that specifies the name of the index on the last-modified column.
• Creation Time Stamp Column Name
  The collection metadata component that specifies the name of the column that stores the creation time stamp of the document. This time stamp is generated during the insert, insertAndGet, save, or saveAndGet operation.
• Media Type Column Name
  The collection metadata component that specifies the name of the column that stores the media type of the document. A media type column is needed if the collection is to be heterogeneous, that is, it can store documents other than JavaScript Object Notation (JSON).
• Read Only
  The collection metadata component that specifies whether the collection is read-only.

7.1 Default Collection Metadata

The kind of database you use determines the collection metadata that is used by default, and which of its field values you can modify for custom metadata.

In particular, the default SQL data type of the column used to store JSON content (the content column), and the default method for computing object version values (the version column generation method), depend on your database.

If the Oracle Database you use is an Oracle Autonomous Database — Autonomous JSON Database (AJD), Autonomous Transaction Processing (ATP), or Autonomous Data Warehouse (ADW) — then SODA always uses Oracle's native JSON format, OSON, to store the JSON content. Fields contentColumn.sqlType and contentColumn.jsonFormat in the metadata reflect this. These fields are not customizable for an autonomous database.

• If database initialization parameter compatible is at least 20 then:

  • The value of field contentColumn.sqlType in the default metadata is "JSON". JSON data type uses OSON format.

  • The value of field versionColumn.method in the default metadata is "UUID".

• Otherwise (parameter compatible is less than 20).

  For an autonomous database:

  • The value of field contentColumn.sqlType is "BLOB". This Binary Large Object (BLOB) data uses Oracle's native JSON format, OSON. (For an autonomous database this field is not customizable.)

    The additional field contentColumn.jsonFormat is present, with value "OSON". (For an autonomous database this field is not customizable.)

    The value of field versionColumn.method is "UUID".

  For a nonautonomous database, that is, for an on-premise database or a nonautonomous cloud database:

  • The value of field contentColumn.sqlType in the default metadata is "BLOB". BLOB textual data is used for JSON content, by default. The data is character data encoded using a Unicode encoding, either UTF-8 or UTF-16.

  • The following additional contentColumn fields are present: compress, cache, encrypt, and validation.

  • The value of field versionColumn.method in the default metadata is "SHA256".

You can define custom metadata, whose values for some fields differ from the default values, as follows:

• If the database you use is an autonomous database, then:

  • You can change metadata field keyColumn.assignmentMethod to CLIENT (instead of the default value, UUID), to specify client-assignment of document keys.

  • If the autonomous database is Autonomous Transaction Processing (ATP) or Autonomous Data Warehouse (ADW), but not Autonomous JSON Database (AJD), then you can add metadata field mediaTypeColumn.name, to specify the name of the column that stores the media type of a document. (By default, this field is absent.) A media-type column is needed for a heterogeneous collection, that is, a collection that can store documents other than JSON documents.

• If the database you use is not an autonomous database, then you can customize any metadata fields.

Note:

You need certain versions of SODA drivers and related software to support collection content type that uses Oracle's native JSON binary format, OSON, that is, for JSON type or for BLOB type with format OSON. See SODA Drivers

7.2 Schema

The collection metadata component that specifies the name of the Oracle Database schema that owns the table or view to which the collection is mapped.

Property	Value
Default value	None
Allowed values	Valid Oracle quoted identifierFoot 1. If this value contains double quotation marks (") or control characters, SODA replaces them with underscore characters (_).
JSON collection metadata document path	schemaName

See Also:

Oracle Database SQL Language Reference for information about valid Oracle quoted identifiers

7.3 Table or View

The collection metadata component that specifies the name of the table or view to which the collection is mapped.

Property	Value
Default value	None
Allowed values	Valid Oracle quoted identifierFoot 1. If this value contains double quotation marks (") or control characters, SODA replaces them with underscore characters (_).
JSON collection metadata document path	tableName or viewName

See Also:

Oracle Database SQL Language Reference for information about valid Oracle quoted identifiers

7.4 Key Column Name

The collection metadata component that specifies the name of the column that stores the document key.

Property	Value
Default value	ID
Allowed values	Valid Oracle quoted identifierFoot 1 (as defined in Oracle Database SQL Language Reference). If this value contains double quotation marks (") or control characters, SODA replaces them with underscore characters (_).
JSON collection metadata document path	keyColumn.name

7.5 Key Column Type

The collection metadata component that specifies the SQL data type of the column that stores the document key.

Property	Value
Default value	VARCHAR2
Allowed values	VARCHAR2 NUMBER RAW(16)
JSON collection metadata document path	keyColumn.sqlType

Caution:

If client-assigned keys are used and the key column type is VARCHAR2 then Oracle recommends that the database character set be AL32UTF8. This ensures that conversion of the keys to the database character set is lossless.

Otherwise, if client-assigned keys contain characters that are not supported in your database character set then conversion of the key into the database character set during a read or write operation is lossy. This can lead to duplicate-key errors during insert operations. More generally, it can lead to unpredictable results. For example, a read operation could return a value that is associated with a different key from the one you expect.

7.6 Key Column Max Length

The collection metadata component that specifies the maximum length of the key column in bytes. This component applies only to keys of type VARCHAR2.

Property	Value
Default value	255
Allowed values	At least 32 bytes if key assignment method is UUID or GUID. See Key Column Assignment Method.
JSON collection metadata document path	keyColumn.maxLength

7.7 Key Column Assignment Method

The collection metadata component that specifies the method used to assign keys to objects that are inserted into the collection.

Property	Value
Default value	UUID
Allowed values	UUID GUID SEQUENCE CLIENT EMBEDDED_OID For descriptions of these methods, see Table 7-1.
JSON collection metadata document path	keyColumn.assignmentMethod

Table 7-1 Key Assignment Methods

Method	Description
UUID (default — but not used by Oracle Database API for MongoDB)	Keys are generated by SODA, based on the UUID (a Universal Unique IDentifier).
GUID	Keys are generated in Oracle Database by SQL function SYS_GUID, described in Oracle Database SQL Language Reference.
SEQUENCE	Keys are generated in Oracle Database by a database sequence. If you specify the key assignment method as SEQUENCE then you must also specify the name of that sequence — see Key Column Sequence Name.
CLIENT	Keys are assigned by the client application. Caution: If client-assigned keys are used and the key column type is VARCHAR2 then Oracle recommends that the database character set be AL32UTF8. This ensures that conversion of the keys to the database character set is lossless. Otherwise, if client-assigned keys contain characters that are not supported in your database character set then conversion of the key into the database character set during a read or write operation is lossy. This can lead to duplicate-key errors during insert operations. More generally, it can lead to unpredictable results. For example, a read operation could return a value that is associated with a different key from the one you expect.
EMBEDDED_OID (used always by Oracle Database API for MongoDB)	This method is used only, and automatically, with Oracle Database API for MongoDB, which is available only for an Oracle Autonomous Database. (You cannot create a collection that uses this method with any of the SODA implementations/languages.) The column value is a string (data type VARCHAR2). The value is also embedded in the documents of the collection, as the value of top-level field _id. If a document already has a top-level field _id, then a string representation of that field value is used for the key column. If a document has no such field, then a field _id with a generated value is embedded in the document at top level, and that value is used for the key column. The value is a string hexadecimal representation of a 12-byte binary OID (ObjectID).

7.8 Key Column Path

The collection metadata component that specifies the top-level document path to the field used as an embedded document key.

The component value is top-level path _id, the embedded key for documents in a collection used by Oracle Database API for MongoDB.

This metadata component is required for key-column assignment method EMBEDDED_OID, and it is used only with that assignment method. It is provided automatically when EMBEDDED_OID is used; you need not specify it explicitly.

(If you do specify this component then an error is raised if keyColumn.path is specified for an assignment method other than EMBEDDED_OID, or if the value specified is not _id.)

Property	Value
Default value	_id
Allowed values	_id
JSON collection metadata document path	keyColumn.path

7.9 Key Column Sequence Name

The collection metadata component that specifies the name of the database sequence that generates keys for documents that are inserted into a collection if the key assignment method is SEQUENCE.

If you specify the key assignment method as SEQUENCE then you must also specify the name of that sequence. If the specified sequence does not exist then SODA creates it.

Property	Value
Default value	None
Allowed values	Valid Oracle quoted identifierFoot 1 (as defined in Oracle Database SQL Language Reference). If this value contains double quotation marks (") or control characters, SODA replaces them with underscore characters (_).
JSON collection metadata document path	keyColumn.sequenceName

Note:

If you drop a collection using SODA, the sequence used for key generation is not dropped. This is because it might not have been created using SODA. To drop the sequence, use SQL command DROP SEQUENCE, after first dropping the collection.

See Also:

• Oracle Database SQL Language Reference for information about DROP SEQUENCE

• Oracle Database Concepts for information about database sequences

7.10 Content Column Name

The collection metadata component that specifies the name of the column that stores the database content.

Property	Value
Default value	JSON_DOCUMENT
Allowed values	Valid Oracle quoted identifierFoot 1 (as defined in Oracle Database SQL Language Reference). If this value contains double quotation marks (") or control characters, SODA replaces them with underscore characters (_).
JSON collection metadata document path	contentColumn.name

7.11 Content Column Type

The collection metadata component that specifies the SQL data type of the column that stores the document content.

Property	Value
Default value	JSON, if database initialization parameter compatible is at least 20 and component mediaTypeColumn.name is not specified (the content is homoogeneous — JSON data only). BLOB, otherwise. If the content type is BLOB then the format is native JSON binary (OSON) if the database you use is an Oracle Autonomous Database (Autonomous JSON Database, Autonomous Transaction Processing or Autonomous Data Warehouse); otherwise, it is textual (unparsed Unicode character data.). If the database you use is an Autonomous JSON Database (AJD) then component mediaTypeColumn.name cannot be specified — the content must be JSON data (homogeneous).
Allowed values	JSON (only if database initialization parameter compatible is at least 20) VARCHAR2 BLOB CLOB
JSON collection metadata document path	contentColumn.sqlType

Note:

You need certain versions of SODA drivers and related software to support collection content type that uses Oracle's native JSON binary format, OSON, that is, for JSON type or for BLOB type with format OSON. See SODA Drivers

.

7.12 Content Column Format

The collection metadata component that specifies the format of the column that stores the document content.

The value of this metadata component is automatically OSON — you cannot change it. This component is available only when the database is an Oracle Autonomous Database: Autonomous JSON Database (AJD), Autonomous Transaction Processing (ATP), or Autonomous Data Warehouse (ADW), and only when the value of metadata component content type (field contentColumn.sqlType) is BLOB.

Property	Value
Allowed value	"OSON"
JSON collection metadata document path	contentColumn.jsonFormat

Note:

You need certain versions of SODA drivers and related software to support collection content type that uses Oracle's native JSON binary format, OSON, that is, for JSON type or for BLOB type with format OSON. See SODA Drivers

.

7.13 Content Column Max Length

The collection metadata component that specifies the maximum length of the content column in bytes. This component applies only to content of type VARCHAR2.

Property	Value
Default value	4000
Allowed values	32767 if extended data types are enabled. Otherwise, 4000 if content column type is VARCHAR2.
JSON collection metadata document path	contentColumn.maxLength

See Also:

Oracle Database SQL Language Reference for information about extended data types

7.14 Content Column JSON Validation

The collection metadata component that specifies the syntax to which JavaScript Object Notation (JSON) content must conform—strict or lax.

Note:

If the content column stores JSON data using Oracle's native binary format OSON — either JSON type or BLOB with format OSON, then this metadata component is absent.

Property	Value
Default value	STANDARD
Allowed values	STANDARD STRICT LAX (default for SQL condition is json)
JSON collection metadata document path	contentColumn.validation

• STANDARD validates according to the JSON RFC 8259 standard (or the RFC 4627 standard, if database initialization parameter compatible is less than 20). It corresponds to the strict syntax defined for Oracle SQL condition is json.^Foot 2

• STRICT is the same as STANDARD, except that it also verifies that the document does not contain duplicate JSON field names. (It corresponds to the strict syntax defined for Oracle SQL condition is json when the SQL keywords WITH UNIQUE KEYS are also used.)

• LAX validates more loosely. (It corresponds to the lax syntax defined for Oracle SQL condition is json.) Some of the relaxations that LAX allows include the following:

  • It does not require JSON field names to be enclosed in double quotation marks (").

  • It allows uppercase, lowercase, and mixed case versions of true, false, and null.

  • Numerals can be represented in additional ways.

See Also:

• Oracle Database JSON Developer’s Guide for information about strict and lax JSON syntax

• IETF RFC 8259 for the JSON RFC 8259 standard

• IETF RFC 4627 for the JSON RFC 4627 standard

7.15 Content Column SecureFiles LOB Compression

The collection metadata component that specifies the SecureFiles LOB compression setting.

Note:

If the content column stores JSON data using Oracle's native binary format OSON — either JSON type or BLOB with format OSON, then this metadata component is absent.

Property	Value
Default value	NONE
Allowed values	NONE HIGH MEDIUM LOW
JSON collection metadata document path	contentColumn.compress

See Also:

Oracle Database SecureFiles and Large Objects Developer's Guide for information about SecureFiles LOB storage

7.16 Content Column SecureFiles LOB Cache

The collection metadata component that specifies the SecureFiles LOB cache setting.

Note:

If the content column stores JSON data using Oracle's native binary format OSON — either JSON type or BLOB with format OSON, then this metadata component is absent.

Property	Value
Default value	TRUE
Allowed values	TRUE FALSE
JSON collection metadata document path	contentColumn.cache

See Also:

Oracle Database SecureFiles and Large Objects Developer's Guide for information about SecureFiles LOB storage

7.17 Content Column SecureFiles LOB Encryption

The collection metadata component that specifies the SecureFiles LOB encryption setting.

Note:

If the content column stores JSON data using Oracle's native binary format OSON — either JSON type or BLOB with format OSON, then this metadata component is absent.

Before you create a collection that uses SecureFiles LOB encryption you must set up an encryption wallet.

 

Property	Value
Default value	NONE
Allowed values	NONE 3DES168 AES128 AES192 AES256
JSON collection metadata document path	contentColumn.encrypt

See Also:

• Oracle Database SecureFiles and Large Objects Developer's Guide for information about SecureFiles LOB storage

• Oracle Database SQL Language Reference for information about how to set up an encryption wallet using the SET ENCRYPTION WALLET clause of the ALTER SYSTEM statement

7.18 Version Column Name

The collection metadata component that specifies the name of the column that stores the document version.

Property	Value
Default value	VERSION
Allowed values	Valid Oracle quoted identifierFoot 1 (as defined in Oracle Database SQL Language Reference). If this value contains double quotation marks (") or control characters, SODA replaces them with underscore characters (_).
JSON collection metadata document path	versionColumn.name

7.19 Version Column Generation Method

The collection metadata component that specifies the method used to compute version values for objects when they are inserted into a collection or replaced.

Property	Value
Default value	UUID, if either (1) database initialization parameter compatible is at least 20 or (2) your database is an Oracle Autonomous Database: Autonomous JSON Database (AJD), Autonomous Transaction Processing (ATP), or Autonomous Data Warehouse (ADW) SHA256, otherwise
Allowed values	UUID TIMESTAMP MD5 SHA256 SEQUENTIAL NONE
JSON collection metadata document path	versionColumn.method

Table 7-2 describes the version generation methods.

Table 7-2 Version Generation Methods

Method	Description
UUID	Ignoring object content, SODA generates a universally unique identifier (UUID) when the document is inserted and for every replace operation. Efficient, but the version changes even if the original and replacement documents have identical content. Version column type value is VARCHAR2(255).
TIMESTAMP	Ignoring object content, SODA generates a value from the time stamp and coverts it to LONG. This method might require a round trip to the database instance to get the time stamp. As with UUID, the version changes even if the original and replacement documents have identical content. Version column type value is NUMBER.
MD5	SODA uses the MD5 algorithm to compute a hash value of the document content. This method is less efficient than UUID, but the version changes only if the document content changes. Version column type value is VARCHAR2(255).
SHA256	SODA uses the SHA256 algorithm to compute a hash value of the document content. This method is less efficient than UUID, but the version changes only if the document content changes. Version column type value is VARCHAR2(255).
SEQUENTIAL	Ignoring object content, SODA assigns version 1 when the object is inserted and increments the version value every time the object is replaced. Version values are easily understood by human users, but the version changes even if the original and replacement documents have identical content. Version column type value is NUMBER.
NONE	If the version column is present, NONE means that the version is generated outside SODA (for example, by a database trigger).

7.20 Last-Modified Time Stamp Column Name

The collection metadata component that specifies the name of the column that stores the last-modified time stamp of the document.

Property	Value
Default value	LAST_MODIFIED
Allowed values	Valid Oracle quoted identifierFoot 1 (as defined in Oracle Database SQL Language Reference). If this value contains double quotation marks (") or control characters, SODA replaces them with underscore characters (_).
JSON collection metadata document path	lastModifiedColumn.name

7.21 Last-Modified Column Index Name

The collection metadata component that specifies the name of the index on the last-modified column.

The value of this component is the name of a nonunique index on the last-modified time-stamp column. The index is created if a name is specified. This index can improve the performance of read and write operations that are driven by last-modified time stamps.

Only SODA for REST provides such an operation (operation GET collection with time-stamp parameters since and until). Other implementations do not use this component, since they do not provide any read or write operations that are driven by last-modified time stamps. Even for SODA for REST, it is typically better not to set this component if you are sure that your application does not use any read or write operations that are driven by time stamps, because creating and maintaining an index carries a cost.

Property	Value
Default value	None
Allowed values	Valid Oracle quoted identifierFoot 1 (as defined in Oracle Database SQL Language Reference). If this value contains double quotation marks (") or control characters, SODA replaces them with underscore characters (_).
JSON collection metadata document path	lastModifiedColumn.index

See Also:

Oracle REST Data Services SODA for REST Developer's Guide

7.22 Creation Time Stamp Column Name

The collection metadata component that specifies the name of the column that stores the creation time stamp of the document. This time stamp is generated during the insert, insertAndGet, save, or saveAndGet operation.

Property	Value
Default value	CREATED_ON
Allowed values	Valid Oracle quoted identifierFoot 1 (as defined in Oracle Database SQL Language Reference). If this value contains double quotation marks (") or control characters, SODA replaces them with underscore characters (_).
JSON collection metadata document path	creationTimeColumn.name

7.23 Media Type Column Name

The collection metadata component that specifies the name of the column that stores the media type of the document. A media type column is needed if the collection is to be heterogeneous, that is, it can store documents other than JavaScript Object Notation (JSON).

Note:

You cannot use query-by-example (QBE) with a heterogeneous collection. An error is raised if you try to do so.

Property	Value
Default value	None
Allowed values	Valid Oracle quoted identifierFoot 1 (as defined in Oracle Database SQL Language Reference). If this value contains double quotation marks (") or control characters then SODA replaces them with underscore characters (_).
JSON collection metadata document path	mediaTypeColumn.name

7.24 Read Only

The collection metadata component that specifies whether the collection is read-only.

Property	Value
Default value	FALSE
Allowed values	TRUE FALSE
JSON collection metadata document path	readOnly

---

Footnote Legend

Footnote 1:

Reminder: letter case is significant for a quoted SQL identifier; it is interpreted case-sensitively.

Footnote 2:

In database releases prior to 20c only IETF RFC 4627 was supported. It allows only a JSON object or array, not a scalar, at the top level of a JSON document. RFC 8259 support includes RFC 4627 support (and RFC 7159 support).