Application Development

SODA and PL/SQL

Use the SODA API for PL/SQL to work with JSON— and without SQL—in Oracle Database.

By Steven Feuerstein

March/April 2019

SQL—Structured Query Language—is the most powerful and widely used language for manipulating data, primarily in relational databases such as Oracle Database. Its declarative and set-oriented approach handles a wide variety of use cases and produces concise, elegant ways to both query and change data.

For some scenarios and some developers, however, relational data and SQL are not seen as a good fit. The flexibility of a JSON document can, for example, better meet the demands of a rapidly changing schema or one that is poorly defined at the outset of a project. For documents in such cases and others, Oracle Database offers Simple Oracle Document Access (SODA) APIs. This set of NoSQL (“not only SQL”) –style APIs enables you to create and store collections of documents in Oracle Database. You can retrieve and query them without needing to know SQL or how the documents are stored in the database.

SODA APIs are available for Java, C, REST, Python, Node.js, and PL/SQL. This article explores the basic concepts behind SODA, as exposed through the SODA API for PL/SQL. Links at the end of the article provide more details on SODA generally and on JSON.

Overview

All of the SODA APIs share the same concepts and flow. First, because the point of SODA is to relieve a developer of the need to know SQL, the APIs are not table-focused but are instead document-centric. Use a SODA API to manage (create, read, update, delete) documents of just about any kind, including videos, images, and—most commonly—JSON.

Documents are organized into collections. You can have one collection for all your documents, you can create a collection for each type of document (my video collection, my song collection, and so on), or you can create collections for different components of your application.

You can query the contents of documents by using pattern matching (query-by-example) or document keys.

You might be wondering why anyone who writes PL/SQL would be interested in avoiding SQL and instead opt for a SODA API?

Most back-end database developers will, of course, stick to the normal way of using PL/SQL: as a way to enhance the SQL language and provide additional security and as a means of implementing business logic.

In large enterprises that have Oracle Database installed, however, more and more front-end (and/or full-stack) developers want to work with document databases. With the wide array of SODA APIs now available for Oracle Database, they can have the best of both worlds: the power and security of the world’s best relational database combined with the ease of use and flexibility of JSON-based document management with easy-to-use NoSQL-style SODA drivers for various programming languages.

In addition, the PL/SQL SODA API makes it possible for database developers to access collections and documents created through other SODA APIs. Thus, a JavaScript developer could use the Node.js SODA API to load JSON documents into the database. A SQL-savvy back-end developer could then bring the full power of SQL to that data: indexing access to the documents and building efficient analytic queries against them.

All PL/SQL SODA operations are made available through the new-to-Oracle Database 18c DBMS_SODA package and several object types, including SODA_collection_t and SODA_document_t. To use the package and manage SODA collections and documents in your schema of choice, you’ll need to grant the SODA_APP role to that schema.

That’s all you need to get going with SODA in PL/SQL! Let’s start exploring.

Working with Collections

Before you can put documents into a collection, you have to create it. In the following block, I create a collection to hold information about my friends. I then use the get_name and get_metadata methods of the SODA_collection_t type to display that information.

DECLARE
   l_collection   soda_collection_t;
BEGIN
   l_collection := dbms_soda.create_collection ('MyFriends');
   DBMS_OUTPUT.put_line (
      'MyFriends ID = ' || l_collection.get_name ());
   DBMS_OUTPUT.put_line (
      'MyFriends JSON metadata = ' || l_collection.get_metadata ());
END;
/

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

Since I passed only a name to create_collection, all the defaults were used for the metadata, which means in part that only JSON documents can be stored in the collection, audit information about the collection is tracked in predefined column names, and the SQL type used for documents is BLOB.

You can override the metadata defaults by supplying a second argument in the call to create_collection, in the form of a JSON document with the values you want to change.

Once a collection is created, I can perform these operations:

  • Open a collection
  • Drop a collection
  • Get a list of all the collections in the user’s schema

You must open a collection before you perform read or write operations. The open_collection function accepts a collection name and returns an instance of type SODA_collection_t. If the function returns NULL, then no collection exists with that name.

The list_collection_names function returns the names of all available collections in the current schema as a PL/SQL nested table of type SODA_collname_list_t.

The following code demonstrates all of these subprograms.

CREATE OR REPLACE PROCEDURE show_soda_collections (title_in IN VARCHAR2)
IS
   l_collections   soda_collname_list_t := dbms_soda.list_collection_names;
BEGIN
   DBMS_OUTPUT.put_line (title_in);

   FOR indx IN 1 .. l_collections.COUNT
   LOOP
      DBMS_OUTPUT.put_line (
         'Collection ' || indx || ' is named ' || l_collections (indx));
   END LOOP;
