SODA for PL/SQL

View All Tutorials Login to Run Tutorial

Tutorial SODA for PL/SQL
Description SODA for PL/SQL is a PL/SQL API that implements Simple Oracle Document Access (SODA). You can use it with PL/SQL to perform create, read (retrieve), update, and delete (CRUD) operations on documents of any kind, and you can use it to query JSON documents. SODA is a set of NoSQL-style APIs that let you create and store collections of documents in Oracle Database, retrieve them, and query them, without needing to know Structured Query Language (SQL) or how the data in the documents is stored in the database. Oracle Database supports storing and querying JSON data. To access this functionality, you need structured query language (SQL) with special JSON SQL operators. SODA for PL/SQL hides the complexities of SQL/JSON programming.
Tags SODA JSON NOSQL SQL
Area SQL General / JSON
Contributor Oracle
Created Saturday August 24, 2019
Modules 34

Module 1

Setup - 1



REM Create demo package specification
CREATE OR REPLACE PACKAGE oow_soda
AUTHID CURRENT_USER
AS
    -- Constants
    SHOW_ALL               CONSTANT NUMBER(2) := 1;
    SHOW_KEY_ONLY          CONSTANT NUMBER(2) := 2;
    SHOW_NO_CONTENT        CONSTANT NUMBER(2) := 3;
    SHOW_KEY_VERSION_ONLY  CONSTANT NUMBER(2) := 4;

    SHOW_COLL_NAME_ONLY    CONSTANT NUMBER(2) := 2;

    -- Show collection components
    PROCEDURE show(collection  IN  SODA_Collection_T,
                   setting   IN  NUMBER DEFAULT oow_soda.SHOW_ALL,
                   comments    IN  VARCHAR2 DEFAULT NULL);

    -- Show document components
    PROCEDURE show(document  IN  SODA_Document_T,
                   setting   IN  NUMBER DEFAULT oow_soda.SHOW_ALL,
                   comments  IN  VARCHAR2 DEFAULT NULL);

    -- Generate JSON content given a number
    PROCEDURE get_doc(docNum      IN   NUMBER, 
                      docContent  OUT  VARCHAR2);

    -- Load nDocs into collection with a given sql type for doc content.
    -- Assumes collection key is server-assigned.
    -- Inserts all nDocs and commits autonomously on success, rollsback
    -- on failure, returning 0 nDocs.
    PROCEDURE load_docs(collection  IN      SODA_Collection_T,
                        nDocs       IN OUT  NUMBER,
                        dType       IN      NUMBER DEFAULT DBMS_SODA.DOC_BLOB);

    -- Convert VARCHAR2 to BLOB
    FUNCTION to_blob (v in varchar2) return BLOB;

    -- Get Metadata for given collection type
    FUNCTION get_Metadata(dType IN  NUMBER DEFAULT DBMS_SODA.DOC_BLOB)
    return VARCHAR2;

END oow_soda;

Module 2

Setup - 2


REM Creating a test package body for SODA

CREATE OR REPLACE PACKAGE BODY oow_soda AS

    PROCEDURE show(collection  IN  SODA_Collection_T,
                   setting   IN  NUMBER DEFAULT oow_soda.SHOW_ALL,
                   comments  IN  VARCHAR2 DEFAULT NULL)
    IS
    BEGIN
        IF comments IS NOT NULL
        THEN
            dbms_output.put_line(comments || ':');
        END IF;

        IF collection IS NULL
        THEN
            dbms_output.put_line('Collection is null');
            RETURN;
        END IF;
        dbms_output.put_line('Collection name: ' || collection.get_Name);
        IF setting = oow_soda.SHOW_COLL_NAME_ONLY
        THEN
          RETURN;
        END IF;
        dbms_output.put_line('Collection descriptor: ' ||
                             JSON_QUERY(collection.get_Metadata, '$' pretty));
    END;

    PROCEDURE show(document  IN  SODA_Document_T,
                   setting   IN  NUMBER   DEFAULT oow_soda.SHOW_ALL,
                   comments  IN  VARCHAR2 DEFAULT NULL)
    IS
    BEGIN
        IF comments IS NOT NULL
        THEN
            dbms_output.put_line(comments || ':');
        END IF;
        IF document IS NULL
        THEN
            dbms_output.put_line('Document is null');
            RETURN;
        END IF;
        dbms_output.put_line('Document key: ' || document.get_Key);
        IF setting = oow_soda.SHOW_KEY_ONLY
        THEN
          RETURN;
        END IF;
        dbms_output.put_line('Document version: ' || document.get_Version);
        IF setting = oow_soda.SHOW_KEY_VERSION_ONLY
        THEN
          RETURN;
        END IF;
        IF setting <> oow_soda.SHOW_NO_CONTENT
        THEN
        dbms_output.put_line('Document content: ' || 
           CASE(document.get_Data_Type)
               WHEN DBMS_SODA.DOC_VARCHAR2 THEN JSON_QUERY(document.get_Varchar2, '$' pretty)
               WHEN DBMS_SODA.DOC_BLOB THEN JSON_QUERY(document.get_Blob, '$' pretty)
               WHEN DBMS_SODA.DOC_CLOB THEN JSON_QUERY(document.get_Clob, '$' pretty)
           END);
        END IF;        
        dbms_output.put_line('Document data type: ' || 
                               CASE(document.get_Data_Type)
                                 WHEN DBMS_SODA.DOC_VARCHAR2 THEN 'VARCHAR2'
                                 WHEN DBMS_SODA.DOC_BLOB THEN 'BLOB'
                                 WHEN DBMS_SODA.DOC_CLOB THEN 'CLOB'
                               END);
        dbms_output.put_line('Document created on: ' || 
                              document.get_Created_On);
        dbms_output.put_line('Document last modified: ' ||
                              document.get_Last_Modified);
    END;
                   
    PROCEDURE get_doc(docNum      IN   NUMBER, 
                      docContent  OUT  VARCHAR2)
    IS
        TYPE oow_list IS TABLE OF VARCHAR2(30);
        i           NUMBER := docnum;
        str         VARCHAR2(1000);
        first_names oow_list := oow_list
            ('Joseph','Robert','William','Edward','Sally',
             'John','Linda', 'Samuel','Melissa','Joan','Ann');
        last_names  oow_list := oow_list
            ('Smith','Jones','White','Black','Robinson');
        companies   oow_list := oow_list
            ('Oracle','Microsoft','Google','IBM','SAP',
             'Salesforce','Facebook');
        titles      oow_list := oow_list
            ('Manager','Group Manager','Director','Vice President');
        orgs        oow_list := oow_list
            ('Development','Consulting','Sales');
        mails       oow_list := oow_list
            ('yahoo.com','gmail.com');
        cities      oow_list := oow_list
            ('New York','Los Angeles','Boston','San Francisco',
             'Washington','Chicago','Atlanta','Dallas','Seattle');
        locations   oow_list := oow_list
            ('[74.0059,40.7127]','[118.2500,34.0500]','[71.0589,42.3601]',
             '[122.4167,37.7833]','[77.0164,38.9047]','[87.6847,41.8369]',
             '[84.3900,33.7550]','[96.7970,32.7767]','[122.3331,47.6097]');
        states      oow_list := oow_list
            ('NY','CA','MA','CA','DC','IL','GA','TX','WA');    
        streets     oow_list := oow_list
            ('1313 Mockingbird Lane','123 Main Street',
             '1600 Pennsylvania Ave');
        first_name  VARCHAR2(100);
        last_name   VARCHAR2(100);
        salary      NUMBER;
    BEGIN
        first_name := first_names(mod(i, first_names.COUNT)+1);
        last_name := last_names(mod(i, last_names.COUNT)+1);
        salary := 100000 + i; 
        salary := salary + (mod(i, titles.COUNT) * 20000);
        salary := salary + (mod(i, companies.COUNT) * 5000);
        salary := salary + (mod(i, orgs.COUNT) * 50000);

        str := str || '{ "empno" : ' || (i+10000);
        str := str || ', "name" : "';
        str := str || first_name || ' ' || last_name || '"';
        str := str || ', "email" : ["';
        str := str || first_name || '_' || last_name || '@';
        str := str || mails(mod(i, mails.COUNT)+1) || '"';
        str := str || ', "';
        str := str || first_name || '_' || last_name || '@icloud.com"]';
        str := str || ', "location" : {"type": "Point", "coordinates": ';
        str := str || locations(mod(i, locations.COUNT)+1);
        str := str || '}';
        str := str || ', "address" : {';
        str := str || ' "street" : "';
        str := str || streets(mod(i, streets.COUNT)+1);
        str := str || '", "city" : "';
        str := str || cities(mod(i, cities.COUNT)+1);
        str := str || '", "state" : "';
        str := str || states(mod(i, states.COUNT)+1);
        str := str || '"},';
        str := str || '"title" : "';
        str := str || titles(mod(i, titles.COUNT)+1);
        str := str || '", "department" : "';
        str := str || orgs(mod(i, orgs.COUNT)+1);
        str := str || '", "company" : "';
        str := str || companies(mod(i, companies.COUNT)+1);
        str := str || '", "spouse" : null ';
        str := str || ', "salary" : ' || salary;
        str := str || ' }';
        docContent := str;
    END;

    PROCEDURE load_docs(collection  IN      SODA_Collection_T,
                        nDocs       IN OUT  NUMBER,
                        dType       IN      NUMBER  DEFAULT DBMS_SODA.DOC_BLOB)
    IS
    PRAGMA AUTONOMOUS_TRANSACTION;
        docContent  VARCHAR2(1000);
        i           NUMBER;
        tot         NUMBER := nDocs;
        doc         SODA_Document_T;
        status      NUMBER;
    BEGIN
        IF collection IS NULL
        THEN
            raise_application_error(-20000,
                                    'Collection cannot be null');
        END IF;

        FOR i in 1..tot
        LOOP
            oow_soda.get_doc(docnum     => i, 
                              docContent => docContent);
            CASE(dType)
                WHEN DBMS_SODA.DOC_VARCHAR2 
                THEN
                    doc := SODA_Document_T(v_Content => docContent);
                WHEN DBMS_SODA.DOC_BLOB
                THEN
                    doc := SODA_Document_T(b_Content => 
                                               utl_raw.cast_to_raw(docContent));
                WHEN DBMS_SODA.DOC_CLOB
                THEN
                    doc := SODA_Document_T(c_Content => docContent);
            END CASE;

            status := collection.insert_One(doc);

            IF status = 1
            THEN
                nDocs := i;
            ELSE
                raise_application_error (-20000,
                                         'Error during insert:' || i);
            END IF;
        END LOOP;
        COMMIT;
    EXCEPTION
        WHEN OTHERS
        THEN
            dbms_output.put_line (SQLERRM);
            ROLLBACK;
            nDocs := 0;
    END;

    FUNCTION to_blob (v in varchar2) return BLOB
    IS
    BEGIN
        return UTL_RAW.CAST_TO_RAW(v);
    END;

    -- Get Metadata for given collection type
    FUNCTION get_Metadata(dType IN  NUMBER DEFAULT DBMS_SODA.DOC_BLOB)
    return VARCHAR2
    IS
      metadata VARCHAR2(4000);
    BEGIN
      CASE(dType)
          WHEN DBMS_SODA.DOC_VARCHAR2 
          THEN
              metadata :='{"contentColumn":{"name":"JSON_DOCUMENT","sqlType":"VARCHAR2"},"lastModifiedColumn":{"name":"LAST_MODIFIED"},"versionColumn":{"name":"VERSION","method" : "SHA256"},"creationTimeColumn":{"name":"CREATED_ON"}}';
          WHEN DBMS_SODA.DOC_BLOB
          THEN
              metadata := NULL;
          WHEN DBMS_SODA.DOC_CLOB
          THEN
              metadata :='{"contentColumn":{"name":"JSON_DOCUMENT","sqlType":"CLOB"},"lastModifiedColumn":{"name":"LAST_MODIFIED"},"versionColumn":{"name":"VERSION","method" : "SHA256"},"creationTimeColumn":{"name":"CREATED_ON"}}';
      END CASE;
      return metadata;
    END;

