Use PL/SQL to Build and Access Document Stores

What does soda have to do with PL/SQL and Oracle Database? Not much...but SODA Ah, there we have a different story to tell.

SODA stands for "Simple Oracle Document Access." It's a "set of NoSQL-style APIs that let you create and store collections of documents (most importantly JSON) in Oracle Database, retrieve them, and query them, without needing to know SQL or how the documents are stored in the database." Read lots more about SODA here.

As of Oracle Database 18c, we offer SODA APIs for Java, C, Node.js (JavaScript), Python, REST, and PL/SQL.

I published an article on SODA for PL/SQL in Oracle Magazine; in this blog post, I focus on some highlights. Also, Tim Hall of Oracle-BASE offers his usual outstanding treatment of this topic here.

SODA for PL/SQL? Whatever For?

First and most importantly, why would a database developer who writes PL/SQL want to avoid SQL and pretend that the relational Oracle Database is a document store?

Most backend 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 a means to implement business logic.

In large enterprises that have Oracle Database installed, however, there is an increasing demand from front-end (and/or full stack) developers 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 a 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 API to load JSON documents into the database. The SQL-savvy backend developer could then bring the full power of SQL to that data: indexing access to the documents and building efficient analytic queries against them.

Getting Started With SODA

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

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, etc.), or you can create collections for different components of your application.

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

All PL/SQL SODA operations are made available through the new-to-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, the SODA_APP role will need to be granted to that schema.

That's all you need to get going to SODA in PL/SQL!
I show below an example of using elements of the API.

I declare several variables based on object types defined for the SODA API. I use the DBMS_SODA package to create a new collection (which holds one or more documents).

Then I use the insert_one_and_get method of the soda_collection_t type to insert a document, which is built using the constructor function of the soda_document_t type.

I then obtain the key value of that document, along with its media type, using methods of the soda_document_t type.

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

There's lots more to explore, and I will be exploring in the coming months. Let me know your thoughts in the comments.