SODA, Simple Oracle Document Access, works with JSON documents as a document store: collections of documents, found by key or by query-by-example filters, without writing SQL. DBMS_SODA and its object types bring the same API to PL/SQL that SODA drivers offer in Java, Python, and Node.js.
Code for This Guide
The main examples are in the examples/pkg-json folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
v_coll := dbms_soda.create_collection('name'); -- or open_collection
n := v_coll.insert_one(soda_document_t(b_content => raw_json));
v_coll.find().count
v_coll.find().filter('{"field":"value"}').get_cursor
v_coll.find().key(key).replace_one(document)
v_coll.find().filter(...).remove
n := dbms_soda.drop_collection('name');A Lost Baggage Collection
Example:
declare
v_coll soda_collection_t;
v_cur soda_cursor_t;
v_doc soda_document_t;
v_n number;
v_ok boolean;
function doc (p_json varchar2) return soda_document_t is
begin
return soda_document_t(b_content => utl_raw.cast_to_raw(p_json));
end;
begin
v_coll := dbms_soda.create_collection('lostBaggage');
v_n := v_coll.insert_one(doc('{"tag":"DXB123456","flight":"NM150","status":"found"}'));
v_n := v_coll.insert_one(doc('{"tag":"LHR987654","flight":"NM101","status":"missing"}'));
dbms_output.put_line('documents: ' || v_coll.find().count);
-- query by example: the documents whose status is "missing"
v_cur := v_coll.find().filter('{"status":"missing"}').get_cursor;
while v_cur.has_next loop
v_doc := v_cur.next;
dbms_output.put_line('missing: ' || json_value(v_doc.get_json, '$.tag'));
-- replace it by its key, which SODA generated
v_n := v_coll.find().key(v_doc.get_key)
.replace_one(doc('{"tag":"LHR987654","flight":"NM101","status":"found"}'));
end loop;
v_ok := v_cur.close;
v_n := v_coll.find().filter('{"status":"found"}').remove;
dbms_output.put_line('removed: ' || v_n || ', left: ' || v_coll.find().count);
end;
/
select column_value as collections from dbms_soda.list_collection_names();Output:
documents: 2 missing: LHR987654 removed: 2, left: 0 PL/SQL procedure successfully completed. COLLECTIONS ______________ lostBaggage
- CREATE_COLLECTION creates the collection and its table; INSERT_ONE adds two documents, each with a key SODA generates.
- The filter {"status":"missing"} is a query-by-example: it finds the documents with that field value.
- REPLACE_ONE, found by the document's key, marks the bag as found, and REMOVE deletes all found bags.
The example drops the collection afterward.
Things to Know
- SODA operations are transactional; commit to keep the changes.
- Filters support operators such as $gt, $in, and $and, written as JSON.
- A collection is a table underneath, so SQL can query the same documents with JSON functions.
Related Guides
Conclusion
DBMS_SODA treats JSON documents as collections in PL/SQL: insert, find by key or by example, replace, and remove, all without SQL. Use it when code thinks in documents, and SQL when you need joins and reports over them.
See my articles more often when you search Google.
