5 Collection Specifications

A collection specification provides information about the Oracle Database table or view underlying the collection object. The table or view is created when you create the collection.

Note:

In collection specifications, you must use strict JSON syntax. That is, you must enclose each nonnumeric value in double quotation marks.

Table 5-1 describes the collection specification fields and their possible values.

Note:

If you omit one of the optional columns (created-on timestamp, last-modified timestamp, version, or media type) from the collection specification then no such column is created. At a minimum, a collection has a key column and a content column.

Table 5-1 Collection Specification Fields

Field	Description	Possible Values
`schemaName`	SQL name of schema that owns table or view underlying collection object.	—
`tableName` or `viewName`	SQL name of table or view underlying collection object.	—
`keyColumn.name`	Name of key column.	Default: `ID`
`keyColumn.sqlType`	SQL data type of key column.	`VARCHAR2` (default), `NUMBER`, `RAW`
`keyColumn.maxLength`	Maximum length of key column, if not of `NUMBER` data type.	Default: 255
`keyColumn.assignmentMethod`	Key assignment method.	`SEQUENCE`, `GUID`, `UUID` (default), or `CLIENT`
`keyColumn.sequenceName`	If `keyColumn.assignmentMethod` is `SEQUENCE`, then this field must specify the name of a database sequence.	Name of existing database sequence
`contentColumn.name`	Name of content column.	Default: `JSON_DOCUMENT`
`contentColumn.sqlType`	SQL data type of content column.	`VARCHAR2`, `BLOB` (default), `CLOB`
`contentColumn.maxLength`	Maximum length of content column, if not of LOB data type.	The default length is 4000 bytes. If `MAX_STRING_SIZE` = `STANDARD` then `maxLength` can be at most 4000 (bytes). If `MAX_STRING_SIZE` = `EXTENDED`, then `maxLength` can be at most 32767 (bytes).
`contentColumn.validation`	Validation level of content column. Corresponds to SQL condition `is` `json`, which determines the syntax to which JSON content must conform. `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 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.	`STANDARD` (default), `STRICT`, `LAX`
`contentColumn.compress`	Compression level for SecureFiles stored in content column.	`NONE` (default), `HIGH`, `MEDIUM`, `LOW`
`contentColumn.cache`	Caching of SecureFiles stored in content column.	`TRUE`, `FALSE` (default)
`contentColumn.encrypt`	Encryption algorithm for SecureFiles stored in content column.^Foot 1	`NONE` (default), `3DES168`, `AES128`, `AES192`, `AES256`
`creationTimeColumn.name`	Name of optional created-on timestamp column. This column has SQL data type `TIMESTAMP` and default value `SYSTIMESTAMP`.	Default: `CREATED_ON`
`lastModifiedColumn.name`	Name of optional last-modified timestamp column. This column has SQL data type `TIMESTAMP` and default value `SYSTIMESTAMP`.	Default: `LAST_MODIFIED`
`lastModifiedColumn.index`	Name of nonunique index on timestamp column. The index is created if a name is specified.
`versionColumn.name`	Name of optional version (ETag) column. This column has SQL data type `VARCHAR2(255)` unless the method is `SEQUENTIAL` or `TIMESTAMP`, in which case it has data type `NUMBER`. Note: If the method is `TIMESTAMP` then the version is stored as an integer representation of the date and time with microsecond precision. It does not store a date/time string or a SQL date/time type.	Default: `VERSION`
`versionColumn.method`	Versioning method.	`SEQUENTIAL`, `TIMESTAMP`, `UUID`, `SHA256` (default), `MD5`, `NONE`
`mediaTypeColumn.name`	Name of optional object media type column. This column has SQL data type `VARCHAR2(255)`.
`readOnly`	Read/write policy: `TRUE` means read-only.	`TRUE`, `FALSE` (default)

^Footnote 1

Set up Encryption Wallet before creating a collection with SecureFile encryption. For information about the SET ENCRYPTION WALLET clause of the ALTER SYSTEM statement, see Oracle Database SQL Language Reference.

Example 5-1 is a collection specification for an object whose underlying table is HR.EMPLOYEES.

Example 5-1 Collection Specification

{
  "schemaName"          : "HR",
  "tableName"           : "EMPLOYEES",
  "contentColumn"       :
  {
    "name"              : "EMP_DOC",
    "sqlType"           : "VARCHAR2",
    "maxLength"         : 4000,
    "validation"        : "STRICT",
    "compress"          : "HIGH",
    "cache"             : true,
    "encrypt"           : "AES128",
  },
  "keyColumn"           :
  {
    "name"              : "EMP_ID",
    "sqlType"           : "NUMBER",
    "assignmentMethod"  : "SEQUENCE",
    "sequenceName"      : "EMPLOYEE_ID_SEQ"
  },
  "creationTimeColumn"  :
  {
    "name"              : "CREATED_ON"
  },
  "lastModifiedColumn"  :
  {
    "name"              : "LAST_UPDATED",
    "index"             : "empLastModIndexName"
  },
  "versionColumn"       :
  {
    "name"              : "VERSION_NUM",
    "method"            : "SEQUENTIAL"
  },
  "mediaTypeColumn"     :
  {
    "name"              : "CONTENT_TYPE"
  },
  "readOnly"            : true
}

Topics

5.1 Key Assignment Method

The key assignment method determines how keys are assigned to objects that are inserted into a collection.

Table 5-2 Key Assignment Methods

Method	Description
`SEQUENCE`	Keys are integers generated by a database sequence. You must specify the name of the sequence in the `keyColumn.sequenceName` field.
`GUID`	Keys are generated by the SQL function `SYS_GUID()`, which returns a globally unique `RAW` value (16 bytes). If necessary, the `RAW` value is converted to the SQL data type specified by `keyColumn.sqlType`.
`UUID`	Keys are generated by the built-in `UUID` capability of the Java Virtual Machine (JVM) on which the REST server is running, which returns a universally unique `RAW` value. If necessary, the `RAW` value is converted to the SQL data type specified by `keyColumn.sqlType`.
`CLIENT`	Keys are assigned by the client application (not recommended).

Oracle REST standards strongly recommend using server-assigned keys; that is, avoiding the key assignment method CLIENT. If you need simple numeric keys, Oracle recommends SEQUENCE. If any unique identifier is sufficient, Oracle recommends UUID.

If the key assignment method is SEQUENCE, GUID, or UUID, you insert a object into the collection with the operation POST object. The REST server always interprets POST as an insert operation, assigning a key and returning the key in the response body.

If the key assignment method is CLIENT, you cannot use POST to a insert a object in the collection, because the URL path does not include the necessary key. Instead, you must insert the object into the collection using PUT object. If the object is not already in the collection, then the REST server interprets PUT as an insert operation. If the object is already in the collection, then the REST server interprets PUT as a replace operation. PUT is effectively equivalent to the SQL statement MERGE.

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.

5.2 Versioning Method

The versioning method determines how the REST server computes version values for objects when they are inserted into a collection or replaced.

Table 5-3 Versioning Methods

Method	Description
`MD5`	The REST server computes an MD5 checksum on the bytes of object content. For bytes with character data types (such as `VARCHAR2` and `CLOB`), the computation uses UTF-8 encoding. For bytes with data type `BLOB`, the computation uses the encoding used to transmit the POST body, which can be either UTF-8 or UTF-16. For a bulk insert, the request body is parsed as an array of objects and the bytes of the individual objects are re-serialized with UTF-8 encoding, regardless of the encoding chosen for storage. In all cases, the checksum is computed on the bytes as they would be returned by a `GET` operation for the object.
`SHA256` (default)	The REST server computes a SHA256 checksum on the bytes of object content. For bytes with character data types (such as `VARCHAR2` and `CLOB`), the computation uses UTF-8 encoding. For bytes with data type `BLOB`, the computation uses the encoding used to transmit the POST body, which can be either UTF-8 or UTF-16. For a bulk insert, the request body is parsed as an array of objects and the bytes of the individual objects are re-serialized with UTF-8 encoding, regardless of the encoding chosen for storage. In all cases, the checksum is computed on the bytes as they would be returned by a `GET` operation for the specific object.
`UUID`	Ignoring object content, the REST server generates a universally unique identifier (UUID)—a 32-character hexadecimal value—when the object is inserted and for every replace operation (even if the replace operation does not change the object content).
`TIMESTAMP`	Ignoring object content, the REST server generates an integer value, derived from the value returned by the SQL `SYSTIMESTAMP` function. The integer value changes at the level of accuracy of the system clock (typically microseconds or milliseconds).
`SEQUENTIAL`	Ignoring object content, the REST server assigns version 1 when the object is inserted and increments the version value every time the object is replaced.
`NONE`	The REST server does not assign version values during insert and replace operations. During GET operations, any non-null value stored in the version column is used as an ETag. Your application is responsible for populating the version column (using, for example, a PL/SQL trigger or asynchronous program).

MD5 and SHA256 compute checksum values that change when the content itself changes, providing a very accurate way to invalidate client caches. However, they are costly, because the REST server must perform a byte-by-byte computation over the objects as they are inserted or replaced.

UUID is most efficient for input operations, because the REST server does not have to examine every byte of input or wait for SQL to return function values. However, replacement operations invalidate cached copies even if they do not change object content.

TIMESTAMP is useful when you need integer values or must compare two versions to determine which is more recent. As with UUID, replacement operations can invalidate cached copies without changing object content. Because the accuracy of the system clock may be limited, TIMESTAMP is not recommended if objects can change at very high frequency (many times per millisecond).

SEQUENTIAL is also useful when you need integer values or must compare two versions to determine which is more recent. Version values are easily understood by human users, and the version increases despite system clock limitations. However, the increment operation occurs within SQL; therefore, the new version value is not always available to be returned in the REST response body.