DBMS_SODA

The DBMS_SODA package is a PL/SQL package implementing Simple Oracle Document Access (SODA). SODA allows you to use the Oracle Database as a NoSQL document store. The core abstraction provided by SODA is that of document collections. The DBMS_SODA package allows you to create, list, and delete document collections from PL/SQL, and to perform CRUD (create, replace, update, delete) operations on documents. All DDL functions are encapsulated within this package.

This chapter contains the following topics:

See Also:

DBMS_SODA Security Model

This package is available to users with the SODA_APP role.

All SODA types (packages and types) are SYS types. PUBLIC is granted EXECUTE privilege on the DBMS_SODA described in this chapter.

Summary of DBMS_SODA Subprograms

This table lists the DBMS_SODA subprograms in alphabetical order and briefly describes them.

Table 1 DBMS_SODA Package Subprograms

Subprogram	Purpose
CREATE_COLLECTION Function	Creates a collection using the collection name and metadata.
DROP_COLLECTION Function	Drops an existing collection from the user’s schema. This also removes all the documents in the collection.
GET_AS_OF_SCN Function	This function returns the current database SCN number.
GET_AS_OF_TIMESTAMP Function	This function returns the current database timestamp value.
GET_DEFAULT_METADATA_AS_CLOB Function	This function returns the default metadata.
GET_DEFAULT_METADATA_AS_VARCHAR2 Function	This function returns the default metadata.
LIST_COLLECTION_NAMES Function	Lists the collection names in the user’s schema as a table of `NVARCHAR2`.
OPEN_COLLECTION Function	Opens an existing collection.

CREATE_COLLECTION Function

Creates a collection using the collection name and metadata. Uses the settings specified in the metadata and auto-assigns the ones that are not, and returns the collection object. If the metadata argument is omitted or set to NULL, a collection is created with default metadata. The returned collection is open for read and/or write operations. If a collection already exists, the function just opens and returns the collection object.

Syntax

DBMS_SODA.CREATE_COLLECTION (
     collection_Name      IN NVARCHAR2,
     metadata             IN VARCHAR2 DEFAULT NULL,
     create_Mode          IN PLS_INTEGER DEFAULT CREATE_MODE_DDL)
 RETURN SODA_Collection_T;

Parameters

Table 2 CREATE_COLLECTION Parameters

Parameter Description

Parameter	Description
`collection_Name`	The name of the collection. The value of `collection_Name` is case-sensitive.
`metadata`	The metadata of the collection in `VARCHAR2` format.
create_Mode	Valid values are: `DBMS_SODA.CREATE_MODE_DDL` (default). First attempts to create a new table for the collection. If table exists already, tries to map that existing table to the collection. Minimal checking is performed to ensure the table shape matches supplied collection metadata (if not, then an error is returned). `DBMS_SODA.CREATE_MODE_MAP`. Tries to map an existing table to the collection. Minimal checking is performed to ensure the table shape matches supplied collection metadata (if not, then an error is returned).

collection_Name

The name of the collection.

The value of collection_Name is case-sensitive.

metadata The metadata of the collection in VARCHAR2 format.

create_Mode

Valid values are:

DBMS_SODA.CREATE_MODE_DDL (default). First attempts to create a new table for the collection. If table exists already, tries to map that existing table to the collection. Minimal checking is performed to ensure the table shape matches supplied collection metadata (if not, then an error is returned).
DBMS_SODA.CREATE_MODE_MAP. Tries to map an existing table to the collection. Minimal checking is performed to ensure the table shape matches supplied collection metadata (if not, then an error is returned).

Return Values

The function returns a Soda_Collection_T object representing the collection.

Exceptions

Descriptor Error—if the input descriptor is invalid
Error—if an error occurs while creating the collection

DROP_COLLECTION Function

Drops an existing collection from the user’s schema. This also removes all the documents in the collection.

Syntax

DBMS_SODA.DROP_COLLECTION (
     collection_Name      IN NVARCHAR2,
     purge                IN BOOLEAN DEFAULT FALSE,
     drop_Mapped_Table    IN BOOLEAN DEFAULT FALSE)
 RETURN NUMBER;

