Oracle | DBMS_SODA

Oracle DBMS_SODA_ADMIN 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 Through 19c this package was used to manage collection metadata. In 21c Oracle added functionality related to the DBMS_REDEFINITION package for performing online zero-downtime redefinition of SODA collections.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
General
SQL_RESOURCE_BUS	NUMBER	-54

Data Types TYPE vctab IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER; TYPE nvctab IS TABLE OF NVARCHAR2(32767) INDEX BY BINARY_INTEGER; TYPE ltab IS TABLE OF LONG INDEX BY BINARY_INTEGER; TYPE vcntab IS TABLE OF VARCHAR2(32767); TYPE nvcntab IS TABLE OF NVARCHAR2(32767); TYPE nuntab IS TABLE OF NUMBER; TYPE intntab IS TABLE OF PLS_INTEGER;

Dependencies

ALL_CONSTRAINTS	JSON$USER_COLLECTION_METADATA
ALL_CONS_COLUMNS	JSON_ARRAY_T
ALL_IND_COLUMNS	JSON_ELEMENT_T
DBMS_ASSERT	JSON_KEY_LIST
DBMS_JSON	JSON_OBJECT_T
DBMS_OUTPUT	PLITBLM
DBMS_REDEFINITION	SODA_COLLECTION_T
DBMS_SCHEDULER	USER_JSON_COLLECTIONS
DBMS_SODA	USER_JSON_COLLECTION_TABLES
DBMS_SODA_DML	USER_SCHEDULER_JOBS
DBMS_SODA_UTIL	USER_SYNONYMS
DBMS_SPACE	XMLTYPE
DUAL

Documented No

Exceptions

Error Code	Reason
ORA-00942	SQL_OBJECT_NOT_EXISTS
ORA-00955	SQL_OBJECT_EXISTS
ORA-06513	SQL_INDEX_OUT_OF_BOUNDS

First Available Not known

Security Model Owned by XDB with EXECUTE granted to the SODA_APP role.

Source {ORACLE_HOME}/rdbms/admin/dbmssodacoll.sql
{ORACLE_HOME}/rdbms/admin/prvtsodacoll.plb

Subprograms

ABORT_REDEF_COLLECTION	DROP_DANGLING_COLLECTIONS
ADD_VALIDATION_SCHEMA	DROP_DANGLING_COLLECTIONS_INNER
BLOB_OSON_TO_JSON	DROP_VALIDATION_SCHEMA
CANONICAL_DESCRIPTOR	ENABLE_VALIDATION_SCHEMA
CAN_REDEF_COLLECTION	FINISH_REDEF_COLLECTION
CHANGE_SCHEMA	GET_COLLECTION_STATISTICS
CHECK_COLLECTION_CONSTRAINTS	GET_DEFAULT_METADATA_C
CLONE_COLLECTION	GET_DEFAULT_METADATA_V
COPY_COLLECTION_DEPENDENTS	GET_INDEX_STATISTICS
CREATE_COLLECTION	GET_PARAMETERS
CREATE_COLLECTION_INNER	GET_SCN
CREATE_INDEX	GET_SERVICE_AND_COMPATIBLE
CREATE_INTERIM_COLLECTION	GET_SQL_TEXT
CREATE_VIEW_FROM_DG	GET_VALIDATION_SCHEMA
DELETE_COLLECTION_METADATA	IS_DEFAULT_ADB_COLLECTION
DELETE_COLLECTION_METADATA_INNER	LIST_COLLECTIONS
DESCRIBE_COLLECTION	LIST_INDEXES
DESCRIBE_INDEX	LIST_USAGE_STATISTICS
DISABLE_VALIDATION_SCHEMA	RENAME COLUMN
DROP_COLLECTION	RENAME_COLIMN_INNER
DROP_COLLECTIONS	START_REDEF_COLLECTION
DROP_COLLECTION_INNER	SYNC_INTERIM_COLLECTION

ABORT_REDEF_COLLECTION

Cleans Up Errors From The Redefinition Process dbms_soda_admin.abort_redef_collection( p_original_collection_name IN NVARCHAR2, p_interim_collection_name IN NVARCHAR2);

TBD

ADD_VALIDATION_SCHEMA (new 23ai)

Undocumented dbms_soda_admin.add_validation_schema( p_collection_name IN NVARCHAR2, p_schema IN JSON, p_novalidate IN BOOLEAN, p_schema_name IN VARCHAR2);

TBD

BLOB_OSON_TO_JSON (new 23ai)

Undocumented dbms_soda_admin.blob_oson_to_json( p_schema_name IN VARCHAR2, p_table_name IN VARCHAR2, p_secret_key IN VARCHAR2);

TBD

CANONICAL_DESCRIPTOR (new 23ai)

Undocumented dbms_soda_admin.canonical_descriptor(p_descriptor IN VARCHAR2) RETURN VARCHAR2;

TBD

CAN_REDEF_COLLECTION

Determines if a given collection can be redefined online dbms_soda_admin.can_redef_collection(p_original_collection_name IN NVARCHAR2);

TBD

CHANGE_SCHEMA

Replaces the value of the schemaName field in the metadata descriptor dbms_soda_admin.change_schema( p_descriptor IN VARCHAR2, p_schema IN VARCHAR2) RETURN VARCHAR2;

CHECK_COLLECTION_CONSTRAINTS

Validate collection constraints dbms_soda_admin.check_collection_constraints(p_interim_collection_name IN NVARCHAR2);

TBD

CLONE_COLLECTION (new 23ai)

Undocumented dbms_soda_admin.clone_collection( p_source_collection_name IN NVARCHAR2, p_target_collection_name IN NVARCHAR2, p_target_table_name IN VARCHAR2);

TBD

COPY_COLLECTION_DEPENDENTS

Copies the dependant objects of the original collection to the interim collection dbms_soda_admin.copy_collection_dependents( p_original_collection_name IN NVARCHAR2, p_interim_collection_name IN NVARCHAR2, p_ignore_errors IN BOOLEAN, p_num_errors OUT PLS_INTEGER);

TBD

CREATE_COLLECTION

Create a collection for a specified URI name, with a specified descriptor. The URI name is case-sensitive. dbms_soda_admin.create_collection( p_uri_name IN NVARCHAR2, p_create_mode IN VARCHAR2 DEFAULT 'MAP', p_descriptor IN OUT VARCHAR2, P_CREATE_TIME OUT VARCHAR2, p_verbose IN BOOLEAN DEFAULT FALSE);

TBD

CREATE_COLLECTION_INNER (new 23ai)

Undocumented dbms_soda_admin.create_collection_inner( p_uri_name IN NVARCHAR2, p_create_mode IN VARCHAR2, p_descriptor IN OUT VARCHAR2, p_create_time OUT VARCHAR2, p_verbose IN BOOLEAN, p_current_user IN VARCHAR2, p_current_schema IN VARCHAR2, p_23c_driver IN BOOLEAN, p_schema IN JSON, p_schema_name IN VARCHAR2);

TBD

CREATE_INDEX

Create an index based on an index specification, for a collection with the supplied URI name dbms_soda_admin.create_index( p_uri_name IN NVARCHAR2, p_index_spec IN VARCHAR2, p_verbose IN BOOLEAN DEFAULT FALSE);

TBD

CREATE_INTERIM_COLLECTION

Undocumented dbms_soda_admin.create_interim_collection( p_original_collection_name IN NVARCHAR2, p_interim_collection_name IN NVARCHAR2, p_interim_metadata IN VARCHAR2 default null);

TBD

CREATE_VIEW_FROM_DG

Creates a view with relational columns, using scalar JSON fields as specified in the data guide dbms_soda_admin.create_view_from_dg( p_uri_name IN NVARCHAR2, p_view_name IN VARCHAR2, p_data_guide IN CLOB, p_materialize IN BOOLEAN DEFAULT FALSE);

TBD

DELETE_COLLECTION_METADATA

Deletes all entries in XDB.JSON$COLLECTION_METADATA belonging to the schema it was called from dbms_soda_admin.delete_collection_metadata(p_key IN VARCHAR2);

TBD

DELETE_COLLECTION_METADATA_INNER (new 23ai)

Undocumented dbms_soda_admin.delete_collection_metadata_inner( p_key IN VARCHAR2, p_current_user IN VARCHAR2);

TBD

DESCRIBE_COLLECTION

Describe a single collection. The input collection name is case-sensitive dbms_soda_admin.decribe_collection( p_uri_name IN OUT NVARCHAR2, p_descriptor OUT VARCHAR2);

TBD

DESCRIBE_INDEX

Describes the index on a collection with the supplied URI name and index name dbms_soda_admin.describe_index( p_uri_name IN NVARCHAR2, p_index_name IN VARCHAR2, p_index_spec OUT VARCHAR2, p_verbose IN BOOLEAN DEFAULT FALSE);

TBD

DISABLE_VALIDATION_SCHEMA (new 23ai)

Undocumented dbms_soda_admin.disable_validation_schema( p_collection_name IN NVARCHAR2, p_schema_name IN VARCHAR2);

TBD

DROP_COLLECTION

Drop a collection given the name. This forcibly drops the the collection regardless of the drop policy, which must be enforced by the calling code. dbms_soda_admin.drop_collection( p_uri_name IN NVARCHAR2, p_purge IN VARCHAR2 DEFAULT 'FALSE', p_drop_mapped_table IN VARCHAR2 DEFAULT 'FALSE');

TBD

DROP_COLLECTIONS

Drops all collections in the current user's schema dbms_soda_admin.drop_collections( p_collections OUT NVCNTAB, p_errors OUT VCNTAB, p_force IN VARCHAR2, p_purge IN VARCHAR2 DEFAULT 'FALSE', p_drop_mapped_table IN VARCHAR2 DEFAULT 'FALSE');

TBD

DROP_COLLECTION_INNER (new 23ai)

Undocumented dbms_soda_admin.drop_collection_inner( p_uri_name IN NVARCHAR2, p_purge IN VARCHAR2, p_drop_mapped_table IN VARCHAR2, p_current_user IN VARCHAR2, p_schema_name IN VARCHAR2);

TBD

DROP_DANGLING_COLLECTIONS

Deletes metadata for collections that no longer have tables or views backing them up belonging to the schema it was called from. dbms_soda_admin.drop_dangling_collections;

exec xdb.dbms_soda_admin.drop_dangling_collections;

      

      PL/SQL procedures successfully commpleted.

DROP_DANGLING_COLLECTIONS_INNER (new 23ai)

Undocumented dbms_soda_admin.drop_dangling_collections_inner(p_current_user IN VARCHAR2);

TBD

DROP_VALIDATION_SCHEMA (new 23ai)

Undocumented dbms_soda_admin.drop_validation_schema( p_collection_name IN NVARCHAR2, p_schema_name IN VARCHAR2);

TBD

ENABLE_VALIDATION_SCHEMA (new 23ai)

Undocumented dbms_soda_admin.enable_validation_schema( p_collection_name IN NVARCHAR2, p_novalidate IN BOOLEAN, p_schema_name IN VARCHAR2);

TBD

FINISH_REDEF_COLLECTION

Registers a dependent objects in the data dictionary dbms_soda_admin.finish_redef_collection( p_original_collection_name IN NVARCHAR2, p_interim_collection_name IN NVARCHAR2, p_dml_lock_timeout IN PLS_INTEGER, p_omit_constraint_check IN BOOLEAN DEFAULT FALSE);

TBD

GET_COLLECTION_STATISTICS

Returns the statistics for a collection as JSON text dbms_soda_admin.get_collection_statistics( p_uri_name IN NVARCHAR2, p_force_analyze IN BOOLEAN DEFAULT FALSE, p_statistics OUT VARCHAR2);

-- a sample return as documented by Oracle with 21c

      

      {

        "name" : "SODA_COLL" --> Name of the collection

        "schema" : "SCOTT" --> Owner of the collection

        "blockSize" : 8192, --> Block size of the tablespace

        "numRows" : 5000, --> Number of documents in the collection

        "avgRowLen" : 536, --> Average length of a document in collection

        "sampleSize" : 5000 --> Sample size used in analyzing collection

        "lastAnalyzed" : --> Date on which this collection was most

        "2018-07-18T11:09:00" recently analyzed

        "usedSize" : 2298150, --> Total size used by the collection

        "tableBlocks" :

        {

          "allocated" : 412, --> Number of blocks allocated for the table

          "empty" : 95, --> Number of empty blocks

          "usedSize" : 2281766 --> Average size used by the table

        },

        "lobBlocks" :

          {

          "allocated" : 3, --> Number of blocks allocated in LOB segment

          "empty" : 1, --> Number of empty blocks

          "usedSize" : 16384 --> Average size used by the LOB

        }

      }

GET_DEFAULT_METADATA_C

Returns the default metadata as CLOB dbms_soda_admin.get_default_metadata_c(p_metadata OUT CLOB);

DECLARE

       outVal CLOB;

      BEGIN

        xdb.dbms_soda_admin.get_default_metadata_c(outVal);

        dbms_output.put_line(outVal);

      END;

      /

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

      "contentColumn":{"name":"JSON_DOCUMENT","sqlType":"JSON"}, "lastModifiedColumn":{"name":"LAST_MODIFIED"}, 
      "versionColumn":{"name":"VERSION","method":"UUID"}, "creationTimeColumn":{"name":"CREATED_ON"}, "readOnly":false}

      

      PL/SQL procedure successfully completed.

GET_DEFAULT_METADATA_V

Returns the default metadata as a VARCHAR2 dbms_soda_admin.get_default_metadata_v(p_metadata OUT CLOB);

DECLARE

       outVal VARCHAR2(4000);

      BEGIN

        xdb.dbms_soda_admin.get_default_metadata_v(outVal);

        dbms_output.put_line(outVal);

      END;

      /

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

      "contentColumn":{"name":"JSON_DOCUMENT","sqlType":"JSON"}, "lastModifiedColumn":{"name":"LAST_MODIFIED"}, 
      "versionColumn":{"name":"VERSION","method":"UUID"}, "creationTimeColumn":{"name":"CREATED_ON"}, "readOnly":false}

      

      PL/SQL procedure successfully completed.

GET_INDEX_STATISTICS

Returns the statistics for a non-domain index on a collection as JSON dbms_soda_admin.get_index_statistics( p_uri_name IN NVARCHAR2, p_index_name IN VARCHAR2, p_force_analyze IN BOOLEAN DEFAULT FALSE, p_statistics OUT VARCHAR2);

-- a sample return as documented by Oracle with 21c

      

      {

        "name" : "SODA_COLL" --> Name of the index

        "schema" : "SCOTT" --> Owner of the index

        "blockSize" : 8192, --> Block size of the tablespace

        "numRows" : 5000 --> Number of rows in the index

        "sampleSize" : 5000 --> Sample size used in analyzing the index

        "lastAnalyzed" : --> Date on which this index was most

        "2018-07-18T11:09:00" recently analyzed

        "indexBlocks" :

          {

          "allocated" : 20, --> Number of blocks allocated for the index

          "empty" : 3, --> Number of empty blocks

          "usedSize" : 139264 --> Average size used by the index

       
      }

      }

GET_PARAMETERS

Returns DB parameters as name/value pairs. Currently returns 3 values: PKEY P_VALUE VARCHAR2_MAX 4000 or 32767, RAW_MAX 2000 or 32767, or "NVARCHAR2_MAX" 2000, 4000, 16383, or 32767 dbms_soda_admin.get_parameters( p_key IN OUT xdb.dbms_soda_admin.vctab, p_value IN OUT xdb.dbms_soda_admin.vctab);

DECLARE

       keyVal xdb.dbms_soda_admin.vctab;

       valVal xdb.dbms_soda_admin.vctab;

      BEGIN

        keyVal(1) := 'RAW_MAX';

        xdb.dbms_soda_admin.get_parameters(keyVal, valVal);

        dbms_output.put_line(keyVal(1));

        dbms_output.put_line(valVal(1));

      END;

      /

      VARCHAR2_MAX

      4000

      

      PL/SQL procedure successfully completed.

GET_SCN

Returns the database SCN value dbms_soda_admin.get_scn(p_scn OUT NUMBER);

