SODA Types

298 SODA Types

This chapter describes the SODA types SODA_DOCUMENT_T and SODA_COLLECTION_T. These are the object types to work with SODA to perform various operations with documents and collections. The SODA types are not persistable.

This chapter contains the following topics:

298.1 SODA Types Overview

There are two SODA types; SODA_DOCUMENT_T and SODA_COLLECTION_T. These SODA types represent the two primary abstractions provided by SODA: documents and collections.

298.2 SODA Types Security Model

The SODA Types are available to users with the SODA_APP role.

All SODA types are SYS types. PUBLIC is granted EXECUTE privilege on the SODA types described in this chapter:

TYPE SODA_Collection_T
TYPE SODA_Document_T

298.3 Summary of SODA Types

This chapter lists the SODA types and describes them.

Table 298-1 SODA Types

Type	Description
`SODA_Collection_T Type`	This `SODA` type represents a `SODA` collection. This type is not persistable.
`SODA_Document_T Type`	This `SODA` type represents a document with content, usually in `JSON` format. This type is not persistable.

298.3.1 SODA_Collection_T Type

This SODA type represents a SODA collection. A reference of SODA collection can only be obtained by either calling DBMS_SODA.CREATE_COLLECTION() or DBMS_SODA.OPEN_COLLECTION().

Table 298-2 SODA_Collection_T Type Subprograms

Subprogram	Description
`FIND_ONE Function`	Fetches the document matching the key.
`GET_METADATA Function`	Returns the metadata of the collection in `JSON` format.
`GET_NAME Function`	Returns the name of the collection.
`INSERT_ONE Function`	Inserts a document into the collection.
`INSERT_ONE_AND_GET Function`	Inserts a document into the collection and returns a result document with all components except for content.
`REMOVE_ONE Function`	Removes the document matching the key.
`REPLACE_ONE Function`	Replaces the content and (optionally) the media type of the document matching the key.
`REPLACE_ONE_AND_GET Function`	Replaces the content and (optionally) the media type of the document matching the key and returns a result document with all components (except content).

298.3.1.1 FIND_ONE Function

This function fetches the document matching the given key.

Syntax

FIND_ONE (
     key         IN VARCHAR2)
 RETURN SODA_Document_T;

Parameters

Table 298-3 FIND_ONE Function Parameters

Parameter	Description
`key`	The key of the document to be fetched.

Return Values

This function returns the document that matches the key. Returns NULL if no match is found.

Exceptions

Error—If an error occurs while finding the document.

298.3.1.2 GET_METADATA Function

This function returns the metadata of the collection in JSON format.

Syntax

GET_METADATA () 
  RETURN VARCHAR2;

Return Values

This function returns the metadata of the collection in JSON format.

298.3.1.3 GET_NAME Function

This function returns the name of the collection.

Syntax

GET_NAME ()
 RETURN NVARCHAR2;

Return Values

This function returns the name of the collection.

298.3.1.4 INSERT_ONE Function

This function inserts a document into the collection.

Syntax

INSERT_ONE (
     document      IN SODA_Document_T)
 RETURN NUMBER;

Parameters

Table 298-4 INSERT_ONE Function Parameters

Parameter	Description
`document`	The input document.

Return Values

The function returns a number–1 if the doc was inserted successfully, 0 otherwise.

Exceptions

Error—If an error occurs while inserting the document into the collection.

298.3.1.5 INSERT_ONE_AND_GET Function

This function inserts a document into the collection.

Syntax

INSERT_ONE_AND_GET (
     document      IN SODA_Document_T)
 RETURN SODA_Document_T;

Parameters

Table 298-5 INSERT_ONE_AND_GET Function Parameters

Parameter	Description
`document`	The input document.

Return Values

The function returns the result document containing all document components supported by the given collection, with the exception of content.

Exceptions

Error—If an error occurs while inserting the document into the collection.

298.3.1.6 REMOVE_ONE Function

This function removes the document matching the given key.

Syntax

REMOVE_ONE (
     key       IN VARCHAR2)
 RETURN NUMBER;

Parameters

Table 298-6 REMOVE_ONE Function Parameters

Parameter	Description
`key`	The key of the document.

Return Values

This function returns the following values:

1–If the document was successfully removed.
0–If the document with the specified key was not found.

Exceptions

Error—If an error occurs while deleting the document from the collection.

298.3.1.7 REPLACE_ONE Function

This function updates the existing document with a new content and (optionally) media type using the key. Any components set in document with the exception of content and media type are not used during the replace. They are ignored.

Syntax

REPLACE_ONE (
     key            IN VARCHAR2,
     document       IN SODA_Document_T)
 RETURN NUMBER;

Parameters

Table 298-7 REPLACE_ONE Parameters

Parameter	Description
`key`	The key of the document.
`document`	The document with the new content and (optionally) media type to replace the old one.

Return Values

This function returns a number—1 if the document was replace, 0 otherwise.

Exceptions

Error—If an error occurs while replacing the document in the collection.

298.3.1.8 REPLACE_ONE_AND_GET Function

Syntax

REPLACE_ONE_AND_GET (
     key            IN VARCHAR2,
     document       IN SODA_Document_T)
 RETURN SODA_Document_T;

Parameters

Table 298-8 REPLACE_ONE_AND_GET Function Parameters

Parameter	Description
`key`	The key of the document.
`document`	The document with the new content and (optionally) media type to replace the old one.

Return Values

The function returns the result document containing all document components supported by the given collection, with the exception of content. Last-modified and version components, if supported by the given collection, will be updated with new values. If no document in the collection had the supplied key, NULL is returned instead of the result document.

Exceptions

Error—If an error occurs while replacing the document in the collection.

298.3.2 SODA_Document_T Type

This SODA type represents a document with content, that is usually in JSON format.

This type is not persistable pl/sql type. However, SODA is a system that basically provides persistence — it has read and write operations. So you do not persist SODA_DOCUMENT_T directly, but you pass it to a write operation (like insert or replace), which is defined on SODA_COLLECTION_T, in order to write the document content and other components to the database.

A document has the following components:

key
content
created-on timestamp
last-modified timestamp
version
media type

Table 298-9 SODA_Document_T Type Subprograms

Subprogram	Description
`GET_BLOB Function`	Fetches the BLOB content of a `BLOB-`based document.
`GET_CLOB Function`	Fetches the `CLOB` content of a `CLOB-`based document.
`GET_CREATED_ON Function`	Fetches the created-on timestamp in `VARCHAR2`.
`GET_DATA_TYPE Function`	Fetches the SQL datatype of the document content with which it was created.
`GET_KEY Function`	Fetches the document key in `VARCHAR2`.
`GET_LAST_MODIFIED Function`	Fetches the last modified timestamp in `VARCHAR2`.
`GET_MEDIA_TYPE Function`	Fetches the media type of the document content in `VARCHAR2`.
`GET_VARCHAR2 Function`	Fetches the `VARCHAR2` content of a `VARCHAR2-based` document.
`GET_VERSION Function`	Fetches the version of the document in `VARCHAR2`.
`SODA_Document_T Function`	There are three different `SODA_DOCUMENT_T` constructor functions. Each constructor function instantiates a document object using key, content, and media type.

298.3.2.1 GET_BLOB Function

This functions fetches the BLOB content of the document. It assumes that the document was constructed with BLOB content, or was returned from a collection with BLOB content. Otherwise, an error is returned.

Syntax

GET_BLOB () 
 RETURN BLOB;

Return Values

This function returns the BLOB content of a document.

Exceptions

SODA Error: If the document was initially not created with BLOB content.

298.3.2.2 GET_CLOB Function

The function fetches CLOB content of the document. It assumes that the document was constructed with CLOB content, or was returned from a collection with CLOB content. Otherwise, an error is returned.

Syntax

GET_CLOB () 
 RETURN CLOB;

Return Values

This function returns the CLOB content of a document.

Exceptions

SODA Error: If the document was initially not created with CLOB content.

298.3.2.3 GET_CREATED_ON Function

This function fetches the created-on timestamp. The timestamp string is in ISO-8601 format, in particular this form: YYYY-MM-DDThh:mm:ss.ssssssZ format. As indicated by the Z at the end, timestamps are returned in UTC (Z indicates zero UTC offset).