Parameters

Table 3 DROP_COLLECTION Parameters

Parameter Description

Parameter	Description
`collection_Name`	The name of the collection. The value of `collection_Name` is case-sensitive.
`purge`	The default value is `FALSE`.
`drop_Mapped_Table`	The default value is `FALSE`.

collection_Name

The name of the collection.

The value of collection_Name is case-sensitive.

purge The default value is FALSE.

drop_Mapped_Table The default value is FALSE.

Return values

This function returns the following values:

1—if the collection was dropped successfully
0—if the collection does not exist

Exceptions

If an error occurs while dropping the collection, for example, due to uncommitted writes to the collection or privilege issues.

GET_AS_OF_SCN Function

This function returns the current database SCN number.

Syntax

DBMS_SODA.GET_AS_OF_SCN ( )
 RETURN NUMBER;

Return values

This function returns the current database SCN number.

GET_AS_OF_TIMESTAMP Function

This function returns the current database timestamp value.

Syntax

DBMS_SODA.GET_AS_OF_TIMESTAMP ( )
 RETURN VARCHAR2;

Return values

This function returns the current database timestamp value.

GET_DEFAULT_METADATA_AS_CLOB Function

This function returns the default metadata.

Syntax

DBMS_SODA.GET_DEFAULT_METADATA_AS_CLOB ( )
 RETURN CLOB;

Return Values

This function returns the default metadata using the CLOB datatype.

GET_DEFAULT_METADATA_AS_VARCHAR2 Function

This function returns the default metadata.

Syntax

DBMS_SODA.GET_DEFAULT_METADATA_AS_VARCHAR2 ( )
 RETURN VARCHAR2;

Return Values

This function returns the default metadata using the VARCHAR2 datatype.

LIST_COLLECTION_NAMES Function

This function returns a list of collection names in the user’s schema as a table of NVARCHAR2.

Syntax

DBMS_SODA.LIST_COLLECTION_NAMES ()
 RETURN SODA_CollName_List_T;

Return Values

This function returns a list of collection names as a table of NVARCHAR2(255). The collection list is empty if there are no collections in the schema.

Exceptions

Error-if an error occurs while listing the collection names.

OPEN_COLLECTION Function

Opens an existing collection for read and/or write operations.

Syntax

DBMS_SODA.OPEN_COLLECTION (
     collection_Name      IN NVARCHAR2)
 RETURN SODA_Collection_T;

Parameters

Table 4 OPEN_COLLECTION Parameters

Parameter Description

Parameter	Description
`collection_Name`	The name of the collection. The value of `collection_Name` is case-sensitive.

collection_Name

The name of the collection.

The value of collection_Name is case-sensitive.

Return Values

This function returns the following values:

a collection object which is open
NULL, if the collection does not exist

Exceptions

Error-if an error occurs while creating the collection

See Also:

Summary of SODA Online Redefinition Subprograms

This table lists the SODA Online Redefinition subprograms in alphabetical order and briefly describes them.

Table 5 SODA Online Redefinition Subprograms

Subprogram	Purpose
ABORT_REDEF_COLLECTION Procedure	This procedure reverts the changes made to a collection if there are any errors in `CREATE_INTERIM_COLLECTION`, `START_REDEF_COLLECTION`, `COPY_COLLECTION_DEPENDENTS`, `SYNC_INTERIM_COLLETION`, or due to any other reasons.
CAN_REDEF_COLLECTION Procedure	This procedure checks if the data table can be redefined. If the data table cannot be redefined, errors are raised.
COPY_COLLECTION_DEPENDENTS Procedure	This procedure copies all the dependents that are defined on the original table to the interim table. However, if the interim table is JSON type, constraints like `IS JSON NOT NULL` will not be copied because those are extraneous.
CREATE_INTERIM_COLLECTION Procedure	This procedure creates an interim `SODA` collection. The interim_metadata specifies the changes the user needs to make. For example, a delta applied as a patch to the original matadata.
FINISH_REDEF_COLLECTION Procedure	This procedure performs the following tasks in one atomic transaction. It introduces a downtime. This can only be called after every dependent required for a `SODA` data table is present on the interim collection, otherwise an error is raised.
START_REDEF_COLLECTION Procedure	This procedure starts the redefinition process. Copies the existing data from the original table to the interim table and performs the required transformations on the redefined columns.
SYNC_INTERIM_COLLETION Procedure	This procedure syncronizes the changes to the interim table if the original data table was changed. This procedure can only be called after every dependent required for the `SODA` data table is present on the interim collection, otherwise, an error is raised.
TO_UUID Function	This function is used to remap the version column of the collection table during Data Pump Import to UUID values. This function is only for use with the `REMAP_DATA` feature of Data Pump.

