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.

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

Comments

Popular posts from this blog

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 p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel