Oracle | DBMS_SODA

Oracle DBMS_SODA_ADMIN 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 Through 19c this package was used to manage collection metadata. As of 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	DBMS_SODA	JSON_ARRAY_T
ALL_CONS_COLUMNS	DBMS_SODA_DML	JSON_ELEMENT_T
ALL_IND_COLUMNS	DBMS_SODA_UTIL	JSON_KEY_LIST
DBMS_ASSERT	DBMS_SPACE	JSON_OBJECT_T
DBMS_JSON	DUAL	PLITBLM
DBMS_OUTPUT	JSON$USER_COLLECTION_METADATA	SODA_COLLECTION_T
DBMS_REDEFINITION

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

Pragma

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

Subprog rams

ABORT_REDEF_COLLECTION	DESCRIBE_COLLECTION	GET_INDEX_STATISTICS
CAN_REDEF_COLLECTION	DESCRIBE_INDEX	GET_PARAMETERS
CHANGE_SCHEMA	DROP_COLLECTION	GET_SCN
CHECK_COLLECTION_CONSTRAINTS	DROP_COLLECTIONS	GET_SERVICE_AND_COMPATIBLE
COPY_COLLECTION_DEPENDENTS	DROP_DANGLING_COLLECTIONS	GET_SQL_TEXT
CREATE_COLLECTION	FINISH_REDEF_COLLECTION	LIST_COLLECTIONS
CREATE_INDEX	GET_COLLECTION_STATISTICS	LIST_INDEXES
CREATE_INTERIM_COLLECTION	GET_DEFAULT_METADATA_C	START_REDEF_COLLECTION
CREATE_VIEW_FROM_DG	GET_DEFAULT_METADATA_V	SYNC_INTERIM_COLLECTION
DELETE_COLLECTION_METADATA

ABORT_REDEF_COLLECTION (new 21c)

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

CAN_REDEF_COLLECTION (new 21c)

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 (new 21c)

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

TBD

COPY_COLLECTION_DEPENDENTS (new 21c)

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); PRAGMA SUPPLEMENTAL_LOG_DATA(CREATE_COLLECTION, AUTO);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(CREATE_INDEX, AUTO);

TBD

CREATE_INTERIM_COLLECTION (new 21c)

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); PRAGMA SUPPLEMENTAL_LOG_DATA(CREATE_VIEW_FROM_DG, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(DELETE_COLLECTION_METADATA, AUTO);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(DESCRIBE_COLLECTION, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(DESCRIBE_INDEX, NONE);

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'); PRAGMA SUPPLEMENTAL_LOG_DATA(DROP_COLLECTION, AUTO);

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'); PRAGMA SUPPLEMENTAL_LOG_DATA(DROP_COLLECTIONS, NONE);

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; PRAGMA SUPPLEMENTAL_LOG_DATA(DROP_DANGLING_COLLECTIONS, AUTO);

exec xdb.dbms_soda_admin.drop_dangling_collections;

FINISH_REDEF_COLLECTION (new 21c)

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); PRAGMA SUPPLEMENTAL_LOG_DATA(GET_COLLECTION_STATISTICS, NONE);

-- 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 (new 21c)

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 (new 21c)

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); PRAGMA SUPPLEMENTAL_LOG_DATA(GET_INDEX_STATISTICS, NONE);

-- 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 VCTAB, p_value IN OUT VCTAB); PRAGMA SUPPLEMENTAL_LOG_DATA(GET_PARAMETERS, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(GET_SCN, NONE);

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 (new 21c)

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

      21

      

      PL/SQL procedure successfully completed.

GET_SQL_TEXT

Returns the current SQL statement dbms_soda_admin.get_sql_text(p_sqltext OUT VARCHAR2); PRAGMA SUPPLEMENTAL_LOG_DATA(GET_SQL_TEXT, NONE);

DECLARE

       outVal VARCHAR2(4000);

      BEGIN

        xdb.dbms_soda_admin.get_sql_text(outVal);

        dbms_output.put_line(outVal);

      END;

      /

      

      PL/SQL procedure successfully completed.

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); PRAGMA SUPPLEMENTAL_LOG_DATA(LIST_COLLECTIONS, NONE);

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 VCNTAB); PRAGMA SUPPLEMENTAL_LOG_DATA(LIST_INDEXES, NONE);

TBD

START_REDEF_COLLECTION (new 21c)

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 (new 21c)

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