157 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:
157.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.
157.2 Summary of DBMS_SODA Subprograms
This table lists the DBMS_SODA
subprograms in alphabetical order and briefly describes them.
Table 157-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. |
|
This function returns the current database SCN number. |
|
This function returns the current database timestamp value. |
|
Lists the collection names in the user's schema as a table of |
|
Opens an existing collection. |
|
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 |
157.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 157-2 CREATE_COLLECTION Parameters
Parameter | Description |
---|---|
|
The name of the collection. The value of |
|
The metadata of the collection in |
create_Mode |
Valid values are:
|
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
157.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, purge IN BOOLEAN DEFAULT FALSE, drop_Mapped_Table IN BOOLEAN DEFAULT FALSE) RETURN NUMBER;
Parameters
Table 157-3 DROP_COLLECTION Parameters
Parameter | Description |
---|---|
|
The name of the collection. The value of |
|
The default value is |
|
The default value is |
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.
157.2.3 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.
157.2.4 GET_AS_OF_TIMESTAMP Function
This function returns the current database timestamp value.
Syntax
DBMS_SODA.GET_AS_OF_TIMESTAMP ( ) RETURN NUMBER;
Return values
This function returns the current database timestamp value.
157.2.5 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.
157.2.6 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 157-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
157.2.7 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 157-5 TO_UUID Parameters
Parameter | Description |
---|---|
|
This parameter is not used and can be ignored. It is used due to
the syntactic constraints for the Data Pump
|
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.