Skip to main content

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), PythonREST and PL/SQL.

I published an article on SODA for PL/SQL in Oracle Magazine; in this blog post, I focus on some highlights. Please do read the full article (and others still to come!). Also, Tim Hall of Oracle-BASE offers his usual outstanding treatment of this topic here.

SODA for PL/SQL? Whatever for?

First and most important, why would a database developer who writes PL/SQL want to avoid SQL and pretend that the amazing 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, 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 frontend (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 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 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.

   l_collection     soda_collection_t;
   l_document       soda_document_t;
   l_new_document   soda_document_t;
   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
      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 (

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

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. In the meantime, check out my Oracle magazine article and give it a try!


Popular posts from this blog

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.

How to Get a Mutating Table Error

I need to implement this rule on my employees table:
Your new salary cannot be more than 25x th…

Table Functions, Part 1: Introduction and Exploration

August 2018 update: please do feel encourage to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done!

Table functions - functions that can be called in the FROM clause of a query from inside the TABLE operator - are fascinating and incredibly helpful constructs.

So I've decided to write a series of blog posts on them: how to build them, how to use them, issues you might run into.

Of course, I am not the first to do so. I encourage to check out the documentation, as well as excellent posts from Adrian Billington (search for "table functions") and Tim Hall. Adrian and Tim mostly focus on pipelined table functions, a specialized variant of table functions designed to improve performance and reduce PGA consumption. I will take a look at pipelined table functions in the latte…

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post.

PL/SQL is a strongly-typed language. Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type).

PL/SQL offers a wide array of pre-defined data types, both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package).

Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types.

You can't really declare your own "user-defined" scalars, though you can define subtypes from those scalars, which can be very helpful from the perspective…