SODA and PL/SQL, Part 2

Use the SODA API for PL/SQL to read and write to SODA documents in Oracle Database.

By Steven Feuerstein

July/August 2019

In my first article on SODA (Simple Oracle Document Access) and PL/SQL, I showed you how to perform SODA operations in Oracle Database 18c and higher to create SODA collections and documents in your schema.

In this article, I explore how to use methods of a new SODA operations type to find documents so that you can perform read and write operations on them.

A New Operations Type

Oracle Database provides the SODA_OPERATION_T type (added in Oracle Database 18.3) to specify all read and write operations, except for inserts (which I covered in my first article on SODA and PL/SQL). It contains methods for finding documents in a collection, replacing those documents, and even removing a document from a collection.

The usual approach to take advantage of these operations is to call the FIND method of SODA_COLLECTION_T. FIND returns an instance of the operation type of the collection. You then use methods of SODA_OPERATION_T to perform the desired actions.

The following are SODA_OPERATION_T methods that perform read operations:

• get_cursor returns a pointer to a cursor that enables you to fetch all specified documents (through the API, not SQL).
• get_one gets a single document.
• count returns the count of documents that match your criteria.

And the following are SODA_OPERATION_T methods that perform write operations:

• replace_one replaces the contents of a document.
• replace_one_and_get replaces the contents and returns the key to the new document.
• remove removes the specified document.

These methods are terminal methods: They are the last method you invoke in a chain of invocations to perform the read or write operation.

Every method call before that in the chain is a nonterminal method, which helps you get to precisely the document(s) you want to read or write. You can think of the nonterminal methods as all contributing in their way to a “WHERE clause” applied to the search through the collection. These are the nonterminal methods:

• key specifies the key for the document you want to operate on.
• keys provides a comma-delimited list of keys.
• Filter uses JSON query-by-example syntax to specify documents of interest, an ordering of the documents returned, and more.
• version specifies the document version for your find operation.
• skip skips N documents and is used for pagination.
• limit limits the retrieval to N documents and is also used for pagination.

Let’s Get Started

To perform operations on a document or a collection of documents, I first need to create that collection. Here’s the data I’ll be working with in this article:

DECLARE
  l_collection   soda_collection_t;
  l_status       PLS_INTEGER;
