1. SODA for PL/SQL Developer's Guide
  2. SODA Collection Configuration Using Custom Metadata

4 SODA Collection Configuration Using Custom Metadata

SODA collections are highly configurable. You can customize collection metadata, to obtain different behavior from that provided by default.

Note:

Although you can customize collection metadata to obtain different behavior from that provided by default, Oracle recommends against this unless you have a compelling reason. Customizing collection metadata requires familiarity with Oracle Database concepts, such as SQL data types. Because SODA collections are implemented on top of Oracle Database tables (or views), many collection configuration components are related to the underlying table configuration.

See Also:

4.1 Getting the Metadata of an Existing Collection

You use SODA_COLLECTION_T method get_metadata() to get all of the metadata for a collection, as a JSON document.

See Also:

Example 4-1 Getting the Metadata of a Collection

This example shows the result of invoking SODA_COLLECTION_T method get_metadata() on the collection with the default configuration that was created using Example 3-3. (It also uses SQL/JSON function json_query, with keyword PRETTY, to pretty-print the JSON data obtained.) 

DECLARE
    collection  SODA_COLLECTION_T;
BEGIN
    collection := DBMS_SODA.open_collection('myCollectionName');
    IF collection IS NULL THEN
        DBMS_OUTPUT.put_line('Collection does not exist');
    ELSE
        DBMS_OUTPUT.put_line('Metadata: '
                             || json_query(collection.get_metadata, '$' PRETTY));
    END IF;
END;
/

Example 4-2 Default Collection Metadata

{
   "schemaName" : "mySchemaName",
   "tableName" : "myTableName",
   "keyColumn" :
   {
      "name" : "ID",
      "sqlType" : "VARCHAR2",
      "maxLength" : 255,
      "assignmentMethod" : "UUID"
   },
   "contentColumn" :
   {
      "name" : "JSON_DOCUMENT",
      "sqlType" : "BLOB",
      "compress" : "NONE",
      "cache" : true,
      "encrypt" : "NONE",
      "validation" : "STANDARD"
   },
   "versionColumn" :
   {
     "name" : "VERSION",
     "method" : "SHA256"
   },
   "lastModifiedColumn" :
   {
     "name" : "LAST_MODIFIED"
   },
   "creationTimeColumn" :
   {
      "name" : "CREATED_ON"
   },
   "readOnly" : false
}

4.2 Creating a Collection That Has Custom Metadata

To create a document collection that has custom metadata, you pass its metadata, as JSON data, to PL/SQL function DBMS_SODA.create_collection.

The optional second argument to PL/SQL function DBMS_SODA.create_collection is a SODA collection specification. It is JSON data that specifies the metadata for the new collection.

If a collection with the same name already exists then it is simply opened and its handle is returned. If the custom metadata provided does not match the metadata of the existing collection then the collection is not opened and an error is raised. (To match, all metadata fields must have the same values.)

See Also:

Example 4-3 Creating a Collection That Has Custom Metadata

This example creates a collection with custom metadata that specifies two metadata columns, named KEY (for document keys), and JSON (for document content type JSON). The key assignment method is CLIENT, and the content-column SQL data type is VARCHAR2. The example uses SODA_COLLECTION_T method get_metadata() to get the complete metadata from the newly created collection, which it passes to SQL/JSON function json_query to pretty-print (using keyword PRETTY). 

DECLARE
    collection SODA_COLLECTION_T;
    metadata VARCHAR2(4000) :=
      '{"keyColumn" : {"name" : "KEY",  "assignmentMethod": "CLIENT" },
        "contentColumn" : { "name" : "JSON", "sqlType": "VARCHAR2" } }';
BEGIN
    collection := DBMS_SODA.create_collection('myCustomCollection', metadata);
    DBMS_OUTPUT.put_line('Collection specification: ' ||
                         json_query(collection.get_metadata, '$' PRETTY));
END
/

This is the pretty-printed output. The values of fields for keyColumn and contentColumn that are not specified in the collection specification are defaulted. The values of fields other than those provided in the collection specification (keyColumn and contentColumn) are also defaulted. The value of field tableName is defaulted from the collection name. The value of field schemaName is the database schema (user) that is current when the collection is created. 

Collection specification: {
  "schemaName" : "mySchemaName",
  "tableName" : "myCustomCollection",
  "keyColumn" :
  {
    "name" : "KEY",
    "sqlType" : "VARCHAR2",
    "maxLength" : 255,
    "assignmentMethod" : "CLIENT"
  },

"contentColumn" :
  {
    "name" : "JSON",
    "sqlType" : "VARCHAR2",
    "maxLength" : 4000,
    "validation" : "STANDARD"
  },
  "readOnly" : false
}

Related Topics