END;
/

DECLARE
   l_collection   soda_collection_t;
BEGIN
   l_collection := dbms_soda.create_collection ('Coll1');

   l_collection := dbms_soda.create_collection ('Coll2');

   l_collection := dbms_soda.open_collection ('Coll3');

   IF l_collection IS NULL
   THEN
      DBMS_OUTPUT.put_line ('No collection named "Coll3"');
   END IF;

   show_soda_collections (title_in => 'Before Drop');

   IF dbms_soda.drop_collection ('Coll1') = 1
   THEN
      show_soda_collections (title_in => 'After Drop');
   END IF;
END;
/

No collection named "Coll3"

Before Drop
Collection 1 is named Coll1
Collection 2 is named Coll2

After Drop
Collection 1 is named Coll2

Working with Documents

Well, a collection all by itself doesn’t really get you anywhere. What you need is some documents!

Once the collection is created, you use methods of the SODA_collection_t type to manage documents in that collection as follows:

Insert a document into a collection insert_one and insert_one_and_get
Find a document in a collection find_one
Remove a document from a collection remove_one
Replace a document in a collection replace_one and replace_one_and_get

Note that the two and_get methods return all the information about the new or replaced document except for the content. You can get that data by calling the find_one method to retrieve all the document’s information.

In the block below, I insert two new documents, using each of the two insert methods. I then demonstrate how find_one works.

DECLARE
   l_collection     soda_collection_t;
   l_document       soda_document_t;
   l_new_document   soda_document_t;
BEGIN
   l_collection := dbms_soda.create_collection ('WithDocuments');

   IF l_collection.insert_one (
         soda_document_t (
            b_content   => UTL_RAW.cast_to_raw (
                             '{"friend_type":1,"friend_name":"Lakshmi"}'))) = 1
   THEN
      DBMS_OUTPUT.put_line ('BLOB document inserted');
   END IF;

   l_new_document :=
      l_collection.insert_one_and_get (
         soda_document_t (
            b_content   => UTL_RAW.cast_to_raw (
                             '{"friend_type":2,"friend_name":"Samuel"}')));

   DBMS_OUTPUT.put_line ('Samuel''s key: ' || l_new_document.get_key);
   DBMS_OUTPUT.put_line (
      'Samuel''s media_type: ' || l_new_document.get_media_type);
END;
/

BLOB document inserted
Samuel's key: 1697CFFB902A4FC2BFAD61DA31CF3B07
Samuel's media_type: application/json

Now let’s try to insert a CLOB document. To do that, I can specify the C_CONTENT parameter name in my named notation invocation of insert_one (in the previous block, I referenced the B_CONTENT parameter).

DECLARE
   l_collection   soda_collection_t;
   l_document     soda_document_t;
BEGIN
   l_collection := dbms_soda.create_collection ('WithDocuments');

   IF l_collection.insert_one (
         soda_document_t (
            c_content   => '{"friend_type":1,"friend_name":"Lakshmi"}')) = 1
   THEN
      DBMS_OUTPUT.put_line ('VARCAHR2 document inserted');
   END IF;
END;
/

ORA-40659: Data type does not match the specification in the collection metadata.

The call to insert_one failed, precisely for the reason referenced in the error message. When you create a collection using all default metadata, the document type defaults to BLOB. So my first insertion worked but the CLOB payload resulted in an error. There are two ways to fix this: (1) cast the CLOB to BLOB with DBMS_LOB.CONVERTTOBLOB or (2) create the collection specifying CLOB as the data type for the collection, as follows:

DECLARE
  c_use_clob constant   VARCHAR2(1000) :=
       '{"keyColumn":{"assignmentMethod": "CLIENT"},
        "contentColumn":{"sqlType": "CLOB"}}';
  l_collection  soda_collection_t;
BEGIN
  l_collection := DBMS_SODA.create_collection('ClobCollection', c_use_clob);
END;

I’ve shown you how to insert a document. What if you need to remove one? Call the remove_one method. This method accepts the key for your document, which you can obtain from the document’s get_key method.

In the block below, I

  • Create a collection
  • Insert a document and get back the metadata
  • Use the find_one method to verify that it is there
  • Invoke the remove_one method to remove the document
  • Call find_one again to confirm that it is gone
DECLARE
   l_collection       soda_collection_t;
   l_new_document     soda_document_t;
   l_found_document   soda_document_t;
