1. Introduction to Simple Oracle Document Access (SODA)
  2. SODA Collection Metadata Components (Reference)

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.

  • 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 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 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 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 identifierFootref 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.2 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 identifierFootref 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.3 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 identifierFootref 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.4 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.5 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

Related Topics

7.6 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

For descriptions of these methods, see Table 7-1.

JSON collection metadata document path

keyColumn.assignmentMethod

Table 7-1 Key Assignment Methods

Method Description

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.

UUID (default)

Keys are generated by SODA, based on the UUID.

7.7 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 identifierFootref 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.

Related Topics

See Also:

7.8 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 identifierFootref 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.9 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

BLOB

Allowed values

VARCHAR2

BLOB

CLOB

JSON collection metadata document path

contentColumn.sqlType

7.10 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

Related Topics

See Also:

Oracle Database SQL Language Reference for information about extended data types

7.11 Content Column JSON Validation

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

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 4627 standard. (It corresponds to the strict syntax defined for Oracle SQL condition is json.)

  • 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:

7.12 Content Column SecureFiles LOB Compression

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

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.13 Content Column SecureFiles LOB Cache

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

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.14 Content Column SecureFiles LOB Encryption

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

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

7.15 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 identifierFootref 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.16 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

SHA256

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 (default)

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.17 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 identifierFootref 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.18 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 identifierFootref 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.19 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 identifierFootref 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.20 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 identifierFootref 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.21 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.