Does Oracle Database provide a NoSQL style API? If yes, how does it work and how to get started? The answer to this question is the Oracle framework with the interesting name SODA (short for Simple Oracle Document Access) which has nothing to do with a soft drink ;).

With SODA you can create and store collections of documents in Oracle Database, retrieve them, and query them, without needing to know the Structured Query Language (SQL) or how the documents are stored in the database. Documents can be fetched from the database by key lookup or by using query-by-example (QBE) pattern-matching. However, access via SQL does allow the use of advanced Oracle Database functionality such as analytics for reporting. SODA can be used with Oracle Databases available on premises or in the cloud. There is also an integration in Oracle tools such as SQLcl or Database Actions. Oracle SODA implementations are available in Node.js, Java, PL/SQL, Oracle Call Interface and via REST. SODA for REST can itself be accessed from almost any programming language. It maps SODA operations to Uniform Resource Locator (URL) patterns.

How can you start with SODA? And what do you need to know? You need a database user account, because SODA uses a SQL schema to store documents. The SODA abstractions hide the complexities of SQL and client programming – the term collections and documents are used instead. What are collections and documents? A collection contains documents persisted in the Oracle Database schema (this is similar to the table or view concept); documents are part of a collection. A document (similar to a row in a table) component includes not only the JSON content but also a key and additional meta data.

With SODA you can:  

  • create and drop a collection
  • list collections
  • create/insert/remove documents
  • find documents with Query By Example (QBE) patterns also called filter specifications

Before you start you need to provide the required specific SODA driver implementation. After that you create a database user with storage quotas, CREATE SESSION and CREATE TABLE privileges and EXECUTE privileges on the special packages DBMS_SODA. Advanced users who are using Oracle sequences for keys will also need the CREATE SEQUENCE privilege.

In the posting we learn about the follwing SODA implementations and usage scenarios:

Oracle SODA and Database Actions

Let’s begin with an example in Autonomous Database. This is the simplest way to demonstrate the functionality of the SODA API. SODA for REST is already installed and you can immediately start using e.g. the Oracle tool Database Actions. 
What is database Actions? Formerly known as SQL Developer Web, Database Actions is delivered as a single-page web application, powered by Oracle REST Data Services (ORDS). It offers, a worksheet for running queries and scripts, the ability to manage and browse your data dictionary, a REST development environment for your REST APIs and AUTOREST enabled objects, an interface for Oracle’s JSON Document Store (SODA), a data model reporting solution, and more.  Database Actions is deployed automatically with Oracle Autonomous Database Cloud Services and is slso available from any customer managed ORDS installation and their Oracle Databases. It supports all database versions ( from 11gR2 to 21c) in multitanent or classic architecture and can be deployed for one or multiple Oracle Databases. More information is provided on the oracle website for Database Actions.


Image 1: Database Actions Launchpad 

Database Actions runs in Oracle REST Data Services and access to it is provided through schema-based authentication. To access Database Actions, you must sign in as a database user whose schema has been enabled for Database Actions. In Oracle Autonomous Database Databases, the ADMIN user is pre-enabled. To enable another database user’s schema, enable a database user to sign into Database Actions, run the following code as the ADMIN user. In any other Oracle databases environments use a privileged user.

BEGIN
 ords_admin.enable_schema( p_enabled => TRUE, p_schema => 'schema-name', 
 p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'schema-alias', p_auto_rest_auth => NULL );
commit;
END;
/

Image 2: SQL Interface: SODA help command

To get an overview of the functions, use the SODA command help. You can create or remove a collection, insert documents and of course find documents. In addition you can index the content to get more performance or more functionality.  Here are some examples to show the usage. 

Delete an existing SODA collection musiccollection.

soda drop musiccollection;

Create a new SODA collection musiccollection.

soda create musiccollection;

Insert a JSON document into the collection musiccollection.

soda insert musiccollection {"name": "Led Zeppelin","img_url": "http://static.thetoptens.com/img/lists/335.jpg","info": "Led Zeppelin were an English rock band formed in London in 1968. The group consisted of Robert Plant (Vocal), Jimmy Page (Guitar), John Paul Jones (Bass, Keyboard) and John Bonham (Drums). The heavy, guitar-driven sound, rooted in blues and psychedelia on their early albums, has earned them recognition as one of the progenitors of heavy metal.","genre":"rock"};