BEGIN
   l_collection := dbms_soda.create_collection ('MyFriends');

   l_new_document :=
      l_collection.insert_one_and_get (
         soda_document_t (
            b_content   => UTL_RAW.cast_to_raw (
                             '{"friend_type":2,"friend_name":"Samuel"}')));

   /* Verify it's there using the find_one method and the key. */
   l_found_document := l_collection.find_one (l_new_document.get_key);

   IF l_found_document IS NOT NULL
   THEN
      DBMS_OUTPUT.put_line (
         'Found document with key: ' || l_new_document.get_key);
   END IF;

   IF l_collection.remove_one (l_new_document.get_key) = 1
   THEN
      DBMS_OUTPUT.put_line ('Document is removed.');
   END IF;

   /* Verify it's there using the find_one method and the key. */
   l_found_document := l_collection.find_one (l_new_document.get_key);

   IF l_found_document IS NULL
   THEN
      DBMS_OUTPUT.put_line ('No document for key.');
   END IF;
END;
/

Found document with key: 42B13A837F964FD5BFC78238985448D2
Document is removed.
No document for key.

What if you need to change a document already in the collection? Remember, you are not using SQL, so you cannot use the handy UPDATE statement to apply a change. Instead, you must replace the existing document (found by key) with another document that contains the modified data.

In the block below, I do the following:

  • Insert a document with a friend named “Helen”
  • Call the find_one method to retrieve the document from the collection, with its content
  • Display that JSON—verifying the name as “Helen”
  • Create a new document with the name “Helena”
  • Call the replace_one_and_get method, specifying the key of the original document and passing it the new document for replacement
  • Verify that the name has been changed to “Helena”
DECLARE
   l_collection          soda_collection_t;
   l_original_document   soda_document_t;
   l_new_document        soda_document_t;
   l_replaced_document   soda_document_t;
BEGIN
   l_collection := dbms_soda.create_collection ('MyFriends2');

   l_original_document :=
      l_collection.insert_one_and_get (
         soda_document_t (
            b_content   => UTL_RAW.cast_to_raw (
                             '{"friend_type":3,"friend_name":"Helen"}')));

   /* Show the content */
   l_original_document := l_collection.find_one (l_original_document.get_key);

   DBMS_OUTPUT.put_line (
         'Original content: '
      || UTL_RAW.cast_to_varchar2 (l_original_document.get_blob));

   /* Create a document (not inserted into any collection) with
      the corrected name "Helena". */
   l_new_document :=
      soda_document_t (
         b_content   => UTL_RAW.cast_to_raw (
                          '{"friend_type":3,"friend_name":"Helena"}'));

   /* Replace the original document and verify the contents */

   l_replaced_document :=
      l_collection.replace_one_and_get (l_original_document.get_key,
                                        l_new_document);
   l_replaced_document := l_collection.find_one (l_replaced_document.get_key);

   DBMS_OUTPUT.put_line (
         'Replaced content: '
      || UTL_RAW.cast_to_varchar2 (l_replaced_document.get_blob));
END;
/

Original content: {"friend_type":3,"friend_name":"Helen"}
Replaced content: {"friend_type":3,"friend_name":"Helena"}

Behind the Scenes

The SODA API is implemented with tables and rows. When you create a collection, a table of the same name is created (with the case preserved):

DECLARE
   l_collection          soda_collection_t;
BEGIN
   l_collection := dbms_soda.create_collection ('SODACollection');
END;
/
SELECT table_name FROM USER_TABLES
 WHERE table_name like 'SODA%'
/

TABLE_NAME
—————————————————
SODACollection

Each document is stored as a row in the collection’s table:

DECLARE
   l_collection soda_collection_t;
   l_document   soda_document_t;
BEGIN
   l_collection := dbms_soda.create_collection ('MyFriends');
   l_document :=
      l_collection.insert_one_and_get (
         soda_document_t (
            b_content   => UTL_RAW.cast_to_raw (
                             '{"friend_type":3,"friend_name":"Helen"}')));
END;
/
SELECT id "UTF8-Encoded JSON String" FROM "MyFriends"
/
UTF8-Encoded JSON String
————————————————————————————————
50D58CF995704F70BFED14673D482A95

These tables are managed for you automatically through the SODA API.

Summary

Most back-end database developers will, of course, stick to the normal way of using PL/SQL: to enhance the SQL language, provide additional security, and implement business logic.

Oracle Database is used more and more widely in distributed applications involving multiple kinds of data. In this context, strong support for both JSON and schema-less development via the SODA APIs will ensure that front-end developers can make the most of the features and power of Oracle Database.

In my next article on SODA and PL/SQL, I will explore the use of the builder pattern to specify read/write patterns in SODA and more.

Next Steps

READ an introduction to SODA.
SODA for PL/SQL Developer’s Guide.

WORK with the LiveSQL script for this article.

Illustration by Wes Rowell

DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.