END oow_soda;

Module 3

Load documents


REM Load docs to a collection
DECLARE
    coll        SODA_Collection_T;
    tot         NUMBER := 1000;
BEGIN
    coll := dbms_soda.create_Collection('Employees');
    oow_soda.load_docs(collection  => coll,
                        nDocs       => tot);
    dbms_output.put_line('Docs loaded: '|| tot);
EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line (SQLERRM);
    ROLLBACK;
END;

Module 4
Creating a SODA collection
You can use PL/SQL function DBMS_SODA.create_collection to create a document collection with the default metadata. The default collection metadata has the following characteristics.
- Each document in the collection has these document components:
  
  Key
  
  Content
  
  Creation timestamp
  
  Last-modified timestamp
  
  Version
- The collection can store only JSON documents.
- Document keys are automatically generated for documents that you add to the collection.
```
REM Create collection with default metadata
DECLARE
    collection  SODA_Collection_T;
BEGIN
    collection := dbms_soda.create_Collection('myCollectionName');   
    dbms_output.put_Line('Name: ' || collection.get_Name);
    dbms_output.put_Line('Metadata: ' || 
                     JSON_QUERY(collection.get_Metadata, '$' pretty));
END;
```
The default collection configuration is recommended in most cases, but collections are highly configurable. When you create a collection you can specify things such as the following:
- Storage details, such as the name of the table that stores the collection and the names and data types of its columns.
- The presence or absence of columns for creation timestamp, last-modified timestamp, and version.
- Whether the collection can store only JSON documents.
- Methods of document key generation, and whether document keys are client-assigned or generated automatically.
- Methods of version generation.

Module 5

Opening a SODA collection

This example uses PL/SQL function DBMS_SODA.open_collection to open the collection named myCollectionName and returns a SODA_COLLECTION_T instance that represents this collection. If the value returned is NULL then there is no existing collection named myCollectionName.


REM Open an existing collection
DECLARE
    collection  SODA_Collection_T;
BEGIN
    collection := dbms_soda.open_Collection('myCollectionName');

    IF collection IS NULL THEN
        dbms_output.put_Line('Collection does not exist');
    ELSE
        dbms_output.put_Line('Name: ' || collection.get_Name);
        dbms_output.put_Line('Metadata: ' || 
                         JSON_QUERY(collection.get_Metadata, '$' pretty));
    END IF;
END;
/

Module 6

Discovering existing collections

This example uses PL/SQL function DBMS_SODA.list_collection_names to obtain a list of the collection names. It then iterates over that list, printing out the names.


REM Discovering existing collections
DECLARE
    coll_List  SODA_CollName_List_T;
BEGIN
    coll_List := dbms_soda.list_Collection_Names;
    dbms_output.put_Line('Number of collections: '||to_Char(coll_List.count));
    dbms_output.put_Line('Collection List: ');

    -- Loop over the collection name list
    IF (coll_List.count > 0) THEN
        FOR i IN
            coll_List.first .. coll_List.last
        LOOP
            dbms_output.put_Line('['||i||']: ' || coll_List(i));
        END LOOP;  
    ELSE   
        dbms_output.put_Line('No collections found');
    END IF;
END;

Module 7
Dropping a collection
This example uses PL/SQL function DBMS_SODA.drop_collection to drop collection myCollectionName. If the collection cannot be dropped because of uncommitted write operations then an exception is thrown. If the collection is dropped successfully, the returned status is 1; otherwise, the status is 0. In particular, if a collection with the specified name does not exist, the returned status is 0 — no exception is thrown.
```
REM Dropping a collection
DECLARE
    status  NUMBER := 0;
BEGIN
    status := dbms_soda.drop_Collection('myCollectionName');
    dbms_output.put_line('Status: ' || status);
    
    status := dbms_soda.drop_Collection('SODAPLSOPR1');
    dbms_output.put_line('Status: ' || status);
END;
```
Module 8
Dropping a collection using SQL
This example drops a collection using a SQL statement. The PL/SQL function dbms_soda.drop_collection returns a NUMBER type, 0 of the collection was not dropped, 1 if it was dropped.
```
SELECT dbms_soda.drop_Collection('myCollectionName') "Status" from dual;
```
Module 9
Creating a Document with JSON Content
This example uses SODA_DOCUMENT_T constructors to create three documents, one of each content type. The example provides only the document content (which is the same for each). The content parameter is different in each case; it specifies the SQL data type to use to store the content. The first document creation here uses content parameter v_content, which specifies VARCHAR2 content; the second uses parameter c_content, which specifies CLOB content; the third uses parameter b_content, which specifies BLOB content. After creating each document, the example uses getter methods to get the document content. Note that the getter method that is appropriate for each content storage type is used: get_blob() for BLOB content, and so on. The document with content type BLOB would be appropriate for writing to the collection created earlier, because that collection (which has the default metadata) accepts documents with (only) BLOB content. The other two documents would not be appropriate for that collection; trying to insert them would raise an error.
```
REM Creating a Document with JSON Content
DECLARE
    v_Doc  SODA_Document_T;
    b_Doc  SODA_Document_T;
    c_Doc  SODA_Document_T;
BEGIN
    -- Create VARCHAR2 document
    v_Doc := SODA_Document_T(v_Content => '{"name" : "Alexander"}');
    dbms_output.put_Line('Varchar2 Doc content: ' || v_Doc.get_Varchar2);
    
    -- Create BLOB document
    b_Doc := SODA_Document_T(b_Content => utl_raw.cast_to_raw('{"name" : "Alexander"}'));
    dbms_output.put_Line('Blob Doc content: ' || utl_raw.cast_to_varchar2(b_Doc.get_Blob));

    -- Create CLOB document
    c_Doc := SODA_Document_T(c_Content => '{"name" : "Alexander"}');
    dbms_output.put_Line('Clob Doc content: ' || c_Doc.get_Clob);
END;
```

Module 10

Creating a Document with key and JSON Content

This example is similar to the previous example, but it provides the document key (myKey) as well as the document content.


REM Creating a Document with key and JSON Content

DECLARE
    v_Doc  SODA_Document_T;
    b_Doc  SODA_Document_T;
    c_Doc  SODA_Document_T;
