Oracle Live SQL - Script: SODA for PL/SQL: Read and Write Operations

Statement 1

A Clean Start

DECLARE 
   l_status   PLS_INTEGER; 
BEGIN 
   l_status := dbms_soda.drop_collection ('FriendsWithDocs'); 
   l_status := dbms_soda.drop_collection ('MyOwnKeys'); 
   l_status := dbms_soda.drop_collection ('ColorKeys'); 
   l_status := dbms_soda.drop_collection ('SillyCollection'); 
   COMMIT; 
END;

Statement processed.

Statement 2

DECLARE
   l_collection   soda_collection_t;
   l_status         PLS_INTEGER;
BEGIN
   l_collection := dbms_soda.create_collection ('FriendsWithDocs');

   l_status :=
      l_collection.insert_one (
         soda_document_t (
            b_content   => UTL_RAW.cast_to_raw (
                             '{"friend_type":1,
                               "friend_name":"Lakshmi", 
                               "favorites": [{"song" : "Somewhere Over the Rainbow"},
                                             {"spice" : "tamarind"},
                                             {"flavor" : "cherry"} ]}')));

   l_status :=
      l_collection.insert_one (
         soda_document_t (
            b_content   => UTL_RAW.cast_to_raw (
                             '{"friend_type":2,
                               "friend_name":"Sally", 
                               "favorites": [{"color" : "blue"},
                                             {"flavor" : "chocolate"},
                                             {"flower" : "rose"} ]}')));

   l_status :=
      l_collection.insert_one (
         soda_document_t (
            b_content   => UTL_RAW.cast_to_raw (
                             '{"friend_type":2,
                               "friend_name":"Jorge", 
                               "favorites": [{"color" : "green"},
                                             {"flavor" : "chocolate"},
                                             {"tree" : "oak"} ]}')));
END;

Statement processed.

Statement 3

DECLARE
   l_collection   soda_collection_t;
   l_operation    soda_operation_t;
   l_cursor       soda_cursor_t;
   l_status         BOOLEAN;
BEGIN
   l_collection := dbms_soda.open_collection ('FriendsWithDocs');

   l_operation := l_collection.find ();

   l_cursor := l_operation.get_cursor ();

   l_status := l_cursor.close;
END;

Statement processed.

Statement 4

DECLARE
   l_cursor   soda_cursor_t;
   l_status     BOOLEAN;
BEGIN
   l_cursor :=
      dbms_soda.open_collection ('FriendsWithDocs').find ().get_cursor ();

   l_status := l_cursor.close;
END;

Statement processed.

Statement 5

DECLARE
   l_document   soda_document_t;
   l_cursor     soda_cursor_t;
   l_content_shown BOOLEAN := FALSE;
   l_status       BOOLEAN;
BEGIN
   l_cursor :=
      dbms_soda.open_collection ('FriendsWithDocs').find ().get_cursor ();

   -- Loop through the cursor
   WHILE l_cursor.has_next
   LOOP
      l_document := l_cursor.NEXT;

      IF l_document IS NOT NULL
      THEN
         DBMS_OUTPUT.put_line ('Document key: ' || l_document.get_key);
         
         IF NOT l_content_shown
         THEN
            l_content_shown := TRUE;
            DBMS_OUTPUT.put_line (
               'Content: ' || json_query (l_document.get_blob, '$' PRETTY));
         END IF;
         
         DBMS_OUTPUT.put_line('Creation timestamp: ' || l_document.get_created_on);
         DBMS_OUTPUT.put_line('Last modified timestamp: ' || l_document.get_last_modified);
         DBMS_OUTPUT.put_line('Version: ' || l_document.get_version);
      END IF;
    END LOOP;

    l_status := l_cursor.close;
END;

Statement processed.
Document key: B1E9156B54BA4FD7BF1437BBEEAA9AD7
Content: {
  "friend_type" : 1,
  "friend_name" : "Lakshmi",
  "favorites" :
  [
    {
      "song" : "Somewhere Over the Rainbow"
    },
    {
      "spice" : "tamarind"
    },
    {
      "flavor" : "cherry"
    }
  ]
}
Creation timestamp: 2019-05-14T20:12:33.964039Z
Last modified timestamp: 2019-05-14T20:12:33.964039Z
Version: 698637C8DC1A843E0078D77DD1680D17A4E8B9A8108B87081BE44902AD9484FD
Document key: 293B2EBEC88A4FDBBF394BE54EDAFD33
Creation timestamp: 2019-05-14T20:12:33.965115Z
Last modified timestamp: 2019-05-14T20:12:33.965115Z
Version: D5B1E2370AB9A6C15FDCC0DB53E38D1D3B5306E57B039040E22BF7619A5166B1
Document key: C8E58568F1144F4EBFAC3F1ABDC1000D
Creation timestamp: 2019-05-14T20:12:33.965351Z
Last modified timestamp: 2019-05-14T20:12:33.965351Z
Version: 63A7781DBA6117B6CBACECF5CB9959054D1FF6D0852317701D9A10F9A43DABA4

Statement 6

DECLARE
   l_document   soda_document_t;
   l_cursor     soda_cursor_t;
   l_filter          VARCHAR2 (128) := '{"friend_type" : "2"}';
   l_status       BOOLEAN;
BEGIN
   l_cursor :=
      dbms_soda.open_collection ('FriendsWithDocs').find ().filter (l_filter).get_cursor ();

   WHILE l_cursor.has_next
   LOOP
      l_document := l_cursor.NEXT;

      IF l_document IS NOT NULL
      THEN
         DBMS_OUTPUT.put_line ('Document key: ' || l_document.get_key);
         DBMS_OUTPUT.put_line (
            'Content: ' || json_query (l_document.get_blob, '$' PRETTY));
      END IF;
    END LOOP;

    l_status := l_cursor.close;
END;

Statement processed.
Document key: 293B2EBEC88A4FDBBF394BE54EDAFD33
Content: {
  "friend_type" : 2,
  "friend_name" : "Sally",
  "favorites" :
  [
    {
      "color" : "blue"
    },
    {
      "flavor" : "chocolate"
    },
    {
      "flower" : "rose"
    }
  ]
}
Document key: C8E58568F1144F4EBFAC3F1ABDC1000D
Content: {
  "friend_type" : 2,
  "friend_name" : "Jorge",
  "favorites" :
  [
    {
      "color" : "green"
    },
    {
      "flavor" : "chocolate"
    },
    {
      "tree" : "oak"
    }
  ]
}

Statement 7

DECLARE
   l_document   soda_document_t;
   l_cursor     soda_cursor_t;
   l_filter          VARCHAR2 (128) := '{"favorites.flavor" : "chocolate"}';
   l_status       BOOLEAN;
BEGIN
   l_cursor :=
      dbms_soda.open_collection ('FriendsWithDocs').find ().filter (l_filter).get_cursor ();

   WHILE l_cursor.has_next
   LOOP
      l_document := l_cursor.NEXT;

      IF l_document IS NOT NULL
      THEN
         DBMS_OUTPUT.put_line ('Document key: ' || l_document.get_key);
         DBMS_OUTPUT.put_line (
            'Content: ' || json_query (l_document.get_blob, '$' PRETTY));
      END IF;
    END LOOP;

    l_status := l_cursor.close;
END;

Statement processed.
Document key: 293B2EBEC88A4FDBBF394BE54EDAFD33
Content: {
  "friend_type" : 2,
  "friend_name" : "Sally",
  "favorites" :
  [
    {
      "color" : "blue"
    },
    {
      "flavor" : "chocolate"
    },
    {
      "flower" : "rose"
    }
  ]
}
Document key: C8E58568F1144F4EBFAC3F1ABDC1000D
Content: {
  "friend_type" : 2,
  "friend_name" : "Jorge",
  "favorites" :
  [
    {
      "color" : "green"
    },
    {
      "flavor" : "chocolate"
    },
    {
      "tree" : "oak"
    }
  ]
}

Statement 8

DECLARE
   l_document     soda_document_t;
   l_collection   soda_collection_t;
   l_metadata       VARCHAR2 (4000)
      := '{"keyColumn" : {"name" : "KEY", "assignmentMethod" : "CLIENT" }}';
   l_status         PLS_INTEGER;
BEGIN
   l_collection := dbms_soda.create_collection ('MyOwnKeys', l_metadata);
   
   DBMS_OUTPUT.put_line (
         'Collection specification: '
      || json_query (l_collection.get_metadata, '$' PRETTY));
      
   l_status :=
      l_collection.insert_one (
         soda_document_t ('FriendLakshmi',
            b_content   => UTL_RAW.cast_to_raw (
                             '{"friend_type":1,"friend_name":"Lakshmi"}')));

   l_document :=
      l_collection
         .find ()
         .key ('FriendLakshmi')
         .get_one;

   DBMS_OUTPUT.put_line ('Key: ' || l_document.get_key);
   DBMS_OUTPUT.put_line ('Content: ');
   DBMS_OUTPUT.put_line (   
      l_document.get_key|| '+'||json_query (l_document.get_blob, '$' PRETTY));
END;

Statement processed.
Collection specification: {
  "schemaName" : "SQL_JDPCIPUIGVRVQCCFIPTWPEHQC",
  "tableName" : "MyOwnKeys",
  "keyColumn" :
  {
    "name" : "KEY",
    "sqlType" : "VARCHAR2",
    "maxLength" : 255,
    "assignmentMethod" : "CLIENT"
  },
  "contentColumn" :
  {
    "name" : "JSON_DOCUMENT",
    "sqlType" : "BLOB",
    "compress" : "NONE",
    "cache" : true,
    "encrypt" : "NONE",
    "validation" : "STANDARD"
  },
  "readOnly" : false
}
Key: FriendLakshmi
Content: 
FriendLakshmi+{
  "friend_type" : 1,
  "friend_name" : "Lakshmi"
}

Statement 9

DECLARE
   l_collection   soda_collection_t;
   l_metadata     VARCHAR2 (4000)
      := '{"keyColumn" : {"name" : "KEY",  "assignmentMethod": "CLIENT" }}';
   l_keys         soda_key_list_t;
   l_cursor       soda_cursor_t;
   l_status       BOOLEAN;
   l_document     soda_document_t;
BEGIN
   l_collection := dbms_soda.create_collection ('ColorKeys', l_metadata);

   l_status :=
      l_collection.insert_one (
         soda_document_t (
            'Red',
            b_content   => UTL_RAW.cast_to_raw ('{"thing":"blood"}'))) = 1;
   l_status :=
      l_collection.insert_one (
         soda_document_t (
            'Green',
            b_content   => UTL_RAW.cast_to_raw ('{"thing":"grass"}'))) = 1;

   l_keys := soda_key_list_t ('Red', 'Green');

   l_cursor := l_collection.find ().keys (l_keys).get_cursor ();

   WHILE l_cursor.has_next
   LOOP
      l_document := l_cursor.NEXT;

      IF l_document IS NOT NULL
      THEN
         DBMS_OUTPUT.put_line ('Key: ' || l_document.get_key);
         DBMS_OUTPUT.put_line (
            json_query (l_document.get_blob, '$' PRETTY));
      END IF;
    END LOOP;

    l_status := l_cursor.close;
END;

Statement processed.
Key: Green
{
  "thing" : "grass"
}
Key: Red
{
  "thing" : "blood"
}

Statement 10

DECLARE
    l_collection  SODA_COLLECTION_T;
    l_document    SODA_DOCUMENT_T;
    l_new_document SODA_DOCUMENT_T;
BEGIN
    l_collection := DBMS_SODA.open_collection('ColorKeys');
    l_document := SODA_DOCUMENT_T(
                  b_content => utl_raw.cast_to_raw('{"Blue" : "Bluebird"}'));
    l_new_document := l_collection.find().key('Red').replace_one_and_get(l_document);

    IF l_new_document IS NOT NULL 
    THEN
        DBMS_OUTPUT.put_line('Document:');
        DBMS_OUTPUT.put_line('Key: ' || l_new_document.get_key);
        DBMS_OUTPUT.put_line('Creation timestamp: ' || l_new_document.get_created_on);
    END IF;
END;

Statement processed.
Document:
Key: Red
Creation timestamp:

Statement 11

DECLARE
    l_collection  SODA_COLLECTION_T;
    l_status NUMBER;
BEGIN
    l_collection := DBMS_SODA.open_collection('ColorKeys');
    
    l_status:= l_collection.find().key('Green').remove;

    IF  l_status = 1 
    THEN
        DBMS_OUTPUT.put_line('Document removed');
    END IF;
END;

Statement processed.
Document removed

Statement 12

DECLARE
   l_collection   soda_collection_t;
   l_document     soda_document_t;
   l_cursor       soda_cursor_t;
   l_status       BOOLEAN;
BEGIN
   l_collection := dbms_soda.create_collection ('SillyCollection');

   FOR indx IN 1 .. 100
   LOOP
      l_status :=
         l_collection.insert_one (
            soda_document_t (
               b_content   => UTL_RAW.cast_to_raw (
                                   '{"myIndex":'
                                || indx
                                || ',"myValue":"'
                                || CASE MOD (indx, 2)
                                      WHEN 0 THEN 'Even'
                                      ELSE 'Odd'
                                   END
                                || '"}'))) = 1;
   END LOOP;

   l_cursor := l_collection.find ().skip (50).LIMIT (5).get_cursor;

   WHILE l_cursor.has_next
   LOOP
      l_document := l_cursor.NEXT;

      IF l_document IS NOT NULL
      THEN
         DBMS_OUTPUT.put_line (
            'Content: ' || json_query (l_document.get_blob, '$' PRETTY));
      END IF;
    END LOOP;

    l_status := l_cursor.close;
END;

Statement processed.
Content: {
  "myIndex" : 86,
  "myValue" : "Even"
}
Content: {
  "myIndex" : 80,
  "myValue" : "Even"
}
Content: {
  "myIndex" : 68,
  "myValue" : "Even"
}
Content: {
  "myIndex" : 94,
  "myValue" : "Even"
}
Content: {
  "myIndex" : 46,
  "myValue" : "Even"
}

Statement 13

DECLARE 
   l_collection   soda_collection_t; 
   l_document     soda_document_t; 
   l_cursor       soda_cursor_t; 
   l_status       BOOLEAN; 
BEGIN 
   l_collection := dbms_soda.open_collection ('SillyCollection'); 
 
   l_cursor := l_collection. 
      find(). 
      filter('{"$orderby" : [ { "path" : "myIndex", "datatype" : "number", "order" : "asc"}]}'). 
      skip(50).limit(5).get_cursor(); 
 
   WHILE l_cursor.has_next 
   LOOP 
      l_document := l_cursor.NEXT; 
 
      IF l_document IS NOT NULL 
      THEN 
         DBMS_OUTPUT.put_line ( 
            'Content: ' || json_query (l_document.get_blob, '$' PRETTY)); 
      END IF; 
    END LOOP; 
 
    l_status := l_cursor.close; 
END;

Statement processed.
Content: {
  "myIndex" : 51,
  "myValue" : "Odd"
}
Content: {
  "myIndex" : 52,
  "myValue" : "Even"
}
Content: {
  "myIndex" : 53,
  "myValue" : "Odd"
}
Content: {
  "myIndex" : 54,
  "myValue" : "Even"
}
Content: {
  "myIndex" : 55,
  "myValue" : "Odd"
}

SODA for PL/SQL: Read and Write Operations

Additional Information