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:

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

For example, if you change the content column type from BLOB (the default value) to VARCHAR2 then you must understand the implications (content size for VARCHAR2 is limited to 32K bytes, character-set conversion can take place, and so on).

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
}

Parent topic: SODA Collection Configuration Using Custom Metadata

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

Parent topic: SODA Collection Configuration Using Custom Metadata