To retrieve information, use LIST and GET arguments.

List all the collections.

soda list
List of collections:
  musiccollection
  myPLSColl

List documents where the name attribute is equal to “Queen”.

soda get musiccollection -f {"name": "Queen"};

Key:         5C49470AF3614561B09D53EC6B43C790
Content:     {"name":"Queen","img_url":"http://static.thetoptens.com/img/lists/587lg.jpg","info":"Queen are a British rock group formed in 1970. Members were Freddie Mercury (Vocals and Piano), Brian May (Guitar, Vocals), Roger Taylor (Drums, Vocals), and John Deacon (Bass Guitar, Vocals). Before forming into Queen, Brian May and Roger Taylor had played together in a band named Smile. Freddie Mercury was a fan of Smile and encouraged them to experiment with more elaborate stage and recording techniques. He then joined the band in 1970, suggested Queen as a new band name, and adopted his familiar stage name. John Deacon was also recruited to complete the lineup.Their release of the album A Night at the Opera in 1975, brought them international success.They entered the mainstream with the track Bohemian Rhapsody, which stayed at number one in the UK for nine weeks and popularised the music video."}
-----------------------------------------
 1 row selected. 

In the Oracle SODA guide you will find more syntax and examples.

Oracle SODA and SQLcl

Do you know SQLcl? Oracle SQL Developer Command Line (short SQLcl) is a free command java line interface for Oracle Database that combines the power of SQL*Plus and SQL Developer. It allows you to interactively or batch execute SQL and PL/SQL. SQLcl provides in-line editing, statement completion, and command recall for a feature-rich experience. In the meantime it provides additional support for APEX, Data Modeler, Datapump, Data Guard Broker, OCI, Cloud Object store and also SODA. 

To get the most recent version of SQLcl, download the .ZIP file from OTN, extract it, and start a terminal/cmd session and run the ‘SQL’ program in the \bin directory. If you have at least a Java Runtime Engine (JRE) 11.0.9 on your machine, you’ll be ready to go with SQLcl 22.1. 
After a connection with the database you may start using SODA. The next esample shows a connection with an Autonomous Database ATP.

SQL> set cloudconfig C:\data\sqlcl221\sqlcl\Wallet_ATPUS.zip
SQL> connect admin@atpus_low
Password? (**********?) *************
Connected.

To get an overview of all available SQLcl commands also use the command help.

SQL> help
For help on a topic type help <topic>
List of Help topics available:
/                @                @@               ACCEPT           ALIAS
APEX             APPEND           ARBORI           ARCHIVE_LOG      BREAK
BRIDGE           BTITLE           CD               CHANGE           CLEAR
CLOUDSTORAGE     CODESCAN         COLUMN           COMPUTE          CONNECT
COPY             CS               CTAS             DATAPUMP         DBCCRED
DDL              DEFINE           DEL              DESCRIBE         DG
DISCONNECT       EDIT             EXECUTE          EXIT             FIND
FORMAT           GET              HISTORY          HOST             INFORMATION
INPUT            LIQUIBASE        LIST             LOAD             MKSTORE
MODELER          NET              OCI              OERR             ORAPKI
PASSWORD         PAUSE            PRINT            PROMPT           QUIT
REMARK           REPEAT           RESERVED_WORDS   REST             RUN
SAVE             SCRIPT           SET              SETERRORL        SHOW
SHUTDOWN         SODA             SPOOL            SSHTUNNEL        START
STARTUP          STORE            TIMING           TNSPING          TOSUB
TTITLE           UNDEFINE         UNLOAD           VARIABLE         VAULT
WHENEVER         WHICH            XQUERY
SQL>

Let’s try some additional commands to retrieve information about our collection musiccollection.

Count the number of documents inside the collection musiccollection.

SQL> soda count musiccollection
4 rows selected.

Get the documents in the collection musiccollection where the “name” attribute starts with “T”.

SQL> soda get musiccollection -f {"name":{"$startsWith" : "T"}};

