Oracle | DBMS

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	FINISH_REDEF_COLLECTION	LIST_COLLECTION_NAMES
CAN_REDEF_COLLECTION	GET_AS_OF_SCN	OPEN_COLLECTION
COPY_COLLECTION_DEPENDENTS	GET_AS_OF_TIMESTAMP	START_REDEF_COLLECTION
CREATE_COLLECTION	GET_DEFAULT_METADATA_AS_CLOB	SYNC_INTERIM_COLLECTION
CREATE_INTERIM_COLLECTION	GET_DEFAULT_METADATA_AS_VARCHAR2	TO_UUID
DROP_COLLECTION

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