Home » Articles » Misc » Here

SQLcl : Simple Oracle Document Access (SODA) for SQLcl

This article gives an overview of the integration of Simple Oracle Document Access (SODA) in SQLcl.

• Before We Start
• Create a Test Database User
• Collections
  
  • Create a Collection
  • List All Collections
  • Drop a Collection
• Documents
  
  • Create a Document
  • Retrieve Documents
  • Update a Document
  • Delete a Document
• Commit

Related articles.

• Simple Oracle Document Access (SODA) for SQLcl
• Oracle REST Data Services (ORDS) : Simple Oracle Document Access (SODA) for REST
• Simple Oracle Document Access (SODA) for PL/SQL in Oracle Database 18c
• SQLcl : All Articles
• Oracle REST Data Services (ORDS) : All Articles

Before We Start

For the examples in this article to work you need a database that has ORDS configured against it. Without that you will get errors from everything except the soda help command.

SQL> soda create TestCollection1;

 Failed to execute: soda create TestCollection1

SQL>

You can get the full usage of the SODA integration using the help soda command in SQLcl. If this doesn't give you a result similar to the following, you need a newer version of SQLcl.

SQL> help soda
SODA
------

SODA allows schemaless application development using the JSON data model.
         SODA create <collection_name>
              Create a new collection

         SODA list
              List all the collections

         SODA get <collection_name> [-all | -f | -k | -klist] [{<key> | <k1> <k2> ... > | <qbe>}]
              List documents the collection
              Optional arguments:
                    -all    list the keys of all docs in the collection
                    -k      list docs matching the specific <key>
                    -klist  list docs matching the list of keys
                    -f      list docs matching the <qbe>

         SODA insert <collection_name> <json_str | filename>
              Insert a new document within a collection

         SODA drop <collection_name>
              Delete existing collection

         SODA count <collection_name> [<qbe>]
              Count # of docs inside collection.
              Optional <qbe> returns # of matching docs

         SODA replace <collection_name> <oldkey> <new_{str|doc}>
              Replace one doc for another

         SODA remove <collection_name> [-k | -klist |-f] {<key> | <k1> <k2> ... | <qbe>}
              Remove doc(s) from collection
              Optional arguments:
                    -k     remove doc in collection matching the specific <key>
                    -klist remove doc in collection matching the list <key1> <key2> ... >
                    -f     remove doc in collection matching <qbe>


SQL>

The commands are case sensitive. The SODA keyword is not case sensitive, but the commands following it are case sensitive.

-- Works
SODA list
soda list

-- Fails
soda LIST
soda List

In the following sections you will see that object names are case sensitive.

Create a Test Database User

We need a new database user for our testing.

CONN sys/SysPassword1@//localhost:1521/pdb1 AS SYSDBA

DROP USER sodauser CASCADE;
CREATE USER sodauser IDENTIFIED BY sodauser1
  DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
  
GRANT CREATE SESSION, CREATE TABLE TO sodauser;
GRANT SODA_APP TO sodauser;

We'll run all the examples from this user.

CONN sodauser/sodauser1@//localhost:1521/pdb1

Collections

As the name suggests, collections are a way of grouping documents. It probably makes sense to define separate collections for different types of documents, but there is nothing to stop you keeping a variety of document types in a single collection.

Create a Collection

Create a new collection using the soda create command.

SQL> soda create TestCollection1
Successfully created collection: TestCollection1

SQL>

A table has been created in the test schema to support the collection. The table name is case sensitive, so you will have to double-quote the table name.

DESC "TestCollection1"
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 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

SQL>

This is essentially a table holding key-value pairs, with the key being the ID column and the value being the JSON_DOCUMENT column.

Because the table name matches the collection name, we can create another collection with a similar name, but using a different case.

SQL> soda create TESTCOLLECTION1
Successfully created collection: TESTCOLLECTION1

SQL>

You may be accessing these collections from URLs, so make sure the collection name is URL friendly.

List All Collections

The soda list command lists all the collections available to the current user.

SQL> soda list
List of collections:

        TESTCOLLECTION1
        TestCollection1

SQL>

Drop a Collection

The soda drop command removes a collection.

SQL> soda list
List of collections:

        TESTCOLLECTION1
        TestCollection1

SQL>


SQL> soda drop TESTCOLLECTION1
Successfully dropped: TESTCOLLECTION1

SQL>


SQL> soda list
List of collections:

        TestCollection1

SQL>

The supporting table has been removed from the schema.

DESC "TESTCOLLECTION1"
ERROR:
ORA-04043: object "TESTCOLLECTION1" does not exist

SQL>

Documents

A document is a combination of a JSON document you wish to persist in a collection, along with some document metadata, including a document identifier (ID).

Create a Document

The soda insert command is used to create a new document. This can be from a file or an inline JSON document.

Create a new file called "employees.json" with the following contents.

{
  "employees": [ 
    { "employee_number": 7369, "employee_name": "SMITH", "department_number": 20 }, 
    { "employee_number": 7499, "employee_name": "ALLEN", "department_number": 30 } 
  ]
}

Check the current contents of the collection, create a new document in the collection from the file, then check the contents of the collection again.

SQL> soda count TestCollection1

 No matching records found.

SQL>


SQL> soda insert TestCollection1 C:\temp\employees.json
Json String inserted successfully.

Successfully inserted file: C:\temp\employees.json

SQL>


SQL> soda count TestCollection1

 1 row selected.

SQL>

We can see a row containing the document has been added to the associated table.

SELECT COUNT(*) FROM "TestCollection1";

  COUNT(*)
----------
         1

1 row selected.

SQL>

Remember, the whole document is being added as a key-value pair. The data is not being exploded into separate columns and rows. As a result, we can run the same command to create the document multiple times with no errors. Each time, the payload will be used to create a new document.

We can also use the same collection to hold documents of completely different structures if we want. In this example we use an inline JSON definition.

SQL> soda count TestCollection1

 1 row selected.

SQL>


SQL> soda insert TestCollection1 {"fruit": "apple"}
Json String inserted successfully.

SQL>
 

SQL> soda count TestCollection1

 2 rows selected.

SQL>

Retrieve Document

The soda get command allows us to retrieve some or all of the documents in a collection. The -all flag gives us the key value and the date it was created.

SQL> soda get TestCollection1 -all
        KEY                                             Created On

        B58C675044F84408A8F058E71D1F38F1                2020-08-12T20:03:14.437315Z
        45E8861FAF834353979DD74659B6E1E7                2020-08-12T20:03:52.907795Z

 2 rows selected.

SQL>

The -k option allows us to retrieve a specific document by key.

SQL> soda get TestCollection1 -k B58C675044F84408A8F058E71D1F38F1

Key:             B58C675044F84408A8F058E71D1F38F1
Content:         {
  "employees": [
    { "employee_number": 7369, "employee_name": "SMITH", "department_number": 20 },
    { "employee_number": 7499, "employee_name": "ALLEN", "department_number": 30 }
  ]
}
-----------------------------------------

 1 row selected.

SQL>

The -klist option allows us to retrieve multiple documents based on a list of keys.

SQL> soda get TestCollection1 -klist B58C675044F84408A8F058E71D1F38F1 45E8861FAF834353979DD74659B6E1E7

Key:             45E8861FAF834353979DD74659B6E1E7
Content:         {"fruit": "apple"}
-----------------------------------------

Key:             B58C675044F84408A8F058E71D1F38F1
Content:         {
  "employees": [
    { "employee_number": 7369, "employee_name": "SMITH", "department_number": 20 },
    { "employee_number": 7499, "employee_name": "ALLEN", "department_number": 30 }
  ]
}
-----------------------------------------

 2 rows selected.

SQL>

The -f option allows us to retrieve multiple documents based on QBE (Query By Example).

SQL> soda get TestCollection1 -f {"fruit": "apple"}

Key:             4D606ABF36C04E37BCDE088B30C4778D
Content:         {"fruit": "apple"}
-----------------------------------------

 1 row selected.

SQL>

Update a Document

The soda replace command allows us to update an existing document with a new document. The new document can be loaded from a file or defined inline. In the following example we check the JSON value for a specific document, replace the JSON and check it again.

SQL> soda get TestCollection1 -klist 45E8861FAF834353979DD74659B6E1E7

Key:             45E8861FAF834353979DD74659B6E1E7
Content:         {"fruit": "apple"}
-----------------------------------------

 1 row selected.

SQL>


SQL> soda replace TestCollection1 45E8861FAF834353979DD74659B6E1E7 {"fruit": "banana"}
45E8861FAF834353979DD74659B6E1E7
Json String replaced successfully.

SQL>


SQL> soda get TestCollection1 -klist 45E8861FAF834353979DD74659B6E1E7

Key:             45E8861FAF834353979DD74659B6E1E7
Content:         {"fruit": "banana"}
-----------------------------------------

 1 row selected.

SQL>

Delete a Document

The soda remove command deletes a document from the collection. Here we check the contents of the collection, delete a specific document and check the contents again.

SQL> soda count TestCollection1

 2 rows selected.

SQL>


SQL> soda remove TestCollection1 -k 45E8861FAF834353979DD74659B6E1E7
Successfully removed 1 record.

SQL>


SQL> soda count TestCollection1

 1 row selected.

SQL>

We could have used the -klist or -f option as we did for the soda get command.

Commit

Creation of collections include an implicit commit, as they create tables to support the collection. All the data interactions don't commit by default.

SQL> soda count TestCollection1

 1 row selected.

SQL>

We've not committed any data changes, so we can remove the data by rolling back all the changes.

SQL> rollback;

Rollback complete.

SQL> soda count TestCollection1

 No matching records found.

SQL>

For more information see:

• Working with Oracle SQLcl
• Simple Oracle Document Access (SODA) for SQLcl
• Oracle REST Data Services (ORDS) : Simple Oracle Document Access (SODA) for REST
• Simple Oracle Document Access (SODA) for PL/SQL in Oracle Database 18c
• SQLcl : All Articles
• Oracle REST Data Services (ORDS) : All Articles

Hope this helps. Regards Tim...

Back to the Top.

Created: 2020-08-13  Updated: 2022-11-09

Contact Us

Home | Articles | Scripts | Blog | Certification | Videos | Misc | About

About Tim Hall
Copyright & Disclaimer