SODA API for PL/SQL

View All Scripts Login to Run Script

Script Name SODA API for PL/SQL
Description With the release of 18c, Simple Oracle Document Access (SODA) APIs have been extended to PL/SQL. This means that you can now store and manipulate JSON documents (and more) without knowing any SQL! OK, you might be wondering, why would I want to do that? Well, most database developers will NOT. But if you have frontend developers clamoring for permission to install a document database so they can work with JSON and not have to wrap their heads around SQL, you can tell them: "No need for another database. Oracle can handle this just fine!" Start here to go exploring: https://docs.oracle.com/en/database/oracle/oracle-database/18/adsdp/using-soda-pl-sql.html
Area PL/SQL General
Contributor Steven Feuerstein
Created Thursday January 03, 2019

Statement 1

I use the DBMS_SODA package to create a collection. Once I have the object type instance, I use its methods (get_name and get_metadata) to display info about the collection just created.

Create a New Collection

DECLARE 
   l_collection   soda_collection_t; 
BEGIN 
   l_collection := dbms_soda.create_collection ('MyFriends'); 
 
   DBMS_OUTPUT.put_line ('MyFriends ID = ' || l_collection.get_name ()); 
 
   DBMS_OUTPUT.put_line ( 
      'MyFriends JSON metadata = ' || l_collection.get_metadata ()); 
END;

Statement processed.
MyFriends ID = MyFriends
MyFriends JSON metadata = {"schemaName":"SQL_WRFVNIXIJGEXFGHXVRIDFFWFS","tableName":"MyFriends","keyColumn":{"name":"ID","sqlType":"VARCHAR2","maxLength":255,"assignmentMethod":"UUID"},"contentColumn":{"name":"JSON_DOCUMENT","sqlType":"BLOB","compress":"NONE","cache":true,"encrypt":"NONE","validation":"STANDARD"},"lastModifiedColumn":{"name":"LAST_MODIFIED"},"versionColumn":{"name":"VERSION","method":"SHA256"},"creationTimeColumn":{"name":"CREATED_ON"},"readOnly":false}

Statement 2

Here's a procedure that calls the list_collec†ion_names function to retrieve and then display the names of all SODA collections defined in my schema.

List All Collections

CREATE OR REPLACE PROCEDURE show_soda_collections (title_in IN VARCHAR2) 
IS 
   l_collections   soda_collname_list_t := dbms_soda.list_collection_names; 
BEGIN 
   DBMS_OUTPUT.put_line (title_in); 
 
   FOR indx IN 1 .. l_collections.COUNT 
   LOOP 
      DBMS_OUTPUT.put_line ( 
         'Collection ' || indx || ' is named ' || l_collections (indx)); 
   END LOOP; 
END;

Procedure created.

Statement 3

This utility procedure displays the names of all SODA collections in my schema, and then relies on the fact that there is a persistent relational table of the same name holding all the documents in that collection to get the count of documents in each collection.

How Many Docs In My Collections?

CREATE OR REPLACE PROCEDURE show_soda_collection_count 
IS 
   e_no_such_table   EXCEPTION; 
   PRAGMA EXCEPTION_INIT (e_no_such_table, -942); 
 
   l_collections     soda_collname_list_t := dbms_soda.list_collection_names; 
   l_count           INTEGER; 
BEGIN 
   DBMS_OUTPUT.put_line ('SODA Collection Count = ' || l_collections.COUNT); 
 
   FOR indx IN 1 .. l_collections.COUNT 
   LOOP 
      BEGIN 
         EXECUTE IMMEDIATE 
            'SELECT COUNT(*) FROM "' || l_collections (indx) || '"' 
            INTO l_count; 
 
         DBMS_OUTPUT.put_line ( 
               l_collections (indx) 
            || ' contains ' 
            || l_count 
            || ' document' 
            || CASE WHEN l_count <> 1 THEN 's' END); 
      EXCEPTION 
         WHEN e_no_such_table 
         THEN 
            DBMS_OUTPUT.put_line ( 
                  '* Error: underlying table not found for ' 
               || l_collections (indx)); 
      END; 
   END LOOP; 
END;

Procedure created.

Statement 4

