Data Dictionary Views for SODA Collections

Starting with 26ai, JSON collection tables and views are listed in the *_JSON_COLLECTIONS data dictionary views:

• USER_JSON_COLLECTIONS for 26ai JSON collection tables and views owned by the current user
• ALL_JSON_COLLECTIONS for 26ai JSON collection tables and views owned by the current user, as well as those owned by other users that the current user can access
• DBA_JSON_COLLECTIONS for all 26ai JSON collection tables and views in the database

The *_SODA_COLLECTIONS views list both 26ai collections based on JSON collection tables and views and pre-26ai SODA collections. There are two such views:

• USER_SODA_COLLECTIONS for all collections owned by the current user
• DBA_SODA_COLLECTIONS for all collections in the database

The *_SODA_COLLECTIONS views include collection metadata as JSON in the JSON_DESCRIPTOR column and indicate whether a collection is based on a table or a view in the OBJECT_TYPE column.

Collections based on JSON collection tables and views:

• have "native": true in the JSON metadata stored in the JSON_DESCRIPTOR column
• also appear in *_JSON_COLLECTIONS

Example 1-1 Selecting Collection Data From USER_SODA_COLLECTIONS

This example selects all columns from the row of view USER_SODA_COLLECTIONS that corresponds to collection name myCol.

SELECT * FROM USER_SODA_COLLECTIONS WHERE URI_NAME = 'myCol';

Collection metadata is also available programmatically through collection metadata accessors in the various SODA implementations.