Oracle DBMS_SODA_ADMIN
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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
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)
??? 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

Related Topics
Built-in Functions
Built-in Packages
Database Security
DBMS_REDEFINITION
DBMS_SODA
DBMS_SODA_DML
DBMS_SODA_DOM
DBMS_SODA_USER_ADMIN
DBMS_SODA_USER_DML
DBMS_SODA_UTIL
SODA for PL/SQL
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx