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), 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. 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!
Labels:

Comments

Post a Comment

Popular posts from this blog

Running out of PGA memory with MULTISET ops? Watch out for DISTINCT!

A PL/SQL team inside Oracle made excellent use of nested tables and MULTISET operators in SQL, blending data in tables with procedurally-generated datasets (nested tables).  All was going well when they hit the dreaded: ORA-04030: out of process memory when trying to allocate 2032 bytes  They asked for my help.  The error occurred on this SELECT: SELECT  *    FROM header_tab trx    WHERE (generated_ntab1 SUBMULTISET OF trx.column_ntab)       AND ((trx.column_ntab MULTISET             EXCEPT DISTINCT generated_ntab2) IS EMPTY) The problem is clearly related to the use of those nested tables. Now, there was clearly sufficient PGA for the nested tables themselves. So the problem was in executing the MULTISET-related functionality. We talked for a bit about dropping the use of nested tables and instead doing everything in SQL, to avoid the PGA error. That would, however require lots of wo...
4 comments
Read more

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today: In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer Quick Answer Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption. Test to see if that's fast enough (likely will be for many cases). If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory.  Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification. Don't put anything in the collection you don't need. [from Giulio Dottorini] Remember: each session that runs this code will use that amount of memory. Background When you use BULK COLLECT, you retrieve more than row with each fetch, ...
4 comments
Read more

PL/SQL 101: Three ways to get error message/stack in PL/SQL

The PL/SQL Challenge quiz for 10 September - 16 September 2016 explored the different ways you can obtain the error message / stack in PL/SQL. Note: an error stack is a sequence of multiple error messages that can occur when an exception is propagated and re-raised through several layers of nested blocks. The three ways are: SQLERRM - The original, traditional and (oddly enough) not currently recommended function to get the current error message. Not recommended because the next two options avoid a problem which you are unlikely  to run into: the error stack will be truncated at 512 bytes, and you might lose some error information. DBMS_UTILITY.FORMAT_ERROR_STACK - Returns the error message / stack, and will not truncate your string like SQLERRM will. UTL_CALL_STACK API - Added in Oracle Database 12c, the UTL_CALL_STACK package offers a comprehensive API into the execution call stack, the error stack and the error backtrace.  Note: check out this LiveSQL script if...
5 comments
Read more