BEGIN
    -- Create VARCHAR2 document
    v_Doc := SODA_Document_T('myKey' , v_Content => '{"name" : "Alexander"}');
    dbms_output.put_Line('Varchar2 Doc key: ' || v_Doc.get_Key);
    dbms_output.put_Line('Varchar2 Doc content: ' || v_Doc.get_Varchar2);
        
    -- Create BLOB document
    b_Doc := SODA_Document_T('myKey' , b_Content => utl_raw.cast_to_raw('{"name" : "Alexander"}'));
    dbms_output.put_Line('Blob Doc key: ' || b_Doc.get_Key);
    dbms_output.put_Line('Blob Doc content: ' || utl_raw.cast_to_varchar2(b_Doc.get_Blob));
    
    -- Create CLOB document
    c_Doc := SODA_Document_T('myKey' , c_Content => '{"name" : "Alexander"}');
    dbms_output.put_Line('Clob Doc key: ' || c_Doc.get_Key);
    dbms_output.put_Line('Clob Doc content: ' || c_Doc.get_Clob);
END;

Module 11
Inserting a Document into a Collection
To insert a document into a collection, you invoke SODA_COLLECTION_T method (member function) insert_one() or insert_one_and_get(). These methods create document keys automatically, unless the collection is configured with client-assigned keys and the input document provides the key. Both method insert_one() and method insert_one_and_get() insert a document into a collection and automatically set the values of the creation time stamp, last-modified time stamp, and version (if the collection is configured to include these components and to generate the version automatically, as is the case by default). In addition to inserting the document, insert_one_and_get returns a result document, which contains the generated document components, such as the key, and which does not contain the content of the inserted document. This example creates a document and inserts it into a collection using SODA_COLLECTION_T method insert_one() . REM Inserting a Document into a Collection
```
DECLARE
    collection  SODA_COLLECTION_T;
    document    SODA_DOCUMENT_T;
    status      NUMBER;
BEGIN
    -- Open the collection
    collection := DBMS_SODA.create_collection('myCollectionName');
    document := SODA_DOCUMENT_T(
                  b_content => utl_raw.cast_to_raw('{"name" : "Alexander"}'));

    -- Insert a document
    status := collection.insert_one(document);
    dbms_output.put_line('Status: ' || status);
END;
```

Module 12

Inserting a Document into a Collection and Getting the Result Document

This example creates a document and inserts it into a collection using method insert_one_and_get(). It then gets (and prints) each of the generated components from the result document (which contains them). To obtain the components it uses SODA_DOCUMENT_T methods get_key(), get_created_on(), get_last_modified(), and get_version().


REM Inserting a Document into a Collection and Getting the Result Document
DECLARE
    collection  SODA_Collection_T;
    document    SODA_Document_T;
    ins_Doc     SODA_Document_T;
BEGIN
    -- Open the collection
    collection := dbms_soda.open_Collection('myCollectionName');
    document := SODA_Document_T(b_Content => utl_raw.cast_to_raw('{"name" : "Ballimer"}'));
    ins_Doc := collection.insert_One_And_Get(document);

    -- Insert the document and get its components
    IF ins_Doc IS NOT NULL THEN
        dbms_output.put_line('Inserted document components:');
        dbms_output.put_line('Key: ' || ins_Doc.get_Key);
        dbms_output.put_line('Creation timestamp: ' || ins_Doc.get_Created_On);
        dbms_output.put_line('Last modified timestamp: ' || ins_Doc.get_Last_Modified);
        dbms_output.put_line('Version: ' || ins_Doc.get_Version);
    END IF;
END;

Module 13

Replacing a Document in a Collection and getting the result Document

This example replaces a document in a collection, given its key. It then gets (and prints) the key and the generated components from the result document. To obtain the components it uses SODA_DOCUMENT_T methods get_key(), get_created_on(), get_last_modified(), and get_version().



REM Replacing a Document in a Collection and getting the result Document
DECLARE
    collection  SODA_Collection_T;
    document    SODA_Document_T;
    rep_Doc     SODA_Document_T;
    key         VARCHAR2(255);
BEGIN
    collection := dbms_soda.open_Collection('myCollectionName');
    document := SODA_Document_T(b_Content => utl_raw.cast_to_raw('{"name" : "Sriky"}'));
    select ID into key from "myCollectionName" where rownum < 2;

    rep_Doc := collection.replace_One_And_Get(key, document);

    IF rep_Doc IS NOT NULL THEN
        dbms_output.put_line('Document components:');
        dbms_output.put_line('Key: ' || rep_Doc.get_Key);
        dbms_output.put_line('Creation timestamp: ' || rep_Doc.get_Created_On);
        dbms_output.put_line('Last modified timestamp: ' || rep_Doc.get_Last_Modified);
        dbms_output.put_line('Version: ' || rep_Doc.get_Version);
    END IF;
END;

Module 14

Finding a unique Document that has a given key

The following example finds a unique document using a key



REM Finding a unique Document that has a given key

DECLARE
    collection  SODA_Collection_T;
    document    SODA_Document_T;
    key         VARCHAR2(255);
BEGIN
    -- Open the collection
    collection := dbms_soda.open_Collection('myCollectionName');

    SELECT ID into key from "myCollectionName" where rownum < 2;

    -- Find a document using a key
    document := collection.find_One(key);

    IF document IS NOT NULL THEN
        dbms_output.put_line('Document components:');
        dbms_output.put_line('Key: ' || document.get_Key);
        dbms_output.put_line('Content: ' || utl_raw.cast_to_varchar2(document.get_Blob));
        dbms_output.put_line('Creation timestamp: ' || document.get_Created_On);
        dbms_output.put_line('Last modified timestamp: ' || document.get_Last_Modified);
        dbms_output.put_line('Version: ' || document.get_Version);
    END IF;
END;

Module 15

Finding the top matching document

This example uses SODA_COLLECTION_T methods find(), key(), and get_one() to find the unique document whose key is "key1".


REM Finding the top matching document

DECLARE
    collection  SODA_COLLECTION_T;
    document    SODA_DOCUMENT_T;
    key         VARCHAR2(255);