Key:             957C097E502644919D80E0DB48535EAE
Content:         {"name":"The Rolling Stones","img_url":"http://static.thetoptens.com/img/lists/331lg.jpg","info":"The Rolling Stones are an English rock band formed in London in 1962. The first settled line-up consisted of Brian Jones (guitar, harmonica), Ian Stewart (piano), Mick Jagger (lead vocals, harmonica), Keith Richards (guitar), Bill Wyman (bass) and Charlie Watts (drums).Their best works include songs like Paint it Black , Sympathy for the Devil, Satisfication(Which ranked no.2 in The Rolling Stones MagazineÆ s best songs of all time list).The Rolling Stones were inducted into the Rock and Roll Hall of Fame in 1989, and the UK Music Hall of Fame in 2004. In 2012, the band celebrated its 50 th anniversary.","genre":"rock"}
-----------------------------------------
1 row selected.

Now let’s try a search on fields using the $contains operator. The Query-by-example (QBE) operator $contains performs full-text search of JSON documents in a SODA collection. A QBE field whose value is an object with a $contains operator matches a JSON document if the document has that field with a full-word string value or a full-number value that matches the string operand of $contains somewhere, including in an array element. Let’s try to find the music band with  “stones” in the field name.

SQL> soda get musiccollection -f {"name" : { "$contains" : "stones" } }
Failed to execute: soda get musiccollection -f {"name" : { "$contains" : "stones" } }
java.sql.SQLException: ORA-40467: JSON_TEXTCONTAINS() cannot be evaluated without a JSON-enabled context index

As you can see, a special index is required to use full-text search with $contains. Let's create a JSON search index for the document collection with the following SQL command.

SQL> create search index idx_musiccollection on musiccollection (json_document) for json;
INDEX IDX_MUSICCOLLECTION created.

Once you have a search index for your collection, this simple QBE searches the  fields of all documents, case-insensitively, for the “name” attribute that contains the word “stones”

SQL> soda get musiccollection -f {"name" : { "$contains" : "stones" } }

Key:             957C097E502644919D80E0DB48535EAE
Content:         {"name":"The Rolling Stones","img_url":"http://static.thetoptens.com/img/lists/331lg.jpg","info":"The Rolling Stones are an English rock band formed in London in 1962. The first settled line-up consisted of Brian Jones (guitar, harmonica), Ian Stewart (piano), Mick Jagger (lead vocals, harmonica), Keith Richards (guitar), Bill Wyman (bass) and Charlie Watts (drums).Their best works include songs like Paint it Black , Sympathy for the Devil, Satisfication(Which ranked no.2 in The Rolling Stones MagazineÆ s best songs of all time list).The Rolling Stones were inducted into the Rock and Roll Hall of Fame in 1989, and the UK Music Hall of Fame in 2004. In 2012, the band celebrated its 50 th anniversary.","genre":"rock"}

-----------------------------------------
1 row selected.

Now search in the “info” attribute for the value “Black”.

SQL>  soda get musiccollection -f {"info" : { "$contains" : "Black" } };

Key:             957C097E502644919D80E0DB48535EAE
Content:         {"name":"The Rolling Stones","img_url":"http://static.thetoptens.com/img/lists/331lg.jpg","info":"The Rolling Stones are an English rock band formed in London in 1962. The first settled line-up consisted of Brian Jones (guitar, harmonica), Ian Stewart (piano), Mick Jagger (lead vocals, harmonica), Keith Richards (guitar), Bill Wyman (bass) and Charlie Watts (drums).Their best works include songs like Paint it Black , Sympathy for the Devil, Satisfication(Which ranked no.2 in The Rolling Stones MagazineÆ s best songs of all time list).The Rolling Stones were inducted into the Rock and Roll Hall of Fame in 1989, and the UK Music Hall of Fame in 2004. In 2012, the band celebrated its 50 th anniversary.","genre":"rock"}
-----------------------------------------

1 row selected.

SODA for PL/SQL

SODA for PL/SQL is a PL/SQL API that implements SODA. SODA for PL/SQL is an integral part of Oracle Database, starting with release 18c. The database is the only prerequisite for using SODA for PL/SQL. 

