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 will be available on generic Linux platforms in January and soon on AIX and Windows

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