Oracle DBMS_SODA
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Purpose This 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.
AUTHID CURRENT_USER
Constants
Name Data Type Value
 Content Types
DOC_VARCHAR2 PLS_INTEGER 1
DOC_BLOB PLS_INTEGER 2
DOC_CLOB PLS_INTEGER 3
DOC_JSON PLS_INTEGER 4
 Collection Create Modes
CREATE_MODE_DDL PLS_INTEGER 1
CREATE_MODE_MAP PLS_INTEGER 2
 Data Guide Formats
DATAGUIDE_FORMAT_HIERARCHICAL PLS_INTEGER 1
DATAGUIDE_FORMAT_FLAT PLS_INTEGER 2
 Data guide Flags
DATAGUIDE_PRETTY PLS_INTEGER 1
DATAGUIDE_GEOJSON PLS_INTEGER 2
DATAGUIDE_GATHER_STATS PLS_INTEGER 4
 MV Refresh Constants
MV_REFRESH_ON_STATEMENT PLS_INTEGER 1
MV_REFRESH_ON_COMMIT PLS_INTEGER 2
MV_REFRESH_ON_DEMAND PLS_INTEGER 3
Data Types CREATE OR REPLACE TYPE SODA_Collection_T FORCE
OID '00000000000000000000000000020017'
authid current_user
AS OPAQUE VARYING (*)
USING library DBMS_SODACOLL_LIB
(...;
/

CREATE OR REPLACE TYPE SYS.SODA_CollName_List_T FORCE
AS TABLE OF NVARCHAR2(255);
/

CREATE OR REPLACE TYPE SODA_Document_T FORCE
OID '00000000000000000000000000020018'
authid current_user
AS OPAQUE VARYING (*)
USING library DBMS_SODADOC_LIB
(...;
/

CREATE OR REPLACE TYPE SODA_Operation_T FORCE
OID '00000000000000000000000000020019'
authid current_user
AS OPAQUE VARYING (*)
USING library DBMS_SODAOPR_LIB
(...;
/
Dependencies
DBMS_SODA_LIB PLITBLM SODA_COLLNAME_LIST_T
JSON$USER_COLLECTION_METADATA SODA_COLLECTION_T  
Documented Yes
First Available 18.1
Security Model Owned by SYS with EXECUTE granted to PUBLIC

The Library's recommendation is that you revoke EXECUTE from PUBLIC. Everybody with a valid userID does not need to be able to drop collections.
Source {ORACLE_HOME}/rdbms/admin/dbmssodapls.sql
Subprograms
 
ABORT_REDEF_COLLECTION (new 21c)
Undocumented dbms_soda.abort_redef_collection(
original_collection_name IN NVARCHAR2,
interim_collection_name  IN NVARCHAR2);
TBD
 
CAN_REDEF_COLLECTION (new 21c)
Undocumented dbms_soda.can_redef_collection(original_collection_name IN NVARCHAR2);
TBD
 
COPY_COLLECTION_DEPENDENTS (new 21c)
Undocumented dbms_soda.copy_collection_dependents(
original_collection_name IN  NVARCHAR2,
interim_collection_name  IN  NVARCHAR2,
ignore_errors            IN  BOOLEAN,
num_errors               OUT BINARY_INTEGER);
TBD
 
CREATE_COLLECTION
Creates a collection using the collection name and metadata 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;
DECLARE
 retVal soda_collection_t;
BEGIN
  dbms_soda.create_collection('UWCOLLECTION');
END;
/
 
CREATE_INTERIM_COLLECTION (new 21c)
Undocumented dbms_soda.create_interim_collection(
original_collection_name IN NVARCHAR2,
interim_collection_name  IN NVARCHAR2,
interim_metadata         IN VARCHAR2);
TBD
 
DROP_COLLECTION
Drops a collection dbms_soda.drop_collection(collection_name IN NVARCHAR2) RETURN NUMBER;
DECLARE
 retVal NUMBER;
BEGIN
  retVal := dbms_soda.drop_collection('UWCOLLECTION');
  dbms_output.put_line(TO_CHAR(retVal);
END;
/
 
FINISH_REDEF_COLLECTION (new 21c)
Undocumented dbms_soda.finish_redef_collection(
original_collection_name IN NVARCHAR2,
interim_collection_name  IN NVARCHAR2,
dml_lock_timeout         IN BINARY_INTEGER,
omit_constraint_check    IN BOOLEAN);
TBD
 
GET_AS_OF_SCN (new 21c)
Undocumented dbms_soda.get_as_of_scn RETURN NUMBER;
TBD
 
GET_AS_OF_TIMESTAMP (new 21c)
Undocumented dbms_soda.get_as_of_timestamp RETURN VARCHAR2;
TBD
 
GET_DEFAULT_METADATA_AS_CLOB (new 21c)
Undocumented dbms_soda.get_default_metadata_as_clob RETURN CLOB;
TBD
 
GET_DEFAULT_METADATA_AS_VARCHAR2 (new 21c)
Undocumented dbms_soda.get_default_metadata_as_varchar2 RETURN VARCHAR2;
TBD
 
LIST_COLLECTION_NAMES
Lists the collection names in the user's schema dbms_soda.list_collection_names RETURN soda_collName_list_t;
DECLARE
 retVal soda_collName_list_t;
BEGIN
  retVal := dbms_soda.list_collection_names;
END;
/
 
OPEN_COLLECTION
Opens a collection dbms_soda.open_collection(collection_name IN NVARCHAR2) RETURN soda_collection_t;
DECLARE
 retVal soda_collection_t;
BEGIN
  retVal := dbms_soda.open_collection;('UWCOLLECTION');
END;
/
 
START_REDEF_COLLECTION (new 21c)
Undocumented dbms_soda.start_redef_collection(
original_collection_name IN NVARCHAR2,
interim_collection_name  IN NVARCHAR2,
copy_vpd_opt             IN BINARY_INTEGER,
refresh_dep_mviews       IN VARCHAR2);
TBD
 
SYNC_INTERIM_COLLECTION (new 21c)
Undocumented dbms_soda.sync_interim_collection(
original_collection_name IN NVARCHAR2,
interim_collection_name  IN NVARCHAR2,
omit_constraint_check    IN BOOLEAN);
TBD
 
TO_UUID (new 21c)
Undocumented dbms_soda.to_uuid(placeholder IN VARCHAR2) RETURN VARCHAR2;
SELECT dbms_soda.to_uuid('ZZYZX')
FROM dual;

DBMS_SODA.TO_UUID('ZZYZX')
---------------------------------
C0802E4518F72AC6E0531000000A7E6A

Related Topics
Built-in Functions
Built-in Packages
DBMS_SODA_ADMIN
DBMS_SODA_DML
DBMS_SODA_DOM
DBMS_SODA_UTIL
SODA for PL/SQL
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx