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

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