BEGIN
  l_collection := dbms_soda.create_collection ('FriendsWithDocs');

  l_status :=
    l_collection.insert_one (
      soda_document_t (
        b_content   => UTL_RAW.cast_to_raw (
                        '{"friend_type":1,
                          "friend_name":"Lakshmi"},
                          "favorites": [{"song" : "Somewhere over the Rainbow"},
                                       {"spice" : "tamarind"},
                                       {"flavor" : "cherry"} ]}')));

  l_status :=
    l_collection.insert_one (
      soda_document_t (
        b_content   => UTL_RAW.cast_to_raw (
                        '{"friend_type":2,
                          "friend_name":"Sally"},
                          "favorites": [{"color" : "blue"},
                                       {"flavor" : "chocolate"},
                                       {"flower" : "rose"} ]}')));

  l_status :=
    l_collection.insert_one (
      soda_document_t (
        b_content   => UTL_RAW.cast_to_raw (
                        '{"friend_type":2,
                          "friend_name":"Jorge",
                          "favorites": [{"color" : "green"},
                                       {"flavor" : "chocolate"},
                                       {"tree" : "oak"} ]}')));
END;

The data comes from three friends (friend_name), of two different friend types (friend_type)—I will leave it up to your imagination to choose a couple of types of friends—and it includes a variety of favorites (favorites) in a nested JSON array.

Note: I use cast_to_raw to convert my string to a BLOB. This is OK for demonstrations and small JSON documents but could cause issues with larger documents. A more robust (but also more complicated) solution, suitable for production, would be to use DBMS_LOB subprograms.

Operation Chaining

Method chaining is a common practice in object-oriented programming but less familiar in the procedural world (and PL/SQL is a procedural language). When coding your read and write operations on documents, you will usually chain together several of the nonterminal methods listed previously.

First, here’s a block of code that does not use method chaining:

DECLARE
  l_collection   soda_collection_t;
  l_operation    soda_operation_t;
  l_cursor       soda_cursor_t;
  l_status       BOOLEAN;
BEGIN
  l_collection := dbms_soda.open_collection ('FriendsWithDocs');

  l_operation := l_collection.find ();

  l_cursor := l_operation.get_cursor ();

  l_status := l_cursor.close;
END;

I start the block by opening my collection. Then I call the FIND method to return an instance of the SODA_OPERATION_T type for all the documents in the collection. (I will show you later in the article how to filter that FIND if you don’t want them all). I then call the get_cursor method to return a cursor I can use to iterate through the documents. Finally, because it is the right thing to do, I close the cursor.

Here’s that same functionality, now using the chained method approach.

DECLARE
  l_cursor   soda_cursor_t;
  l_status   BOOLEAN;
BEGIN
  l_cursor :=
    dbms_soda.open_collection ('FriendsWithDocs').find ().get_cursor ();

  l_status := l_cursor.close;
END;

There is no need to declare the collection or operation instances. I simply “chain” the method calls together, using dot notation. So: fewer declarations, fewer lines of code. Each call to open_collection involves a SQL query against the underlying tables. So if you plan to perform multiple find operations on the same collection, you should open it in one step, returning the collection instance, and then use that instance for multiple chained operations.

When you first start working with SODA elements, you might want to break these nonterminal methods out so you can more easily debug the steps in your chain. Once you are familiar with SODA, however, you will find the chained approach preferable.

Iterating Through All Documents

The SODA API offers a cursor to make it easy for you to iterate through all documents returned by the FIND and FILTER methods.

DECLARE
  l_document      soda_document_t;
  l_cursor        soda_cursor_t;
  l_content_shown BOOLEAN := FALSE;
  l_status        BOOLEAN;
BEGIN
  l_cursor :=
    dbms_soda.open_collection ('FriendsWithDocs').find ().get_cursor ();

  -- Loop through the cursor
  WHILE l_cursor.has_next
  LOOP
    l_document := l_cursor.NEXT;

    IF l_document IS NOT NULL
    THEN
      DBMS_OUTPUT.put_line ('Document key: ' || l_document.get_key);

      IF NOT l_content_shown
      THEN
        /* Just show content once to reduce output volume */
        l_content_shown := TRUE;
        DBMS_OUTPUT.put_line (
          'Content: ' || json_query (l_document.get_blob, '$' PRETTY));
      END IF;

      DBMS_OUTPUT.put_line('Creation timestamp: ' || l_document.get_created_on);
      DBMS_OUTPUT.put_line('Last modified timestamp: ' || l_document.get_last_modified);
      DBMS_OUTPUT.put_line('Version: ' || l_document.get_version);
    END IF;
   END LOOP;

   l_status := l_cursor.close;
END;

This cursor block finds all the documents in a collection, returns a cursor to those documents, and then uses these cursor methods to iterate:

• has_next returns TRUE if there is more to “fetch.”
• next gets the next document.

Note that for “basic” find operations in which order is not specified and no pagination methods are called, no ordering is applied to the documents. When calls to skip() or limit() are involved, the order of the documents is based on the internal key for that document, a universal unique identifier (UUID). I’ll explore how to override this sorting later in this article.

Here’s the output from running the above cursor block:

Document key: 29D6E98467EC4FA7BF18572986FF8925
Content: {
  "friend_type" : 1,
  "friend_name" : "Lakshmi",
  "favorites" :
  [
    {
      "song" : "Somewhere over the Rainbow"
    },
    {
      "spice" : "tamarind"
    },
    {
      "flavor" : "cherry"
    }
  ]
}
Creation timestamp: 2019-05-03T18:39:46.787333Z
Last modified timestamp: 2019-05-03T18:39:46.787333Z
Version: 698637C8DC1A843E0078D77DD1680D17A4E8B9A8108B87081BE44902AD9484FD
Document key: 6CFE30D607284F15BF0F59B1FD952842
Creation timestamp: 2019-05-03T18:39:46.787884Z
Last modified timestamp: 2019-05-03T18:39:46.787884Z
Version: D5B1E2370AB9A6C15FDCC0DB53E38D1D3B5306E57B039040E22BF7619A5166B1
Document key: BE2CA098F5554F5DBFF9454A8F02DEA4
Creation timestamp: 2019-05-03T18:39:46.788234Z
Last modified timestamp: 2019-05-03T18:39:46.788234Z
Version: 63A7781DBA6117B6CBACECF5CB9959054D1FF6D0852317701D9A10F9A43DABA4

Finding a Specific Document

Sometimes you’ll want to iterate through multiple documents, and sometimes you’ll want to get just one document. You can get that document by referencing a specific key.

Usually those keys are generated internally and passed back and forth between the database and the application. For purposes of the demonstration below, I create a collection and use the metadata parameter to tell PL/SQL that I will provide the keys myself.

That way I know the key I can use to retrieve the document.

DECLARE
  l_document       soda_document_t;
  l_collection     soda_collection_t;
  l_metadata       VARCHAR2 (4000)
    := '{"keyColumn" : { "assignmentMethod" : "CLIENT" }}';
  l_status         PLS_INTEGER;
BEGIN
  l_collection := dbms_soda.create_collection ('MyOwnKeys', l_metadata);

  DBMS_OUTPUT.put_line (
      'Collection specification: '
   || json_query (l_collection.get_metadata, '$' PRETTY));

  l_status :=
    l_collection.insert_one (
      soda_document_t ('FriendLakshmi',
        b_content   => UTL_RAW.cast_to_raw (
                             '{"friend_type":1,"friend_name":"Lakshmi"}')));

  l_document :=
    l_collection
      .find ()
      .key ('FriendLakshmi')
      .get_one;

  DBMS_OUTPUT.put_line ('Key: ' || l_document.get_key);
  DBMS_OUTPUT.put_line ('Content: ');
  DBMS_OUTPUT.put_line (
    l_document.get_key|| '+'||json_query (l_document.get_blob, '$' PRETTY));
END;

The output below shows first the information about the collection, verifying that the assignmentMethod for the key is CLIENT. Following that is the information for that document.

Collection specification: {
  "schemaName" : "HR",
  "tableName" : "MyOwnKeys",
  "keyColumn" :
  {
    "name" : "KEY",
    "sqlType" : "VARCHAR2",
    "maxLength" : 255,
    "assignmentMethod" : "CLIENT"
  },
  "contentColumn" :
  {
    "name" : "JSON_DOCUMENT",
    "sqlType" : "BLOB",
    "compress" : "NONE",
    "cache" : false,
    "encrypt" : "NONE",
    "validation" : "STANDARD"
  },
  "readOnly" : false
}
Key: FriendLakshmi
Content:
FriendLakshmi+{
  "friend_type" : 1,
  "friend_name" : "Lakshmi"
}

Finding Documents That Match Filters

I’ve shown you how to get all the documents in a collection and a single document by key, but you have much more flexibility than that. You can also use JSON query-by-example syntax to filter your document retrieval.

You do this by adding the filter method to the chained invocation, right after find, as in

dbms_soda.open_collection ('FriendsWithDocs').find ().filter (l_filter).get_cursor ();

In the block below, I set the filter to find only those friends of type “2” (whatever that might be!).

DECLARE
  l_document   soda_document_t;
  l_cursor     soda_cursor_t;
  l_filter     VARCHAR2 (128) := '{"friend_type" : "2"}';
  l_status     BOOLEAN;
BEGIN
  l_cursor :=  dbms_soda.open_collection ('FriendsWithDocs').find ().filter (l_filter)
.get_cursor ();

  WHILE l_cursor.has_next
  LOOP
    l_document := l_cursor.NEXT;

    IF l_document IS NOT NULL
    THEN
      DBMS_OUTPUT.put_line ('Document key: ' || l_document.get_key);
      DBMS_OUTPUT.put_line (
        'Content: ' || json_query (l_document.get_blob, '$' PRETTY));
   END IF;
   END LOOP;

   l_status := l_cursor.close;
END;

Document key: 6CFE30D607284F15BF0F59B1FD952842
Content: {
  "friend_type" : 2,
  "friend_name" : "Sally",
  "favorites" :
  [
    {
      "color" : "blue"
    },
    {
      "flavor" : "chocolate"
    },
    {
      "flower" : "rose"
    }
  ]
}
Document key: BE2CA098F5554F5DBFF9454A8F02DEA4
Content: {
  "friend_type" : 2,
  "friend_name" : "Jorge",
  "favorites" :
  [
    {
      "color" : "green"
    },
    {
      "flavor" : "chocolate"
    },
    {
      "tree" : "oak"
    }
  ]
}

I can also use the query-by-example syntax to search for documents whose nested array elements match a certain criterion. The code below finds and displays all friends whose favorite flavor is chocolate:

DECLARE
  l_document   soda_document_t;
  l_cursor     soda_cursor_t;
  l_filter     VARCHAR2 (128) := '{"favorites.flavor" : "chocolate"}';
  l_status     BOOLEAN;
BEGIN
  l_cursor :=  dbms_soda.open_collection ('FriendsWithDocs').find ()
.filter (l_filter).get_cursor ();

  WHILE l_cursor.has_next
  LOOP
    l_document := l_cursor.NEXT;

    IF l_document IS NOT NULL
    THEN
      DBMS_OUTPUT.put_line ('Document key: ' || l_document.get_key);
      DBMS_OUTPUT.put_line (
         'Content: ' || json_query (l_document.get_blob, '$' PRETTY));
    END IF;
  END LOOP;

  l_status := l_cursor.close;
END;

Document key: 6CFE30D607284F15BF0F59B1FD952842
Content: {
  "friend_type" : 2,
  "friend_name" : "Sally",
  "favorites" :
  [
    {
      "color" : "blue"
    },
    {
      "flavor" : "chocolate"
    },
    {
      "flower" : "rose"
    }
  ]
}
Document key: BE2CA098F5554F5DBFF9454A8F02DEA4
Content: {
  "friend_type" : 2,
  "friend_name" : "Jorge",
  "favorites" :
  [
    {
      "color" : "green"
    },
    {
      "flavor" : "chocolate"
    },
    {
      "tree" : "oak"
    }
  ]
}

I can also find all the documents whose keys match the provided list. I demonstrate this feature below by again specifying a document in which I can provide the keys myself. I do this with the metadata parameter and an assignmentMethod set to CLIENT.

DECLARE
  l_collection   soda_collection_t;
  l_metadata     VARCHAR2 (4000)
     := '{"keyColumn" : { "assignmentMethod": "CLIENT" }}';
  l_keys         soda_key_list_t;
  l_cursor       soda_cursor_t;
  l_status       BOOLEAN;
  l_document     soda_document_t;
BEGIN
  l_collection := dbms_soda.create_collection ('ColorKeys', l_metadata);

  l_status :=
    l_collection.insert_one (
      soda_document_t (
        'Red',
       b_content   => UTL_RAW.cast_to_raw ('{"thing":"blood"}'))) = 1;
  l_status :=
    l_collection.insert_one (
      soda_document_t (
        'Green',
        b_content   => UTL_RAW.cast_to_raw ('{"thing":"grass"}'))) = 1;

  l_keys := soda_key_list_t ('Green');

  l_cursor := l_collection.find ().keys (l_keys).get_cursor ();

  WHILE l_cursor.has_next
  LOOP
    l_document := l_cursor.NEXT;

    IF l_document IS NOT NULL
    THEN
      DBMS_OUTPUT.put_line ('Key: ' || l_document.get_key);
      DBMS_OUTPUT.put_line (
         json_query (l_document.get_blob, '$' PRETTY));
    END IF;
  END LOOP;

  l_status := l_cursor.close;
END;

And I see this output:

Key: Green

{

"thing" : "grass"

}

Replacing Documents

Suppose I want to change a document in my collection. Currently I will need to replace it with another. So I first create or receive the document; find the document, using its key; and use replace_one or replace_one_and_get.

In the block below, I use the same ColorKeys collection created in the previous section and replace the Red document with a Blue document.

DECLARE
  l_collection   SODA_COLLECTION_T;
  l_document     SODA_DOCUMENT_T;
  l_new_document SODA_DOCUMENT_T;
BEGIN
  l_collection := DBMS_SODA.open_collection('ColorKeys');
  l_document := SODA_DOCUMENT_T(
               b_content => utl_raw.cast_to_raw('{"Blue" : "Bluebird"}'));
  l_new_document := l_collection.find().key('Red').replace_one_and_get(l_document);

  IF l_new_document IS NOT NULL
  THEN
     DBMS_OUTPUT.put_line('Document:');
     DBMS_OUTPUT.put_line('Key: ' || l_new_document.get_key);
  END IF;

  COMMIT;
END;

If I do not need the result document (the document returned by and_get methods), I can call the replace_one method. That replaces the specified document and returns a status value: 1 if the document was replaced, 0 otherwise.

Removing Documents

My previous article on SODA showed you how to remove a single document. You can now use a remove method in SODA_OPERATION_T to remove one or many documents, once you have found, via key or filter, the document or documents you wish to remove. Here is an example of single-document removal:

DECLARE
  l_collection  SODA_COLLECTION_T;
  l_status NUMBER;
BEGIN
  l_collection := DBMS_SODA.open_collection('ColorKeys');

  l_status:= l_collection.find().key('Green').remove;

  IF  l_status = 1
  THEN
     DBMS_OUTPUT.put_line('Document removed');
  END IF;
END;

And in the block below, I use query-by-example to remove all documents from FriendsWithDocs whose friend_type is equal to 2.

DECLARE
  l_collection  SODA_COLLECTION_T;
  l_status NUMBER;
BEGIN
  l_collection := DBMS_SODA.open_collection('FriendsWithDocs');

  l_status:= l_collection.find().filter('{"friend_type" : "2"}').remove();

  IF  l_status = 1
  THEN
     DBMS_OUTPUT.put_line(Friend type 2 documents removed');
  END IF;
END;

Pagination Features

Suppose your collection has 1,000,000 documents and you want to work with batches of 100 documents at a time. SODA_COLLECTION_T offers the () and limit() nonterminal methods to help you get the job done.

In the following block, I create a collection and stuff it full of 100 very simple documents. I then use find(), skip(), and limit() to skip the first 50 documents and get just the next 5.

DECLARE
  l_collection   soda_collection_t;
  l_document     soda_document_t;
  l_cursor       soda_cursor_t;
  l_status       BOOLEAN;
BEGIN
  l_collection := dbms_soda.create_collection ('SillyCollection');

  FOR indx IN 1 .. 100
  LOOP
    l_status :=
      l_collection.insert_one (
        soda_document_t (
          b_content   => UTL_RAW.cast_to_raw (
                             '{"myIndex":'
                          || indx
                          || ',"myValue":"'
                          || CASE MOD (indx, 2)
                               WHEN 0 THEN 'Even'
                             ELSE 'Odd'
                             END
                          || '"}'))) = 1;
  END LOOP;

  l_cursor := l_collection.find ().skip (50).LIMIT (5).get_cursor;

  WHILE l_cursor.has_next
  LOOP

    l_document := l_cursor.NEXT;

    IF l_document IS NOT NULL
    THEN
      DBMS_OUTPUT.put_line (
        'Content: ' || json_query (l_document.get_blob, '$' PRETTY));
    END IF;
  END LOOP;

  l_status := l_cursor.close;
END;

Here’s my output:

Content: { "myIndex" : 89, "myValue" : "Odd" }
Content: { "myIndex" : 48, "myValue" : "Even" }
Content: { "myIndex" : 50, "myValue" : "Even" }
Content: { "myIndex" : 2, "myValue" : "Even" }
Content: { "myIndex" : 60, "myValue" : "Even" }

But wait! Why don’t I see 51 through 55? That’s because results are ordered only by the document key values (which are by default generated automatically) unless skip() or limit() appears in method calls.

If you need to change the ordering, use the filter method and specify an orderby. In the block below, I order by the myIndex value. I specify that the datatype is a number to ensure the correct ordering (it will otherwise be treated as a string).

DECLARE
  l_collection   soda_collection_t;
  l_document     soda_document_t;
  l_cursor       soda_cursor_t;
  l_status       BOOLEAN;
BEGIN
  l_collection := dbms_soda.open_collection ('SillyCollection');

  l_cursor := l_collection. find(). filter('{"$orderby" : [ { "path" : "myIndex", "datatype" : "number", "order" : "asc"}]}').
    skip(50).limit(5).get_cursor();

  WHILE l_cursor.has_next
  LOOP
    l_document := l_cursor.NEXT;

    IF l_document IS NOT NULL
     THEN
       DBMS_OUTPUT.put_line (
         'Content: ' || json_query (l_document.get_blob, '$' PRETTY));
    END IF;
  END LOOP;


  l_status := l_cursor.close;
END;

And I now see the following output:

Content: { "myIndex" : 51, "myValue" : "Odd" }
Content: { "myIndex" : 52, "myValue" : "Even" }
Content: { "myIndex" : 53, "myValue" : "Odd" }
Content: { "myIndex" : 54, "myValue" : "Even" }
Content: { "myIndex" : 55, "myValue" : "Odd" }

One thing to keep in mind when you are doing pagination and sorting: The process can be slow if you have lots of documents and you paginate through all of them. Whenever possible, try to restrict the set of documents in advance. Do this with a more complex query-by-example specification, such as

qbe = '{"$query" : {"salary" : {"$between" : [10000, 20000]}},
"$orderby" : {"someField" : 1}}';

Powerful Document Tools

SODA for PL/SQL will probably never be a “mainstream” feature set for Oracle Database developers. The majority of our work is likely to remain centered on traditional, extremely flexible, proven relational database objects, such as tables and views.

But as more and more UI developers, used to working with JavaScript and document databases, learn that they can also get the job done with Oracle Database, demand for these features and the skills to use them properly will increase.

Next Steps

READ “SODA and PL/SQL.”

READ an introduction to SODA.

LEARN more about SODA for PL/SQL.

WORK with the LiveSQL script for this article.

Illustration by Wes Rowell