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 typeVARCHAR2
. - 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 isSEQUENCE
. - 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 typeVARCHAR2
. - 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 theinsert
,insertAndGet
,save
, orsaveAndGet
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.
Related Topics
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 least20
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 than20
).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
, andvalidation
. -
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
toCLIENT
(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 ( |
JSON collection metadata document path |
|
See Also:
Oracle Database SQL Language Reference for information about valid Oracle quoted identifiers
Parent topic: SODA Collection Metadata Components (Reference)
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 ( |
JSON collection metadata document path |
|
See Also:
Oracle Database SQL Language Reference for information about valid Oracle quoted identifiers
Parent topic: SODA Collection Metadata Components (Reference)
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 |
|
Allowed values |
Valid Oracle quoted identifierFoot 1 (as defined in Oracle Database SQL
Language Reference). If this value contains double quotation marks ( |
JSON collection metadata document path |
|
Parent topic: SODA Collection Metadata Components (Reference)
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 |
|
Allowed values |
|
JSON collection metadata document path |
|
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.
Parent topic: SODA Collection Metadata Components (Reference)
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 |
|
Allowed values |
At least 32 bytes if key assignment method is |
JSON collection metadata document path |
|
Related Topics
Parent topic: SODA Collection Metadata Components (Reference)
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 |
|
Allowed values |
For descriptions of these methods, see Table 7-1. |
JSON collection metadata document path |
|
Table 7-1 Key Assignment Methods
Method | Description |
---|---|
|
Keys are generated by SODA, based on the
|
|
Keys are generated in Oracle Database by SQL function |
|
Keys are generated in Oracle Database by a database sequence. If you specify the key assignment method as |
|
Keys are assigned by the client application. Caution: If client-assigned keys are used and the key column type is
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. |
|
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
|
Parent topic: SODA Collection Metadata Components (Reference)
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 |
|
Allowed values |
|
JSON collection metadata document path |
|
Parent topic: SODA Collection Metadata Components (Reference)
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 ( |
JSON collection metadata document path |
|
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.
Related Topics
See Also:
-
Oracle Database SQL Language Reference for information about
DROP SEQUENCE
-
Oracle Database Concepts for information about database sequences
Parent topic: SODA Collection Metadata Components (Reference)
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 |
|
Allowed values |
Valid Oracle quoted identifierFoot 1 (as defined in Oracle Database SQL
Language Reference). If this value contains double quotation marks ( |
JSON collection metadata document path |
|
Parent topic: SODA Collection Metadata Components (Reference)
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 |
If the content type is If the database you use is an Autonomous JSON Database
(AJD) then component |
Allowed values |
|
JSON collection metadata document path |
|
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
Related Topics
Parent topic: SODA Collection Metadata Components (Reference)
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 |
|
JSON collection metadata document path |
|
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
Related Topics
Parent topic: SODA Collection Metadata Components (Reference)
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 |
JSON collection metadata document path |
|
Related Topics
See Also:
Oracle Database SQL Language Reference for information about extended data types
Parent topic: SODA Collection Metadata Components (Reference)
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 |
|
Allowed values |
|
JSON collection metadata document path |
|
-
STANDARD
validates according to the JSON RFC 8259 standard (or the RFC 4627 standard, if database initialization parametercompatible
is less than20
). It corresponds to the strict syntax defined for Oracle SQL conditionis json
.Foot 2 -
STRICT
is the same asSTANDARD
, 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 conditionis json
when the SQL keywordsWITH UNIQUE KEYS
are also used.) -
LAX
validates more loosely. (It corresponds to the lax syntax defined for Oracle SQL conditionis json
.) Some of the relaxations thatLAX
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
, andnull
. -
Numerals can be represented in additional ways.
-
Related Topics
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
Parent topic: SODA Collection Metadata Components (Reference)
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 |
|
Allowed values |
|
JSON collection metadata document path |
|
Related Topics
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for information about SecureFiles LOB storage
Parent topic: SODA Collection Metadata Components (Reference)
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 |
|
Allowed values |
|
JSON collection metadata document path |
|
Related Topics
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for information about SecureFiles LOB storage
Parent topic: SODA Collection Metadata Components (Reference)
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 |
|
Allowed values |
|
JSON collection metadata document path |
|
Related Topics
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 theALTER SYSTEM
statement
Parent topic: SODA Collection Metadata Components (Reference)
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 |
|
Allowed values |
Valid Oracle quoted identifierFoot 1 (as defined in Oracle Database SQL
Language Reference). If this value contains double quotation marks ( |
JSON collection metadata document path |
|
Parent topic: SODA Collection Metadata Components (Reference)
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 |
|
|
Allowed values |
|
|
JSON collection metadata document path |
|
Table 7-2 describes the version generation methods.
Table 7-2 Version Generation Methods
Method | Description |
---|---|
|
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 |
|
Ignoring object content, SODA generates a value from the time stamp and coverts it to Version column type value is |
|
SODA uses the MD5 algorithm to compute a hash value of the document content. This method is less efficient than Version column type value is |
|
SODA uses the SHA256 algorithm to compute a hash value of the document content. This method is less efficient than Version column type value is |
|
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 |
|
If the version column is present, |
Parent topic: SODA Collection Metadata Components (Reference)
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 |
|
Allowed values |
Valid Oracle quoted identifierFoot 1 (as defined in Oracle Database SQL
Language Reference). If this value contains double quotation marks ( |
JSON collection metadata document path |
|
Parent topic: SODA Collection Metadata Components (Reference)
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 ( |
JSON collection metadata document path |
|
See Also:
Oracle REST Data Services SODA for REST Developer's Guide
Parent topic: SODA Collection Metadata Components (Reference)
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 |
|
Allowed values |
Valid Oracle quoted identifierFoot 1 (as defined in Oracle Database SQL
Language Reference). If this value contains double quotation marks ( |
JSON collection metadata document path |
|
Parent topic: SODA Collection Metadata Components (Reference)
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 ( |
JSON collection metadata document path |
|
Parent topic: SODA Collection Metadata Components (Reference)
7.24 Read Only
The collection metadata component that specifies whether the collection is read-only.
Property | Value |
---|---|
Default value |
|
Allowed values |
|
JSON collection metadata document path |
|
Parent topic: SODA Collection Metadata Components (Reference)
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).