I create a few collections, display all existing collections, then drop a collection and show the newly reduced list.

Drop a Collection

DECLARE 
   l_collection   soda_collection_t; 
BEGIN 
   l_collection := dbms_soda.create_collection ('Coll1'); 
 
   l_collection := dbms_soda.create_collection ('Coll2'); 
 
   l_collection := dbms_soda.open_collection ('Coll3'); 
 
   IF l_collection IS NULL 
   THEN 
      DBMS_OUTPUT.put_line ('No collection named "Coll3"'); 
   END IF; 
 
   show_soda_collections (title_in => 'Before Drop'); 
 
   IF dbms_soda.drop_collection ('Coll1') = 1 
   THEN 
      show_soda_collections (title_in => 'After Drop'); 
   END IF; 
END;

Statement processed.
No collection named "Coll3"
Before Drop
Collection 1 is named Coll1
Collection 2 is named Coll2
Collection 3 is named MyFriends
After Drop
Collection 1 is named Coll2
Collection 2 is named MyFriends

Statement 5

This block demonstrates the use of insert_one and insert_one_and_get to insert documents into a collection. It also uses methods of the soda_document_t type to get/display the key and media type of a document.

Insert Documents into a Collection

DECLARE 
   l_collection     soda_collection_t; 
   l_document       soda_document_t; 
   l_new_document   soda_document_t; 
BEGIN 
   l_collection := dbms_soda.create_collection ('FriendsWithDocuments'); 
 
   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;

Statement processed.
BLOB document inserted
Samuel's key: C4B8146FE2A34F7FBF1A08E7C3E0C023
Samuel's media_type: application/json

Statement 6

By default, a collection's document type is BLOB. So when I try to insert a document with CLOB content ("c_content" parameter), the insert fails.

Document Type Must Match Collection

DECLARE 
   l_collection   soda_collection_t; 
   l_document     soda_document_t; 
BEGIN 
   l_collection := dbms_soda.create_collection ('CLOBFriends'); 
 
   IF l_collection.insert_one ( 
         soda_document_t ( 
            c_content   => '{"friend_type":1,"friend_name":"Lakshmi"}')) = 1 
   THEN 
      DBMS_OUTPUT.put_line ('CLOB document inserted'); 
   END IF; 
END;

ORA-40659: Data type does not match the specification in the collection metadata. ORA-06512: at "SYS.SODA_COLLECTION_T", line 114
ORA-06512: at line 9
ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-40659

Statement 7

If you want your collections to hold documents of types other than BLOB, then specify alternative metadata when you create the collection. Here's an example.

Modify Collection Metadata

DECLARE 
  c_use_clob constant   VARCHAR2(1000) :=  
       '{"keyColumn":{"assignmentMethod": "CLIENT"}, 
        "contentColumn":{"sqlType": "CLOB"}}'; 
  l_collection  soda_collection_t; 
BEGIN 
  l_collection := DBMS_SODA.create_collection('CLOBFriends2', c_use_clob); 
END;

Statement processed.

Statement 8

In this block, I take the following steps: • Create a collection. • Insert a document and get back the metadata. • Use the find_one method to verify it is there. • Invoke the remove_one method to remove the document. • Call find_one again to confirm it is gone.

Remove a Document

DECLARE 
   l_collection       soda_collection_t; 
   l_new_document     soda_document_t; 
   l_found_document   soda_document_t; 
BEGIN 
   l_collection := dbms_soda.create_collection ('MyFriends'); 
 
   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"}'))); 
 
   /* Verify it's there using the find_one method and the key. */ 
   l_found_document := l_collection.find_one (l_new_document.get_key); 
 
   IF l_found_document IS NOT NULL 
   THEN 
      DBMS_OUTPUT.put_line ( 
         'Found document with key: ' || l_new_document.get_key); 
   END IF; 
 
   IF l_collection.remove_one (l_new_document.get_key) = 1 
   THEN 
      DBMS_OUTPUT.put_line ('Document is removed.'); 
   END IF; 
 
   /* Verify it's there using the find_one method and the key. */ 
   l_found_document := l_collection.find_one (l_new_document.get_key); 
 
   IF l_found_document IS NULL 
   THEN 
      DBMS_OUTPUT.put_line ('No document for key.'); 
   END IF; 