DECLARE

       scnVal1 v$database.current_scn%TYPE;

       scnVal2 v$database.current_scn%TYPE;

      BEGIN

        SELECT current_scn

        INTO scnVal1

        FROM v$database;

      

        xdb.dbms_soda_admin.get_scn(scnVal2);

      

        dbms_output.put_line(scnVal1);

        dbms_output.put_line(scnVal2);

      END;

      /

      65494192

      65494193

      

      PL/SQL procedure successfully completed.

GET_SERVICE_AND_COMPATIBLE

Returns service name (cloud), and compatible parameter

What is fascinating about the value returned by p_service is that the Oracle 21c database is residing in the Oracle OCI Cloud: You wouldn't know it from the string 'ONPREMISE'. dbms_soda_admin.get_service_and_compatible( p_service OUT VARCHAR2, p_compatible OUT NUMBER);

DECLARE

       srvVal dbms_id;

       cmpVal NUMBER;

      BEGIN

        xdb.dbms_soda_admin.get_service_and_compatible(srvVal, cmpVal);

        dbms_output.put_line(srvVal);

        dbms_output.put_line(cmpVal);

      END;

      /

      ONPREMISE

      23

      

      PL/SQL procedure successfully completed.

GET_SQL_TEXT

Returns the current SQL statement dbms_soda_admin.get_sql_text(p_sqltext OUT VARCHAR2);

DECLARE

       outVal VARCHAR2(4000);

      BEGIN

        xdb.dbms_soda_admin.get_sql_text(outVal);

        dbms_output.put_line(outVal);

      END;

      /

      

      PL/SQL procedure successfully completed.

GET_VALIDATION_SCHEMA (new 23ai)

Undocumented dbms_soda_admin.get_validation_schema( p_collection_name IN NVARCHAR2, p_schema OUT JSON p_schema_name IN VARCHAR2);

TBD

IS_DEFAULT_ADB_COLLECTION (new 23ai)

Undocumented dbms_soda_admin.is_default_adb_collection(p_collection_name IN NVARCHAR2) RETURN BOOLEAN;

TBD

LIST_COLLECTIONS

List collections by returning a cursor with two columns: the URI name and a JSON descriptor in URI string order dbms_soda_admin.list_collections( p_start_name IN VARCHAR2 DEFAULT NULL, p_results OUT sys_refcursor);

TBD

LIST_INDEXES

List all the indexes on a collection with the supplied URI name accessible to the invoker (CURRENT_USER) dbms_soda_admin.list_indexes( p_uri_name IN NVARCHAR2, p_index_name IN VARCHAR2 DEFAULT NULL, p_index_specS OUT xdb.dbms_soda_admin.vctab);

TBD

LIST_USAGE_STATISTICS (new 23ai)

Undocumented dbms_soda_admin.list_usage_statistics( p_access_type IN VARCHAR2, p_sort IN NUMBER, p_limit IN NUMBER, p_statistics OUT VARCHAR2, p_schema_name IN VARCHAR2);

TBD

RENAME_COLUMN (new 23ai)

Undocumented dbms_soda_admin.rename_column( p_collection_name IN NVARCHAR2, p_old_column_name IN VARCHAR2, p_new_column_name IN VARCHAR2);

TBD

RENAME_COLUMN_INNER (new 23ai)

Undocumented dbms_soda_admin.rename_column_inner( p_collection_name IN NVARCHAR2, p_old_column_name IN VARCHAR2, p_new_column_name IN VARCHAR2 p_current_user IN VARCHAR2);

TBD

START_REDEF_COLLECTION

Starts the Redefinition process for the collection dbms_soda_admin.start_redef_collection( p_original_collection_name IN NVARCHAR2, p_interim_collection_name IN NVARCHAR2, p_copy_vpd_opt IN BINARY_INTEGER, p_refresh_dep_mviews IN VARCHAR2);

TBD

SYNC_INTERIM_COLLECTION

Maintains synchronization between the original and interim collections dbms_soda_admin.sync_interim_collection( p_original_collection_name IN NVARCHAR2, p_interim_collection_name IN NVARCHAR2, p_omit_constraint_check IN BOOLEAN DEFAULT FALSE);

TBD