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 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:

  • GET_METADATA Function in Oracle Database PL/SQL Packages and Types Reference for information about SODA_COLLECTION_T method get_metadata()

  • JSON_QUERY in Oracle Database SQL Language Reference for information about SQL/JSON function json_query

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;
/

The default metadata for a collection is presented in Default Collection Metadata in Oracle Database Introduction to Simple Oracle Document Access (SODA).

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:

  • CREATE_COLLECTION Function in Oracle Database PL/SQL Packages and Types Reference for information about PL/SQL function DBMS_SODA.create_collection

  • SODA_COLLECTION_T Type in Oracle Database PL/SQL Packages and Types Reference for information about SODA_COLLECTION_T method get_metadata()

  • JSON_QUERY in Oracle Database SQL Language Reference for information about SQL/JSON function json_query

Example 4-2 Creating a Collection That Has Custom Metadata

This example creates a collection with the default metadata, except that the key assignment method is set to CLIENT.

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" : "ID", "assignmentMethod" : "CLIENT" },
        "contentColumn" : {"name" : "JSON_DOCUMENT"},
        "versionColumn" : {"name" : "VERSION"},
        "lastModifiedColumn" : {"name" : "LAST_MODIFIED"},
        "creationTimeColumn" : {"name" : "CREATED_ON"}}';
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 any 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 (that is, other than 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 was current when the collection was created. 

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

Related Topics

Parent topic: SODA Collection Configuration Using Custom Metadata