Syntax

GET_CREATED_ON () 
 RETURN VARCHAR2;

Return Values

This function returns the created-on timestamp.

298.3.2.4 GET_DATA_TYPE Function

This function fetches the SQL datatype of the document content with which it was created.

Syntax

GET_DATA_TYPE () 
 RETURN PLS_INTEGER;

Return Values

Table 298-10 GET_DATA_TYPE Return Values

Constant	Value	Description
`DOC_VARCHAR2 CONSTANT PLS_INTEGER`	`1`	`VARCHAR2`
`DOC_BLOB CONSTANT PLS_INTEGER`	`2`	`BLOB`
`DOC_CLOB CONSTANT PLS_INTEGER`	`3`	`CLOB`

298.3.2.5 GET_KEY Function

This function fetches the document key.

Syntax

GET_KEY () 
 RETURN VARCHAR2;

Return Values

This function returns the document key.

298.3.2.6 GET_LAST_MODIFIED Function

This function fetches the last modified timestamp. The timestamp string is in ISO-8601 format, in particular this form: YYYY-MM-DDThh:mm:ss.ssssssZ format. As indicated by the Z at the end, timestamps are returned in UTC (Z indicates zero UTC offset).

Syntax

GET_LAST_MODIFIED () 
 RETURN VARCHAR2;

Return Values

This function returns the last modified timestamp.

298.3.2.7 GET_MEDIA_TYPE Function

This function fetches the media type of the document content.

Syntax

GET_MEDIA_TYPE () 
 RETURN VARCHAR2;

Return Values

This function returns the media type of the document content. application/JSON is the media type for JSON documents (default).

298.3.2.8 GET_VARCHAR2 Function

This function fetches the VARCHAR2 content of the document. It assumes that the document was constructed with VARCHAR2 content, or was returned from a collection with VARCHAR2 content. Otherwise, an error is returned.

Syntax

GET_VARCHAR2 () 
 RETURN VARCHAR2;

Return Values

This function returns the VARCHAR2 content of a document.

Exceptions

SODA Error: If the document was initially not created with VARCHAR2 content.

298.3.2.9 GET_VERSION Function

This function fetches the version of the document.

Syntax

GET_VERSION () 
 RETURN VARCHAR2;

Return Values

This function returns the version of the document.

298.3.2.10 SODA_Document_T Function

This function instantiates a document object using key, content, and media type. There are three different SODA_DOCUMENT_T constructor functions. The second parameter (<v|b|c>_Content) is different in each constructor. It is VARCHAR2 in the first variant, BLOB in the second, and CLOB in the third.

Syntax

Key and media type are optional parameters (will be defaulted to NULL). All three parameters can be set to NULL. If media_Type is set to NULL, it will be defaulted to application/json.

SODA_DOCUMENT_T (
     key           IN VARCHAR2 DEFAULT NULL,
     v_Content     IN VARCHAR2,
     media_Type    IN VARCHAR2 DEFAULT NULL) 
 RETURN SODA_Document_T;

SODA_DOCUMENT_T (
     key           IN VARCHAR2 DEFAULT NULL,
     b_Content     IN BLOB,
     media_Type    IN VARCHAR2 DEFAULT NULL) 
 RETURN SODA_Document_T;

SODA_DOCUMENT_T (
     key           IN VARCHAR2 DEFAULT NULL,
     c_Content     IN CLOB,
     media_Type    IN VARCHAR2 DEFAULT NULL) 
 RETURN SODA_Document_T;

Parameters

Table 298-11 SODA_Document_T Parameters

Parameter	Description
`key`	The key of the document.
`v_Content`	The content of the document in `VARCHAR2` format.
`b_Content`	The content of the document in `BLOB` format.
`c_Content`	The content of the document in `CLOB` format.
`media_Type`	The media type of the document. The media type could be `application`/`json` for `JSON` documents.

Note:

v_Content, b_Content, and c_Content are not all parameters of a single SODA_DOCUMENT_T constructor function. Each one corresponds to a particular variant of the constructor function as shown in the Syntax section.

Return Values

This function returns a document of type SODA_Document_T.