ABORT_REDEF_COLLECTION Procedure

This procedure reverts the changes made to a collection if there are any errors in CREATE_INTERIM_COLLECTION, START_REDEF_COLLECTION, COPY_COLLECTION_DEPENDENTS, SYNC_INTERIM_COLLETION, or due to any other reasons.

Syntax

DBMS_SODA.ABORT_REDEF_COLLECTION (
   collection_name         IN  NVARCHAR2,
   interim_collection_name IN  NVARCHAR2);

Parameters

Table 6 ABORT_REDEF_COLLECTION Procedure

Parameter	Description
`collection_name`	The name of the collection to be redefined.
`interim_collection_name`	The name used for the interim collection.

CAN_REDEF_COLLECTION Procedure

This procedure checks if the data table can be redefined. If the data table cannot be redefined, errors are raised.

Syntax

DBMS_SODA.CAN_REDEF_COLLECTION (
   collection_name   IN  NVARCHAR2);

Parameters

Table 7 CAN_REDEF_COLLECTION Procedure

Parameter	Description
`collection_name`	The name of the collection to be redefined.

COPY_COLLECTION_DEPENDENTS Procedure

This procedure copies all the dependents that are defined on the original table to the interim table.

Syntax

DBMS_SODA.COPY_COLLECTION_DEPENDENTS (
   collection_name         IN  NVARCHAR,
   interim_collection_name IN  NVARCHAR2,
   ignore_error            IN  BOOLEAN DEFAULT NULL,
   num_errors              OUT PLS_INTEGER);

Parameters

Table 8 COPY_COLLECTION_DEPENDENTS Procedure

Parameter	Description
`collection_name`	The name of the collection to be redefined.
`interim_collection_name`	The name used for the interim collection.
`ignore_error`	Ignore the errors encountered in the process and proceed.
`num_errors`	The number of errors encountered in the process.

CREATE_INTERIM_COLLECTION Procedure

This procedure creates an interim SODA collection. The interim_metadata specifies the changes the user needs to make. For example, a delta applied as a patch to the original matadata.

Syntax

DBMS_SODA.CREATE_INTERIM_COLLECTION (
   collection_name            IN  NVARCHAR2,
   interim_collection_name    IN  NVARCHAR2,
   interim_metadata           IN  VARCHAR2);

Parameters

Table 9 CREATE_INTERIM_COLLECTION Procedure

Parameter	Description
`collection_name`	The name of the collection to be redefined.
`interim_collection_name`	The name used for the interim collection.
`interim_metadata`	The metadata snippet specifying the columns that need to be redefined and how they need to be redefined.

Usage Notes

The interim_metadata parameter can contain the following fields. An error is raised if other fields are present and if the fields are not the same as those in the metadata for the original collection.

Set contentColumn.sqlType to JSON in order to redefine the CONTENT column to JSON type, automatically generating the VERSION column if needed.
Set versionColumn.method to UUID to redefine the VERSION column to use UUID.
Set tableName to specify a name for the data table of this interim collection. If this field is not present, a default data table name will be generated according to the rules of table name defaulting outlined in Default Naming of a Collection Table. However, if the table already exists, the collection is created using the MAP mode. In this scenario, make sure that the mapped table does not have any constraints defined.
The user can combine the above fields in the supplied interim_metadata snippet. For example, the most common use case is to both redefine the content column to JSON type and redefine the version column to UUID, which can be achieved by setting the interim_metadata parameter to {"contentColumn" : {"sqlType": "JSON"}, "versionColumn" : {"method": "UUID"}} .

FINISH_REDEF_COLLECTION Procedure

This procedure performs the following tasks in one atomic transaction. It introduces a downtime. This can only be called after every dependent required for a SODA data table is present on the interim collection, otherwise an error is raised.

Syntax

DBMS_SODA.FINISH_REDEF_COLLECTION (
   collection_name         IN  NVARCHAR,
   interim_collection_name IN  NVARCHAR2,
   dml_lock_timeout        IN  PLS_INTEGER DEFAULT NULL);

Parameters

Table 10 FINISH_REDEF_COLLECTION Procedure

Parameter	Description
`collection_name`	The name of the collection to be redefined.
`interim_collection_name`	The name used for the interim collection.
`dml_lock_timeout`	The wait period in seconds. If the lock is not acquired within this wait period, an error occurs.

Usage Notes

This action cannot be undone. After this procedure is executed, you cannot perform ROLLBACK procedure provided by the DBMS_REDEFINITION package.

Caution:

Before calling FINISH_REDEF_COLLECTION Procedure, the interim collection should be tried out to make sure it can be accessed and is working as expected. In case of any issues, use ABORT_REDEF_COLLECTION Procedure.

It is very important to do this before calling FINISH_REDEF_COLLECTION because the latter cannot be reversed.

START_REDEF_COLLECTION Procedure

This procedure starts the redefinition process. Copies the existing data from the original table to the interim table and performs the required transformations on the redefined columns.

Syntax

DBMS_SODA.START_REDEF_COLLECTION (
   collection_name         IN  NVARCHAR,
   interim_collection_name IN  NVARCHAR2,
   copy_vpd_opt            IN  DEFAULT NULL,
   refresh_dep_mviews      IN  DEFAULT NULL);

Parameters

Table 11 START_REDEF_COLLECTION Procedure

Parameter	Description
`collection_name`	The name of the collection to be redefined.
`interim_collection_name`	The name used for the interim collection.
`copy_vpd_opt`	Can be either `DBMS_REDEFINITION.CONS_VPD_MANUAL` or `DBMS_REDEFINITION.CONS_VPD_NONE`. `DBMS_REDEFINITION.CONS_VPD_MANUAL` is used to indicate to copy VPD policies manually. `DBMS_REDEFINITION.CONS_VPD_NONE` is used to indicate that there are no VPD policies on the original table. If `NULL`, `DBMS_REDEFINITION.CONS_VPD_NONE` is used.
`refresh_dep_mviews`	Can be `N` or `Y`. When set to `N`, fast refresh of dependent materialized views is performed when the `START_REDEF_TABLE` procedure is run, each time the `SYNC_INTERIM_TABLE` procedure is run, and when the `FINISH_REDEF_TABLE` procedure is run. If `NULL`, `N` is used.

SYNC_INTERIM_COLLETION Procedure

This procedure synchronizes the changes to the interim table if the original data table was changed. This procedure can only be called after every dependent required for the SODA data table is present on the interim collection, otherwise, an error is raised.

Syntax

DBMS_SODA.SYNC_INTERIM_COLLETION (
   collection_name         IN  NVARCHAR,
   interim_collection_name IN  NVARCHAR2);

Parameters

Table 12 SYNC_INTERIM_COLLETION Procedure

Parameter	Description
`collection_name`	The name of the collection to be redefined.
`interim_collection_name`	The name used for the interim collection.

TO_UUID Function

This function is used to remap the version column of the collection table during Data Pump Import to UUID values. This function is only for use with the REMAP_DATA feature of Data Pump.

Syntax

DBMS_SODA.TO_UUID (
     placeholder      IN   NVARCHAR2)
 RETURN VARCHAR2;

Parameters

Table 13 TO_UUID Parameters

Parameter	Description
`placeholder`	This parameter is not used and can be ignored. It is used due to the syntactic constraints for the Data Pump `REMAP_DATA` function.

Return values

This function returns a hexadecimal string that can be used as a UUID value.

See Also: REMAP_DATA in the Oracle® Database Utilities Guide.