BEGIN
    -- Open the collection
    collection := DBMS_SODA.open_collection('myCollectionName');

    SELECT ID into key from "myCollectionName" where rownum < 2;

    -- Find a document using a key
    document := collection.find().key(key).get_one;

    IF document IS NOT NULL THEN
        DBMS_OUTPUT.put_line('Document components:');
        DBMS_OUTPUT.put_line('Key: ' || document.get_key);
        DBMS_OUTPUT.put_line('Content: ' || JSON_QUERY(document.get_blob, '$' PRETTY));
        DBMS_OUTPUT.put_line('Creation timestamp: ' || document.get_created_on);
        DBMS_OUTPUT.put_line('Last modified timestamp: ' || document.get_last_modified);
        DBMS_OUTPUT.put_line('Version: ' || document.get_version);
    END IF;
END;

Module 16

Finding All Documents in a Collection

This example uses SODA_COLLECTION_T methods find() and getCursor() to obtain a cursor for a query result list that contains each document in a collection. It then uses the cursor in a WHILE statement to get and print the content of each document in the result list, as a string. Finally, it closes the cursor. It uses SODA_DOCUMENT_T methods get_key(), get_blob(), get_created_on(), get_last_modified(), and get_version(), to get the document components, which it prints. It passes the document content to SQL/JSON function json_query to pretty-print (using keyword PRETTY).


REM Finding All Documents in a Collection

DECLARE
    collection    SODA_Collection_T;
    document      SODA_Document_T;
    cur           SODA_Cursor_T;
    status        BOOLEAN;
BEGIN
    -- Open the collection to be queried
    collection := DBMS_SODA.open_collection('myCollectionName');

    -- Open the cursor to fetch the documents
    cur := collection.find().get_cursor();

    -- Loop through the cursor
    WHILE cur.has_Next
    LOOP
      document := cur.next;
      IF document IS NOT NULL THEN
          DBMS_OUTPUT.put_line('Document components:');
          DBMS_OUTPUT.put_line('Key: ' || document.get_key);
          DBMS_OUTPUT.put_line('Content: ' ||
                               JSON_QUERY(document.get_blob, '$' PRETTY));
          DBMS_OUTPUT.put_line('Creation timestamp: ' || 
                               document.get_created_on);
          DBMS_OUTPUT.put_line('Last modified timestamp: ' ||
                               document.get_last_modified);
          DBMS_OUTPUT.put_line('Version: ' || document.get_version);
      END IF;
    END LOOP;

    -- IMPORTANT: You must close the cursor to release resources!
    status := cur.close;
END;

Module 17

Finding Multiple Documents with Specified Document Keys

This example defines key list myKeys, with (string) keys "key1", "key2", and "key3". It then finds the documents that have those keys, and it prints their components. SODA_COLLECTION_T method keys() specifies the documents with the given keys.



REM  Finding Multiple Documents with Specified Document Keys

DECLARE
    collection  SODA_COLLECTION_T;
    document    SODA_DOCUMENT_T;
    cur         SODA_CURSOR_T;
    status      BOOLEAN;
    myKeys      SODA_KEY_LIST_T;
BEGIN
    -- Open the collection
    collection := DBMS_SODA.open_collection('myCollectionName');

    -- Set the keys list
    SELECT ID bulk collect into myKeys from "myCollectionName";

    -- Find documents using keys
    cur := collection.find().keys(myKeys).get_cursor;

    -- Loop through the cursor
    WHILE cur.has_next
    LOOP
      document := cur.next;
      IF document IS NOT NULL THEN
          DBMS_OUTPUT.put_line('Document components:');
          DBMS_OUTPUT.put_line('Key: ' || document.get_key);
          DBMS_OUTPUT.put_line('Content: ' || json_query(document.get_blob, '$' PRETTY));
          DBMS_OUTPUT.put_line('Creation timestamp: ' || document.get_created_on);
          DBMS_OUTPUT.put_line('Last modified timestamp: ' || document.get_last_modified);
          DBMS_OUTPUT.put_line('Version: ' || document.get_version);
      END IF;
    END LOOP;
    status := cur.close;
END;

Module 18

Finding Documents with a Filter Specification

SODA_COLLECTION_T method filter() provides a powerful way to filter JSON documents in a collection. Its parameter is a JSON query-by-example (QBE, also called a filter specification). The syntax of filter specifications is an expressive pattern-matching language for JSON documents. This example uses only a very simple QBE, just to indicate how you make use of one in SODA for PL/SQL. This example does the following:

Creates a filter specification that looks for all JSON documents whose name field has value "Alexander".
Uses the filter specification to find the matching documents.
Prints the components of each document.


REM Finding Documents with a Filter Specification

DECLARE
    collection  SODA_COLLECTION_T;
    document    SODA_DOCUMENT_T;
    cur         SODA_CURSOR_T;
    status      BOOLEAN;
    qbe         VARCHAR2(128);
BEGIN
    -- Open the collection
    collection := DBMS_SODA.open_collection('myCollectionName');

    -- Define the filter specification (QBE)
    qbe := '{"name" : "Alexander"}';

    -- Open a cursor for the filtered documents
    cur := collection.find().filter(qbe).get_cursor;

    -- Loop through the cursor
    WHILE cur.has_next
    LOOP
      document := cur.next;
      IF document IS NOT NULL THEN
          DBMS_OUTPUT.put_line('Document components:');
          DBMS_OUTPUT.put_line('Key: ' || document.get_key);
          DBMS_OUTPUT.put_line('Content: ' || JSON_QUERY(document.get_blob, '$' PRETTY));
          DBMS_OUTPUT.put_line('Creation timestamp: ' || document.get_created_on);
          DBMS_OUTPUT.put_line('Last modified timestamp: ' || document.get_last_modified);
          DBMS_OUTPUT.put_line('Version: ' || document.get_version);
      END IF;
    END LOOP;
    status := cur.close;
END;

Module 19

Specifying Document Version to search

This example uses SODA_COLLECTION_T method version() to specify the document version. This is useful for implementing optimistic locking, when used with the terminal methods for write operations. You typically use version() together with method key(), which specifies the document. You can also use version() with methods keyLike() and filter(), provided they identify at most one document.


