Oracle | DBMS

Oracle DBMS_SODA Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai is now availble. If you haven't you downloaded already: Why?

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
Sampling Constnats
SAMPLE_ROW	PLS_INTEGER	1
SAMPLE_BLOCK	PLS_INTEGER	2

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_REDEFINITION	JSON_OBJECT_T
DBMS_SODA_ADMIN	PLITBLM
DBMS_SODA_LIB	SODA_COLLECTION_T
DBMS_SODA_UTIL	SODA_COLLNAME_LIST_T
DBMS_UPG_JSON_INT	SODA_DOCUMENT_T
JSON$USER_COLLECTION_METADATA	SODA_OPERATION_T
JSON_ELEMENT_T

Documented Yes

First Available 18c

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

ABORT_REDEF_COLLECTION

Abort Online COLLECTION redefinition dbms_soda.abort_redef_collection( original_collection_name IN NVARCHAR2, interim_collection_name IN NVARCHAR2);

TBD

CAN_REDEF_COLLECTION

Online COLLECTION redefinition dbms_soda.can_redef_collection(original_collection_name IN NVARCHAR2);

TBD

CLONE_COLLECTION (new 23ai)

Undocumented dbms_soda.clone_collection( source_collection_name IN NVARCHAR2, target_collection_name IN NVARCHAR2, target_table_name IN VARCHAR2 DEFAULT NULL);

TBD

COPY_COLLECTION_DEPENDENTS (new 23ai parameter default)

Copy dependent objects for Online COLLECTION redefinition dbms_soda.copy_collection_dependents( original_collection_name IN NVARCHAR2, interim_collection_name IN NVARCHAR2, ignore_errors IN BOOLEAN DEFAULT FALSE, 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_DUALV_COLLECTION (new 23ai)

Undocumented dbms_soda.create_dualv_collection( collection_name IN NVARCHAR2, view_name IN VARCHAR2, view_schema IN VARCHAR2 DEFAULT NULL) RETURN soda_collection_t;

TBD

CREATE_INTERIM_COLLECTION

Create Interim Online COLLECTION redefinition dbms_soda.create_interim_collection( original_collection_name IN NVARCHAR2, interim_collection_name IN NVARCHAR2, interim_metadata IN VARCHAR2);

TBD

DROP_COLLECTION (new 23ai parameters)

Drops a collection dbms_soda.drop_collection( collection_name IN NVARCHAR2, purge IN BOOLEAN DEFAULT FALSE, drop_mapped_table IN BOOLEAN DEFAULT FALSE) RETURN NUMBER;

DECLARE

       retVal NUMBER;

      BEGIN

        retVal := dbms_soda.drop_collection('UWCOLLECTION');

        dbms_output.put_line(TO_CHAR(retVal);

      END;

      /

FINISH_REDEF_COLLECTION (new 23ai parameter defaults)

Undocumented dbms_soda.finish_redef_collection( original_collection_name IN NVARCHAR2, interim_collection_name IN NVARCHAR2, dml_lock_timeout IN BINARY_INTEGER DEFAULT NULL, omit_constraint_check IN BOOLEAN DEFAULT NULL);

TBD

GET_AS_OF_SCN

Returns the current System Change Number (SCN) dbms_soda.get_as_of_scn RETURN NUMBER;

SELECT dbms_soda.get_as_of_scn;

      

      GET_AS_OF_SCN

      -------------

           12245399

GET_AS_OF_TIMESTAMP

Returns the current timestamp dbms_soda.get_as_of_timestamp RETURN VARCHAR2;

SELECT dbms_soda.get_as_of_timestamp;

      

      GET_AS_OF_TIMESTAMP

      ----------------------------

      2024-07-07T15:47:03.994850Z

GET_DEFAULT_METADATA_AS_CLOB

Undocumented dbms_soda.get_default_metadata_as_clob RETURN CLOB;

SELECT dbms_soda.get_default_metadata_as_clob;

      

      GET_DEFAULT_METADATA_AS_CLOB

      ----------------------------------------------------------------------

      {"keyColumn":{"name":"ID","sqlType":"VARCHAR2","maxLength":255,"assignmentMethod

      ":"UUID"},"contentColumn":{"name":"DATA","sqlType":"JSON"},"lastModifiedColumn":

      {"name":"LAST_MODIFIED"},"versionColumn":{"name":"VERSION","method":"UUID"},"cre

      ationTimeColumn":{"name":"CREATED_ON"},"readOnly":false)

GET_DEFAULT_METADATA_AS_VARCHAR2

Undocumented dbms_soda.get_default_metadata_as_varchar2 RETURN VARCHAR2;

SELECT dbms_soda.get_defaykt_metadata_as_varchar2 AS GDMAS;

      

      col GDMAS format a80

      

      GDMAS

      ----------------------------------------------------------------------

      {"keyColumn":{"name":"ID","sqlType":"VARCHAR2","maxLength":255,"assignmentMethod

      ":"UUID"},"contentColumn":{"name":"DATA","sqlType":"JSON"},"lastModifiedColumn":

      {"name":"LAST_MODIFIED"},"versionColumn":{"name":"VERSION","method":"UUID"},"cre

      ationTimeColumn":{"name":"CREATED_ON"},"readOnly":false)

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;

      /

REDEF_OSON_TO_JSON (new 23ai)

Undocumented dbms_soda.redef_oson_to_json(p_collection_name IN NVARCHAR2);

TBD

RENAME_COLUMN (new 23ai)

Undocumented dbms_soda.rename_column( collection_name IN NVARCHAR2, old_column_name IN VARCHAR2, new_column_name IN VARCHAR2);

TBD

START_REDEF_COLLECTION (new 23ai parameter defaults)

Begin COLLECTION redefinition process dbms_soda.start_redef_collection( original_collection_name IN NVARCHAR2, interim_collection_name IN NVARCHAR2, copy_vpd_opt IN BINARY_INTEGER DEFAULT sys.dbms_redefinition.cons_vpd_none, refresh_dep_mviews IN VARCHAR2 DEFAULT 'N');

TBD

SYNC_INTERIM_COLLECTION (new 23ai parameter default)

Undocumented dbms_soda.sync_interim_collection( original_collection_name IN NVARCHAR2, interim_collection_name IN NVARCHAR2, omit_constraint_check IN BOOLEAN DEFAULT NULL);

TBD

TO_UUID

Undocumented dbms_soda.to_uuid(placeholder IN VARCHAR2) RETURN VARCHAR2;

SELECT dbms_soda.to_uuid('ZZYZX');

      

      DBMS_SODA.TO_UUID('ZZYZX')

      ---------------------------------

      C0802E4518F72AC6E0531000000A7E6A