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.
