Oracle | DBMS

Oracle DBMS_SODA Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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