REM Specifying Document Version to search

DECLARE
    collection  SODA_COLLECTION_T;
    document    SODA_DOCUMENT_T;
    k           varchar2(255);
    v           varchar2(255);
BEGIN
    -- Open the collection
    collection := DBMS_SODA.open_collection('myCollectionName');

    select ID, VERSION into k, v from "myCollectionName" where rownum < 2;

    -- Find a particular version of the document that has a given key
    document := collection.find().key(k).version(v).get_one;

    IF document IS NOT NULL THEN
        DBMS_OUTPUT.put_line('Document components:');
        DBMS_OUTPUT.put_line('Key: ' || document.get_key);
        DBMS_OUTPUT.put_line('Content: ' ||
                             JSON_QUERY(document.get_blob, '$' PRETTY));
        DBMS_OUTPUT.put_line('Creation timestamp: ' || document.get_created_on);
        DBMS_OUTPUT.put_line('Last modified timestamp: ' ||
                             document.get_last_modified);
        DBMS_OUTPUT.put_line('Version: ' || document.get_version);
    END IF;
END;

Module 20

Counting the Number of Documents Found

This example uses SODA_COLLECTION_T method count() to get a count of all of the documents in the collection. It then gets a count of all of the documents that are returned by a filter specification (QBE).


REM Counting the Number of Documents Found

DECLARE
    collection  SODA_COLLECTION_T;
    num_docs    NUMBER;
    qbe         VARCHAR2(128);
BEGIN
    -- Open the collection
    collection := DBMS_SODA.open_collection('myCollectionName');

    -- Get a count of all documents in the collection
    num_docs := collection.find().count;
    DBMS_OUTPUT.put_line('Count: ' || num_docs);
    
    -- Set the filter
    qbe := '{"name" : "Alexander"}';

    -- Get a count of all documents in the collection that match a filter spec
    num_docs := collection.find().filter(qbe).count;
    DBMS_OUTPUT.put_line('Count: ' || num_docs);
END;

Module 21

Replacing a Document in a Collection, Given Its Key, and Getting the Result Document


REM Replacing a Document in a Collection, Given Its Key, and Getting the Result Document

DECLARE
    collection  SODA_COLLECTION_T;
    document    SODA_DOCUMENT_T;
    new_doc     SODA_DOCUMENT_T;
    k           VARCHAR2(255);
BEGIN
    collection := DBMS_SODA.open_collection('myCollectionName');
    SELECT ID into k from "myCollectionName" where rownum < 2;
    document := SODA_DOCUMENT_T(
                  b_content => utl_raw.cast_to_raw('{"name" : "Sriky"}'));
    new_doc := collection.find().key(k).replace_one_and_get(document);

    IF new_doc IS NOT NULL THEN
        DBMS_OUTPUT.put_line('Document components:');
        DBMS_OUTPUT.put_line('Key: ' || new_doc.get_key);
        DBMS_OUTPUT.put_line('Creation timestamp: ' || new_doc.get_created_on);
        DBMS_OUTPUT.put_line('Last modified timestamp: ' ||
                             new_doc.get_last_modified);
        DBMS_OUTPUT.put_line('Version: ' || new_doc.get_version);
    END IF;
END;

Module 22

Replacing a Particular Version of a Document

To implement optimistic locking when replacing a document, you can chain together methods key() and version(), as in this example.


REM Specifying Document Version to search

DECLARE
    collection  SODA_COLLECTION_T;
    document    SODA_DOCUMENT_T;
    k           varchar2(255);
    v           varchar2(255);
BEGIN
    -- Open the collection
    collection := DBMS_SODA.open_collection('myCollectionName');

    select ID, VERSION into k, v from "myCollectionName" where rownum < 2;

    -- Find a particular version of the document that has a given key
    document := collection.find().key(k).version(v).get_one;

    IF document IS NOT NULL THEN
        DBMS_OUTPUT.put_line('Document components:');
        DBMS_OUTPUT.put_line('Key: ' || document.get_key);
        DBMS_OUTPUT.put_line('Content: ' ||
                             JSON_QUERY(document.get_blob, '$' PRETTY));
        DBMS_OUTPUT.put_line('Creation timestamp: ' || document.get_created_on);
        DBMS_OUTPUT.put_line('Last modified timestamp: ' ||
                             document.get_last_modified);
        DBMS_OUTPUT.put_line('Version: ' || document.get_version);
    END IF;
END;

Module 23

Removing Documents from a Collection with SODA for PL/SQL

This example removes the document whose document key is "key1". The removal status (1 if the document was removed; 0 if not) is returned and printed.



REM Removing Documents from a Collection with SODA for PL/SQL

DECLARE
    collection  SODA_COLLECTION_T;
    document    SODA_DOCUMENT_T;
    status      NUMBER;
    k           varchar2(255);
BEGIN
    -- Open the collection
    collection := DBMS_SODA.open_collection('myCollectionName');

    SELECT ID into k from "myCollectionName" where rownum < 2;

    -- Remove document that has key
    status := collection.find().key(k).remove;

    -- Count is 1 if  document was found
    IF status = 1 THEN
        DBMS_OUTPUT.put_line('Document was removed!');
    END IF;
END;

Module 24

Removing a Particular Version of a Document

To implement optimistic locking when removing a document, you can chain together methods key() and version(), as in this example.


REM Removing a Particular Version of a Document
DECLARE
    collection  SODA_COLLECTION_T;
    document    SODA_DOCUMENT_T;
    status      NUMBER;
    k           VARCHAR2(255);
    v           VARCHAR2(255);
BEGIN
    -- Open the collection
    collection := DBMS_SODA.open_collection('myCollectionName');

    select ID, VERSION into k, v from "myCollectionName";

    -- Remove version 'version1' of the document that has key 'key1'.
    status := collection.find().key(k).version(v).remove;

    -- Count is 1, if specified version of document with key 'key1' is found
    IF status = 1 THEN
        DBMS_OUTPUT.put_line('Document was removed!');
    END IF;
END;

Module 25

QBE: Count all Microsoft employees that are Managers

The following example applies a filter on a collection and finds the count of matching documents using count() function.


