Example 3-1 Getting Started Run-Through

DECLARE
    collection      SODA_COLLECTION_T;
    document        SODA_DOCUMENT_T;
    foundDocument   SODA_DOCUMENT_T;
    result_document SODA_DOCUMENT_T;
    docKey          VARCHAR2(100);
    status          NUMBER;
BEGIN
    -- Create a collection.
    collection := DBMS_SODA.create_collection('myCollectionName');

    -- The default collection has BLOB content, so create a BLOB-based document.
    document := SODA_DOCUMENT_T(
                  b_content => utl_raw.cast_to_raw('{"name" : "Alexander"}'));

    -- Insert the document and get it back.
    result_document := collection.insert_one_and_get(document);

    -- The result document has auto-generated components, such as key and version,
    -- in addition to the content.  Print the auto-generated document key.
    docKey := result_document.get_key;
    DBMS_OUTPUT.put_line('Auto-generated key is ' || docKey);

    -- Commit the insert
    COMMIT;

    -- Find the document in the collection by its key
    foundDocument := collection.find_one(docKey);

    -- Get and print some document components: key, content, etc.
    DBMS_OUTPUT.put_line('Document components:');
    DBMS_OUTPUT.put_line('  Key: ' || foundDocument.get_key);
    DBMS_OUTPUT.put_line('  Content: '
                         || utl_raw.cast_to_varchar2(foundDocument.get_blob));
    DBMS_OUTPUT.put_line('  Creation timestamp: ' || foundDocument.get_created_on);
    DBMS_OUTPUT.put_line('  Last-modified timestamp: '
                         || foundDocument.get_last_modified);
    DBMS_OUTPUT.put_line('  Version: ' || foundDocument.get_version);
END;
/

Example 3-2 Sample Output for Getting Started Run-Through

Example 3-1 results in output similar to this. The values of the auto-generated components will differ in any actual execution.

Auto-generated key is 96F35328CD3B4F96BF3CD01BCE9EBDF5
Document components:
  Key: 96F35328CD3B4F96BF3CD01BCE9EBDF5
  Content: {"name" : "Alexander"}
  Creation timestamp: 2017-09-19T01:05:06.160289Z
  Last-modified timestamp: 2017-09-19T01:05:06.160289Z
  Version: FD69FB6ACE73FA735EC7922CA4A02DDE0690462583F9EA2AF754D7E342B3EE78

Example 3-3 Creating a Collection That Has the Default Metadata

This example creates collection myCollectionName with the default metadata.

DECLARE
    collection  SODA_Collection_T;
BEGIN
    collection := DBMS_SODA.create_collection('myCollectionName');   
END;
/

Example 3-4 Opening an Existing Document 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.

DECLARE
    collection  SODA_COLLECTION_T;
BEGIN
    collection := DBMS_SODA.open_collection('myCollectionName');
END;
/

Example 3-5 Checking for a Collection with a Given Name

This example uses DBMS_SODA.open_collection to try to open an existing collection named myCollectionName. It prints a message if no such collection exists.

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');
    END IF;
END;
/

Example 3-6 Printing the Names of All 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.

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: ');
    IF (coll_list.count > 0) THEN
        -- Loop over the collection name list
        FOR i IN
            coll_list.first .. coll_list.last
        LOOP
            DBMS_OUTPUT.put_line(coll_list(i));
        END LOOP;  
    ELSE   
        DBMS_OUTPUT.put_line('No collections found');
    END IF;
END;
/

Example 3-7 Dropping a Document 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.

DECLARE
    status  NUMBER := 0;
BEGIN
    status := DBMS_SODA.drop_collection('myCollectionName');
END;
/

Example 3-8 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 in Example 3-3, 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.

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

Example 3-9 Creating a Document with Document Key and JSON Content

This example is similar to Example 3-8, but it provides the document key (myKey) as well as the document 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;
/

Example 3-10 Inserting a Document into a Collection

This example creates a document and inserts it into a collection using SODA_COLLECTION_T method insert_one() .

DECLARE
    collection  SODA_COLLECTION_T;
    document    SODA_DOCUMENT_T;
    status      NUMBER;
BEGIN
    -- Open the collection
    collection := DBMS_SODA.open_collection('myCollectionName');
    document := SODA_DOCUMENT_T(
                  b_content => utl_raw.cast_to_raw('{"name" : "Alexander"}'));

    -- Insert a document
    status := collection.insert_one(document);
END;
/

Example 3-11 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().

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" : "Alexander"}'));
    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;
/

Example 3-12 Finding the Unique Document That Has a Given Document Key

This example uses SODA_COLLECTION_T method find_one() to find the unique document whose key is "key1".

It then uses SODA_DOCUMENT_T methods get_key(), get_blob(), get_created_on(), get_last_modified(), and get_version(), to get the document metadata, which it passes to SQL/JSON function json_query to pretty-print (using keyword PRETTY).

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

    - Find a document using a key
    document := collection.find_one('key1');

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

Example 3-13 Replacing a Document in a Collection, Given Its Key, 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().

DECLARE
    collection  SODA_COLLECTION_T;
    document    SODA_DOCUMENT_T;
    rep_doc     SODA_DOCUMENT_T;
BEGIN
    collection := DBMS_SODA.open_collection('myCollectionName');
    document := SODA_DOCUMENT_T(
                  b_content => utl_raw.cast_to_raw('{"name" : "Sriky"}'));
    rep_doc := collection.replace_one_and_get('k1', 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;
/

Example 3-14 Removing a Document from a Collection Using a Document Key

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.

DECLARE
    collection  SODA_COLLECTION_T;
    status      NUMBER;
BEGIN
    collection := DBMS_SODA.open_collection('myCollectionName');
    status := collection.remove_one('key1');
    DBMS_OUTPUT.put_line('Status: ' || status);    
END;
/

Example 3-15 Transaction Involving SODA Document Insertion and Replacement

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.

DECLARE
    collection SODA_COLLECTION_T;
    status NUMBER;
BEGIN
    collection := DBMS_SODA.open_collection('myCollectionName');
    status := collection.insert_one(
                SODA_Document_T(
                  b_content => utl_raw.cast_to_raw('{"a":"aval","b":"bval","c":"cval"}'));
    status := collection.replace_one('key1', SODA_DOCUMENT_T('{"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;
/