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}
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.
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.
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
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
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 1721More Details: https://docs.oracle.com/error-help/db/ora-40659
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.
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.
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"}
DECLARE
l_collection soda_collection_t;
BEGIN
l_collection := dbms_soda.create_collection ('SODACollection');
END;
Statement processed.
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
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
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.
SELECT id FROM "MyFriends"
ID | 4999CF30DB0C4F6DBFB174A75BDF5CDF |
---|
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