You can use SODA for PL/SQL to perform create, read (retrieve), update, and delete (CRUD) operations on documents of any kind, and you can use it to query JSON documents. The functionality is provided by the package DBMS_SODA. For example CREATE_COLLECTION creates a collection, DROP_COLLECTION drops a collection, LIST_COLLECTION_NAMES lists the collection names and OPEN_COLLECTION opens an existing collection.
Here are some simple examples to show the usage. In the first two scripts we create and drop a collection.  

Create a collection myPLSColl.

DECLARE
    collection  SODA_Collection_T;
BEGIN
    collection := DBMS_SODA.create_collection('myPLSColl')  
END;
/

Delete an existing SODA collection.

DECLARE
    status  NUMBER := 0;
BEGIN
    status := DBMS_SODA.drop_collection('myPLSColl');
END;
/

SODA for PL/SQL represents a document using an instance of PL/SQL object type SODA_DOCUMENT_T. This object is a carrier of document content and other document components, such as the document key. If you are familiar with object types and methods in Oracle Database you may easily understand the concept used.
The following example inserts a JSON document in the collection myPLSColl. < p=””> <>

< p=””> <>

declare
  collection SODA_COLLECTION_T;
  document SODA_DOCUMENT_T;
  ins_doc SODA_DOCUMENT_T;
begin
-- open a collection
collection:=DBMS_SODA.open_collection('myPLSColl');

document:=SODA_DOCUMENT_T(b_content=>utl_raw.cast_to_raw('{"name":"Queen"}'));

-- insert a collection
ins_doc:=collection.insert_one_and_get(document);

DBMS_OUTPUT.put_line('Inserted document components:');
DBMS_OUTPUT.put_line('Key:'||ins_doc.get_key);
DBMS_OUTPUT.put_line('Creationtimestamp:'||ins_doc.get_created_on);
DBMS_OUTPUT.put_line('Lastmodifiedtimestamp:'||ins_doc.get_last_modified);
DBMS_OUTPUT.put_line('Version:'||ins_doc.get_version);
END;
/

The output of this short PL/SQL script is:

Inserted document components:
Key:BFF6B0F0554B4FA9BFD5D65A53B1928C
Creationtimestamp:2022-04-08T10:39:29.168907Z
Lastmodifiedtimestamp:2022-04-08T10:39:29.168907Z
Version:78A3BCABABDF4FBDBF3CC3CCFA2A41EE

More examples and scripts can be found in SODA for PL/SQL Developer’s Guide and also in the LiveSQL Tutorial.

Oracle SODA and Python

cx_Oracle is a Python extension module that enables Python access to Oracle Database. It conforms to the Python Database API v2.0 Specification with a considerable number of additions and a couple of exclusions. cx_Oracle is typically installed from PyPI using pip. The Oracle Client libraries need to be installed separately. The libraries can be from an installation of Oracle Instant Client, from a full Oracle Client installation, or even from an Oracle Database installation. SODA requires Oracle Client 18.3 or higher and Oracle Database 18.1 and higher. 

In general cx_Oracle uses the following objects for SODA:

  • SODA Database Object: The top level object for cx_Oracle SODA operations. This is acquired from an Oracle Database connection. A SODA database is analogous to an Oracle Database user or schema, a collection is analogous to a table, and a document is analogous to a table row with one column for a unique document key, a column for the document content, and other columns for various document attributes.

  • SODA Collection Object: Represents a collection of SODA documents. By default, collections allow JSON documents to be stored. By default, the name of the Oracle Database table storing a collection is the same as the collection name. Note: Do not use SQL to drop the database table, since SODA metadata will not be correctly removed. Use the SodaCollection.drop() method instead.

  • SODA Document Object: Represents a document. Typically the document content will be JSON. The document has properties including the content, a key, timestamps, and the media type. By default, document keys are automatically generated.

  • SODA Document Cursor: A cursor object representing the result of the SodaOperation.getCursor() method from a SodaCollection.find() operation. It can be iterated over to access each SodaDoc.

  • SODA Operation Object: An internal object used with SodaCollection.find() to perform read and write operations on documents. This is an internal object that should not be directly accessed.

Here are two short examples to show how SODA for Python can be used. In the first example create a new SODA collection mycollection; it will open an existing collection if the name is already in use and insert a document into the collection.

import cx_Oracle
cx_Oracle.init_oracle_client(lib_dir="c:\Data\Python310\instantclient_21_3")
con = cx_Oracle.connect("ADMIN", "password", "atpus_low")
soda = con.getSodaDatabase()

collection = soda.createCollection("mycollection")
con.autocommit = True
content = {'name': 'Matilda', 'address': {'city': 'Melbourne'}}
returned_doc = collection.insertOneAndGet(content)
key = returned_doc.key
print('The key of the new SODA document is: ', key)

The result looks like …

C:\Data\Python310>python soda1.py
The key of the new SODA document is:  A6CE248F09114FA8BF802C9EB8F58D02

You can also search for documents using query-by-example syntax:

import cx_Oracle
cx_Oracle.init_oracle_client(lib_dir="c:\Data\Python310\instantclient_21_3")
con = cx_Oracle.connect("ADMIN", "password", "atpus_low")
soda = con.getSodaDatabase()

collection = soda.createCollection("mycollection")
print("Names matching 'Ma%'")
qbe = {'name': {'$like': 'Ma%'}}
for doc in collection.find().filter(qbe).getDocuments():
    content = doc.getContent()
    print(content["name"])

The result looks like …

C:\Data\Python310>python soda2.py
Names matching 'Ma%'
Matilda

The general recommendation for SODA applications is to turn on autocommit globally and use the SODA Metadata Cache to improve the performance – more information can be found in cx_Oracle documentation.

Monitoring

Oracle Database view USER_SODA_COLLECTIONS lists the basic features of all of your SODA collections, that is, all SODA collections created by the database user (database schema) that you are currently connected to the database as.

The view includes, for each collection, its metadata and its underlying database information, in particular, the collection name and the name and database schema of the table or view that backs the collection.

The following example shows the list of the created collections.

SQL>  select object_name, created_on, create_mode 
      from user_soda_collections;
      
       OBJECT_NAME                     CREATED_ON    CREATE_MODE
__________________ ______________________________ ______________
myPLSColl          08.04.22 10:20:00,378798000    DDL
MUSICCOLLECTION    06.04.22 13:18:43,821343000    DDL
MYCOLLECTION       07.04.22 15:49:33,694832000    DDL

By default, the name of the database table that underlies a document collection is derived from the collection name. If you want a different table name from that provided by default then use custom collection metadata to explicitly provide the name. You can use the usual SQL or SQLcl commands to get more information on the stored collections.

SQL> describe MUSICCOLLECTION
            Name       Null?              Typ
________________ ___________ ________________
ID               NOT NULL    VARCHAR2(255)
CREATED_ON       NOT NULL    TIMESTAMP(6)
LAST_MODIFIED    NOT NULL    TIMESTAMP(6)
VERSION          NOT NULL    VARCHAR2(255)
JSON_DOCUMENT                BLOB

In SQLcl try the INFO command to retrieve additional information. 

SQL> info musiccollection
TABLE: MUSICCOLLECTION
         LAST ANALYZED:2022-04-06 16:48:01.0
         ROWS         :4
         SAMPLE SIZE  :4
         INMEMORY     :DISABLED
         COMMENTS     :
Columns
NAME            DATA TYPE            NULL  DEFAULT    COMMENTS
*ID             VARCHAR2(255 BYTE)   No
 CREATED_ON     TIMESTAMP(6)         No    sys_extract_utc(SYSTIMESTAMP)
 LAST_MODIFIED  TIMESTAMP(6)         No    sys_extract_utc(SYSTIMESTAMP)
 VERSION        VARCHAR2(255 BYTE)   No
 JSON_DOCUMENT  BLOB                 Yes

Indexes
ADMIN.SYS_C0030132           UNIQUE        VALID                       ID               ADMIN.IDX_MUSICCOLLECTION    NONUNIQUE     VALID                       JSON_DOCUMENT  

Additional guidelines, recommendations or restrictions can be found in SODA Guidelines and Restrictions in the documentation.

Conclusion

It is quite easy to access and process JSON documents in Oracle database. You can quickly and directly perform CRUD operations with JSON documents even without to know SQL syntax and integrate them into your own program code. There are SODA APIs for all modern programming languages and interfaces.
The examples in the posting are intentionally kept short to demonstrate the easy usage of the different SODA implementations. There are additional contributions and sample scripts in the Oracle Database documention, blog postings or on github to start with.

More information