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;
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;
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;
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;
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;
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;
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;
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;
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;
DECLARE
l_collection soda_collection_t;
BEGIN
l_collection := dbms_soda.create_collection ('SODACollection');
END;
Each Collection Has Its Own Table
SELECT table_name FROM USER_TABLES
WHERE table_name like 'SODA%'
TABLE_NAME | SODACollection |
---|
EXEC show_soda_collection_count
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;
SELECT id FROM "MyFriends"
ID | 4999CF30DB0C4F6DBFB174A75BDF5CDF |
---|
EXEC show_soda_collection_count