END;

Statement processed.
Found document with key: CF2D1B4D14074F55BF3FE2E9604C5EA8
Document is removed.
No document for key.

Statement 9

In this block, I take the following steps: • Insert a document with a friend named “Helen”. • Call the find_one method to retrieve the document from the collection, with its content. • Display that JSON – verifying the name as “Helen”. • Create a new document with the name “Helena”. • Call the replace_one_and_get method specifying the key of the original document, passing it the new document for replacement. • Verify that the name has been changed to “Helena”.

Replace a Document

DECLARE 
   l_collection          soda_collection_t; 
   l_original_document   soda_document_t; 
   l_new_document        soda_document_t; 
   l_replaced_document   soda_document_t; 
BEGIN 
   l_collection := dbms_soda.create_collection ('MyFriends2'); 
 
   l_original_document := 
      l_collection.insert_one_and_get ( 
         soda_document_t ( 
            b_content   => UTL_RAW.cast_to_raw ( 
                             '{"friend_type":3,"friend_name":"Helen"}'))); 
 
   /* Show the content */ 
   l_original_document := l_collection.find_one (l_original_document.get_key); 
    
   DBMS_OUTPUT.put_line ( 
         'Original content: ' 
      || UTL_RAW.cast_to_varchar2 (l_original_document.get_blob)); 
 
   /* Create a document (not inserted into any collection) with 
      the corrected name "Helena". */ 
   l_new_document := 
      soda_document_t ( 
         b_content   => UTL_RAW.cast_to_raw ( 
                          '{"friend_type":3,"friend_name":"Helena"}')); 
 
   /* Replace the original document and verify the contents */ 
 
   l_replaced_document := 
      l_collection.replace_one_and_get (l_original_document.get_key, 
                                        l_new_document); 
   l_replaced_document := l_collection.find_one (l_replaced_document.get_key); 
    
   DBMS_OUTPUT.put_line ( 
         'Replaced content: ' 
      || UTL_RAW.cast_to_varchar2 (l_replaced_document.get_blob)); 
END;

Statement processed.
Original content: {"friend_type":3,"friend_name":"Helen"}
Replaced content: {"friend_type":3,"friend_name":"Helena"}

Statement 10

DECLARE 
   l_collection soda_collection_t; 
BEGIN 
   l_collection := dbms_soda.create_collection ('SODACollection'); 
END;

Statement processed.

Statement 11
When you create a collection, a table of the same name is created (with case preserved).
Each Collection Has Its Own Table
```
SELECT table_name FROM USER_TABLES 
 WHERE table_name like 'SODA%'
```
```
TABLE_NAME
SODACollection
```

TABLE_NAME
SODACollection

Statement 12

EXEC show_soda_collection_count

Statement processed.
SODA Collection Count = 7
CLOBFriends contains 0 documents
CLOBFriends2 contains 0 documents
Coll2 contains 0 documents
FriendsWithDocuments contains 2 documents
MyFriends contains 0 documents
MyFriends2 contains 1 document
SODACollection contains 0 documents

Statement 13

Each document is stored as a row in the collection’s table. You don't manage this data directly. It is done through the SODA API.

A Row for Each Document

DECLARE 
   l_collection soda_collection_t; 
   l_document   soda_document_t; 
BEGIN 
   l_collection := dbms_soda.create_collection ('MyFriends'); 
 
   l_document := 
      l_collection.insert_one_and_get ( 
         soda_document_t ( 
            b_content   => UTL_RAW.cast_to_raw ( 
                             '{"friend_type":3,"friend_name":"Helen"}'))); 
END;

Statement processed.

Statement 14

SELECT id FROM "MyFriends"


ID
4999CF30DB0C4F6DBFB174A75BDF5CDF

ID
4999CF30DB0C4F6DBFB174A75BDF5CDF

Statement 15

EXEC show_soda_collection_count

Statement processed.
SODA Collection Count = 7
CLOBFriends contains 0 documents
CLOBFriends2 contains 0 documents
Coll2 contains 0 documents
FriendsWithDocuments contains 2 documents
MyFriends contains 1 document
MyFriends2 contains 1 document
SODACollection contains 0 documents

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database