REM Count all Microsoft employees that are Managers

DECLARE
    collection SODA_Collection_T;
    operation  SODA_Operation_T;
    document   SODA_Document_T;
    cur        SODA_Cursor_T;
    qbe        VARCHAR2(400);
    rowCount   NUMBER;
BEGIN
    collection := dbms_soda.open_collection('Employees');
    oow_soda.show(collection,
                  setting => oow_soda.SHOW_COLL_NAME_ONLY);

    qbe := '{"$query" : {
                 "title" : { "$eq" : "Manager" },
                 "company" : { "$eq" : "Microsoft" }
              }
            }';
    operation := collection.find().filter(qbe);
    rowCount := operation.count();
    dbms_output.put_line('No. of docs: ' || rowCount);
    COMMIT;
END;

Module 26

Specifying Pagination Queries with Methods skip() and limit()

This example uses SODA_COLLECTION_T methods filter(), skip() and limit() in a pagination query.


REM Specifying Pagination Queries with Methods skip() and limit()

DECLARE
    collection  SODA_COLLECTION_T;
    document    SODA_DOCUMENT_T;
    cur         SODA_Cursor_T;
    status      BOOLEAN;
    pos         number := 0;
BEGIN
    -- Open the collection
    collection := DBMS_SODA.open_collection('Employees');

    -- Find a document using a key
    cur := collection.find().skip(50).limit(100).get_cursor;

    -- Loop through the cursor
    WHILE cur.has_Next
    LOOP
      document := cur.next;
      pos := pos + 1;
      IF document IS NOT NULL THEN
          DBMS_OUTPUT.put_line('Document components:');
          DBMS_OUTPUT.put_line('Key: ' || document.get_key);
          DBMS_OUTPUT.put_line('Content: ' ||
                               JSON_QUERY(document.get_blob, '$' PRETTY));
          DBMS_OUTPUT.put_line('Creation timestamp: ' || 
                               document.get_created_on);
          DBMS_OUTPUT.put_line('Last modified timestamp: ' ||
                               document.get_last_modified);
          DBMS_OUTPUT.put_line('Version: ' || document.get_version);
      END IF;
    END LOOP;
    dbms_output.put_line('Docs. processed: ' || pos);
    status := cur.close;
END;

Module 27

Creating a B-Tree Functional Index for a JSON Field

You index the documents in a SODA collection with SODA_COLLECTION_T method create_index(). Its input parameter is a textual JSON index specification. This can specify support for B-tree, spatial, full-text, and ad hoc indexing, and it can specify support for a JSON data guide. This example creates a B-tree non-unique index for numeric field salary of the JSON documents in collection Employees.


REM Creating a B-Tree functional index

DECLARE
  spec    VARCHAR2(700);
  coll    SODA_Collection_T;
  status  NUMBER;
BEGIN
  coll := dbms_soda.open_Collection('Employees');
  spec := '{"name"   : "Salary_IDX",
            "unique" : false,
            "fields" : [{"path"     : "salary",
                         "datatype" : "NUMBER"}]
           }';
  status := coll.drop_Index('Salary_IDX');
  status := coll.create_Index(spec);
  dbms_output.put_Line('Status: ' || status);
END;

Module 28

QBE: Using $orderby clause and numbers

The following examples finds all the employee documents whose employee number is less than or equal to 20040, whose salary is greater than 200000 and whose name starts with "Melissa". It also orders the result in ascending order of salary.


REM Using order by clause and numbers in QBE


DECLARE
    collection SODA_Collection_T;
    operation  SODA_Operation_T;
    document   SODA_Document_T;
    cur        SODA_Cursor_T;
    qbe        VARCHAR2(400);
    pos        NUMBER  := 0;
    status     BOOLEAN;
BEGIN
    collection := dbms_soda.open_collection('Employees');
    oow_soda.show(collection,
                  setting => oow_soda.SHOW_COLL_NAME_ONLY);

    qbe := '{"$query" :{
                "empno" : { "$lte": 20040 },
                "name" : { "$startsWith" : "Melissa" },
                "salary" : { "$gt" : 200000 }
              },
             "$orderby" : [
                  {"path" : "salary", "datatype" : "number", "order" : "asc"}
                ]
            }';
    operation := collection.find().filter(qbe);
    cur := operation.get_Cursor;
    -- Loop over the cursor
    WHILE cur.has_Next()
    LOOP
        pos := pos + 1;
        document := cur.next;
        oow_soda.show(document,
                      comments => 'Doc ('|| pos ||')');
    END LOOP;
    dbms_output.put_line('No. of docs read: ' || pos);
    status := cur.close;
END;

Module 29

QBE: Using Geo Spatial Query using $near

The following example fetches the cursor to a list of employees whose title is Director and whose location is within 100 miles from a given point. It orders the results in ascending order of salary.



REM Geo Spatial Query - $near

DECLARE                        
    collection SODA_Collection_T;
    operation  SODA_Operation_T; 
    document   SODA_Document_T;  
    cur        SODA_Cursor_T;                           
    qbe        VARCHAR2(800);
    pos        NUMBER  := 0;
    hasNext    BOOLEAN := TRUE;
    status     BOOLEAN;
BEGIN     
                                              
    collection := dbms_soda.open_collection('Employees');
    oow_soda.show(collection,
                   setting => oow_soda.SHOW_COLL_NAME_ONLY);

    qbe := '{
	       "$query": {
		  "title": { "$eq": "Director" },
		  "location": {
			"$near": {
				"$geometry": {
					"type": "Point",
					"coordinates": [122, 47]
				 },
				 "$unit": "mile",
				 "$distance": 100
			     }
		         }
	        },
	        "$orderby": [{
		   "path": "salary",
		    "order": "asc"
	        }]
            }';
    operation := collection.find().filter(qbe);  
    cur := operation.get_Cursor;

    -- Loop over the cursor
    WHILE cur.has_Next()
    LOOP
        pos := pos + 1;
        document := cur.next;
        oow_soda.show(document,
                       comments => 'Doc ('|| pos ||')');
    END LOOP;
    dbms_output.put_line('No. of docs read: ' || pos);
    status := cur.close;
END;

Module 30

Creating Dataguide enabled Search Index

This example indexes the documents in Employees collection for ad hoc queries and full-text search (queries using QBE operator $contains), and it automatically accumulates and updates data-guide information about your JSON documents (aggregate structural and type information). The index specification has only field name (no field fields).


REM Creating Dataguide enabled Search Index

-- Now, create an index
DECLARE
  spec VARCHAR2(100);
  coll SODA_Collection_T;
  n number;
BEGIN
  spec := '{"name" : "SODAPLSDG_txt",
            "dataguide" : "on",
            "search_on" : "text_value"}';
  coll := dbms_soda.open_Collection('Employees');

  -- Drop the index
  n := coll.drop_Index('SODAPLSDG_txt');
  dbms_output.put_Line('Drop status: ' || n);

  -- Create the index
  n := coll.create_Index(spec);
  dbms_output.put_line('Create status: ' || n);
END;
/

Module 31

QBE: Filter documents that contain a text

The following example uses $contains QBE operator to filter employee documents that contains the text 'President' in the title field and the text 'Salesforce' in company field.


REM Test text search of the documents
REM Get employees whose salary > 240000 whose title has the word 'President' and company has the word 'Salesforce'

DECLARE
    collection SODA_Collection_T;
    operation  SODA_Operation_T;
    document   SODA_Document_T;
    cur        SODA_Cursor_T;
    qbe        VARCHAR2(400);
    pos        NUMBER  := 0;
BEGIN
    collection := dbms_soda.open_collection('Employees');

    qbe := '{"$query" :{
                "salary" : { "$gt" : 240000 },
                "title" : { "$contains" : "President" },
                "company" : { "$contains" : "Salesforce" }
              },
             "$orderby" : [
                  {"path" : "salary", "order" : "asc"}
                ]
            }';
    operation := collection.find().filter(qbe);
    document := operation.get_One;
    oow_soda.show(document);
END;
/

Module 32
Get Dataguide using Search Index
You use SODA_COLLECTION_T method get_Data_Guide() to get a data guide for a collection. A data guide is a JSON document that summarizes the structural and type information of the JSON documents in the collection. It records metadata about the fields used in those documents. Note: Before you can obtain a data guide for your collection you must create a data guide-enabled JSON search index on it. This example gets a data guide for collection MyCollectionName using SODA_COLLECTION_T method get_Data_Guide(). It then uses SQL/JSON function json_query to pretty-print the content of the data-guide document. Finally, it frees the temporary LOB used for the data-guide document.
```
REM Fetch the data guide now that index is created

DECLARE
    coll  SODA_Collection_T;
    dg    CLOB;
BEGIN
    coll := dbms_soda.open_Collection('Employees');
    dg := coll.get_Data_Guide;
    dbms_output.put_line(JSON_QUERY(dg, '$' pretty));
    if dbms_lob.isTemporary(dg) = 1
    then
      dbms_output.put_Line('Temporary lob needs to be freed');
      dbms_lob.freeTemporary(dg);
    end if;   
END;
```

Module 33

Using SQL against SODA Collections

You can also use native SQL statements to select documents from a SODA collection. The following SQL query selects Employee's ID, name, salary and company from the employee document matching a filter criteria.


REM Using SQL/JSON operators to filter documents

  SELECT emp.ID "ID",
         JSON_Value(emp."JSON_DOCUMENT",
                    '$.name') "Name",
         JSON_Value(emp."JSON_DOCUMENT",
                    '$.salary') "Salary",
         JSON_Value(emp."JSON_DOCUMENT",
                    '$.company') "Company",
         JSON_Query(emp."JSON_DOCUMENT",
                    '$.address' returning VARCHAR2(500)) "Address"
    FROM "Employees" emp
   WHERE JSON_Exists(emp."JSON_DOCUMENT",
                     '$?(@.empno <= $B0 && @.salary > $B1 && @.name starts with $B2)'
                     PASSING 10041 AS "B0", 200000 AS "B1", 'Melissa' AS "B2")
     AND JSON_TextContains(emp."JSON_DOCUMENT",
                           '$.company',
                           'Salesforce')              
ORDER BY JSON_Value(emp."JSON_DOCUMENT",
                    '$.salary') ASC;

Module 34
Handling Transactions with SODA
As usual in PL/SQL and SQL, you can treat individual SODA read and write operations, or groups of them, as a transaction. To commit a transaction, use a SQL COMMIT statement. If you want to roll back changes, use a SQL ROLLBACK statement. SODA operations DBMS_SODA.create_collection and DBMS_SODA.drop_collection do not automatically commit before or after they perform their action. This differs from the behavior of SQL DDL statements, which commit both before and after performing their action. One consequence of this is that, before a SODA collection can be dropped, any outstanding write operations to it must be explicitly committed or rolled back — you must explicitly use SQL COMMIT or ROLLBACK. This is because DBMS_SODA.drop_collection does not itself issue commit before it performs its action. In this, the behavior of DBMS_SODA.drop_collection differs from that of a SQL DROP TABLE statement. This example shows the use of SQL COMMIT and ROLLBACK statements in an anonymous PL/SQL block. It opens a SODA collection, inserts a document, and then replaces its content. The combination of the document insertion and document content replacement operations is atomic: a single transaction.
```
REM Handling Transactions with SODA

DECLARE
    collection SODA_COLLECTION_T;
    status NUMBER;
    doc SODA_Document_T;
BEGIN
    collection := dbms_soda.open_collection('myCollectionName');
    doc := SODA_Document_T(b_Content => utl_raw.cast_to_raw('{"a":"aval","b":"bval"}'));
    status := collection.insert_one(doc);
    status := collection.replace_one('ABCDEF', SODA_DOCUMENT_T(b_Content => utl_raw.cast_to_raw('{"x":"xval","y":"yval"}')));
    -- Commit the transaction
    COMMIT;
    DBMS_OUTPUT.put_line('Transaction is committed');
-- Catch exceptions and roll back if an error is raised
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (SQLERRM);
    ROLLBACK;
    DBMS_OUTPUT.put_line('Transaction has been rolled back');
END;
```

Additional Information

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