DBMS_SODA

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

150.1 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.

150.2 Summary of DBMS_SODA Subprograms

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

Table 150-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.
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.

150.2.1 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 150-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

150.2.2 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)
 RETURN NUMBER;

Parameters

Table 150-3 DROP_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:

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.

150.2.3 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.

150.2.4 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 150-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: