Use SODA from SQLcl

SQLcl provides SODA support using commands and scripting integration.

Topics

Get Started with SODA Using SQLcl Commands

Perform some basic SODA operations using SQLcl, including creating a collection, inserting documents, finding a select set of documents, committing the document insertions and dropping the collection.

Note:

For a complete list of all commands and additional information about each command, you can execute the soda help command within SQLcl.

To perform basic SODA operations using SQLcl.

Enable SODA for your service. See Enable SODA for Your Service.
Using Oracle SQLcl, connect to your Exadata Express service. See Connect SQLcl.
To create a collection named myColl, enter this command:
```
SQL> soda create myColl
```
Command results:
```
Successfully created collection: myColl
```
To add a document with content {"name" : "Alex"} to myColl, enter this command:
```
SQL> soda insert myColl {"name" : "Alex"}
```
Command results:
```
Json String inserted successfully
```
To add a document with content {"name" : "Vlad"} to myColl, enter this command:
```
SQL> soda insert myColl {"name" : "Vlad"}
```
Command results:
```
Json String inserted successfully
```
To find all documents in myColl that have a “name” field that starts with “A”, enter this command:
```
SQL> soda find myColl -f {"name" : { "$startsWith" : "A" }}
```
Command results:
```
Key:       BB325C9D97D7467FB34F20FEE3C3F091 
Content:    {"name" : "Alex"} 
----------------------------------------- 
 1 row selected. 
```
Note:
Because the key value is automatically generated, the key value for your results will be different. This example shows how to run a query-by-example (QBE), also known as a filter (that’s what the -f flag) means.
$startsWith is just one of the operators that can be used inside a QBE, the other ones are listed here: Filter Conditions in Oracle Database SODA for Java Developer’s Guide. For comprehensive information on using QBEs, see Using Filter Specifications (QBEs) with SODA for Java in Oracle Database SODA for Java Developer’s Guide.
To commit the document inserts, enter this command:
```
SQL> commit;
```
Command results:
```
  Commit complete.
```
To drop myColl, enter this command:
```
  SQL> soda drop myColl
```
Command results:
```
  Successfully dropped: myColl
```
Note:
All writes to a collection must be committed, otherwise the collection cannot be dropped.

Get Started with SODA Using SQLcl Scripts

You can access SODA for Java classes using SQLcl running Javascript. The following simple script illustrates using SODA for Java classes with Javascript in SQLcl. The script creates a collection, inserts a document, and finds the inserted document using a QBE (query-by-example).

For further details about Java classes, see SODA for Java Javadoc and Oracle Database SODA for Java Online Documentation.

To run a simple Javascript that performs some basic SODA operations:

Enable SODA for your service. See Enable SODA for Your Service.
Using Oracle SQLcl, connect to your Exadata Express service. See Connect SQLcl.

Copy the following script into a file named soda.sql.

script
ctx.write('Caffeinated SODA \n');

var OracleRDBMSClient = Java.type("oracle.soda.rdbms.OracleRDBMSClient");
var OracleDataSource = Java.type('oracle.jdbc.pool.OracleDataSource');
var cl = new OracleRDBMSClient();
var db = cl.getDatabase(conn);

ctx.write("Creating foo\n")
foo = db.admin().createCollection("foo");

var doc = db.createDocumentFromString("{ \"name\" : \"Alex\", \"friends\" : \"50\" }");
ctx.write("Inserted Document\n")
foo.insertAndGet(doc);

conn.commit();

var f = db.createDocumentFromString(JSON.stringify( {"name" : { "$startsWith" : "A" }}));
ctx.write("Searching...\n")
var c = foo.find().filter(f).getCursor();

while (c.hasNext()) { 
cDoc = c.next(); 
ctx.write(cDoc.getKey() + "\n"); 
ctx.write(cDoc.getContentAsString()); 
ctx.write("\n\n");
}
ctx.write("\n\n");
/

To execute the script file, enter this command from within SQLcl:

SQL> @soda.sql

Command results:

Caffeinated SODA 
Creating foo
Inserted Document
Searching...8AC72C7F5F2243899F56E1362B8DF689
{"name" : "Alex", "friends" : "50" }

Note:

If you run this script multiple times, the collection grows. Each time the script is executed, another JSON document is inserted. This is because createCollection(...) simply opens the collection if it already exists. To completely drop the collection created by the script, execute the soda drop foo command from SQLcl.