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.
- Getting the Metadata of an Existing Collection
You useSODA_COLLECTION_T
methodget_metadata()
to get all of the metadata for a collection, as a JSON document. - 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 functionDBMS_SODA.create_collection
.
See Also:
-
Overview of SODA Document Collections in Oracle Database Introduction to Simple Oracle Document Access (SODA) for general information about SODA document collections and their metadata
-
SODA Collection Metadata Components (Reference) in Oracle Database Introduction to Simple Oracle Document Access (SODA) for reference information about collection metadata components
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
methodget_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:
-
Oracle Database PL/SQL Packages and Types Reference for information about PL/SQL function
DBMS_SODA.create_collection
-
Oracle Database PL/SQL Packages and Types Reference for information about
SODA_COLLECTION_T
methodget_metadata()
-
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 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