148 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:
148.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.
148.2 Summary of DBMS_SODA Subprograms
This table lists the DBMS_SODA subprograms in alphabetical order and briefly describes them.
Table 148-1 DBMS_SODA Package Subprograms
| Subprogram | Purpose |
|---|---|
|
Creates a collection using the collection name and metadata. |
|
|
Drops an existing collection from the user’s schema. This also removes all the documents in the collection. |
|
|
Lists the collection names in the user's schema as a table of |
|
|
Opens an existing collection. |
148.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)
RETURN SODA_Collection_T;Parameters
Table 148-2 CREATE_COLLECTION Parameters
| Parameter | Description |
|---|---|
|
|
The name of the collection. The value of |
|
|
The metadata of the collection in |
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
148.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 148-3 DROP_COLLECTION Parameters
| Parameter | Description |
|---|---|
|
|
The name of the collection. The value of |
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.
148.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.
148.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 148-4 OPEN_COLLECTION Parameters
| Parameter | Description |
|---|---|
|
|
The name of the collection. The value of |
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