DBA Hub

📋Steps in this guide1/12

SQLcl : Simple Oracle Document Access (SODA) for SQLcl

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

oracle miscconfigurationintermediate
by OracleDba
14 views
1

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 command. You can get the full usage of the SODA integration using the command in SQLcl. If this doesn't give you a result similar to the following, you need a newer version of SQLcl. The commands are case sensitive. The keyword is not case sensitive, but the commands following it are case sensitive. In the following sections you will see that object names are case sensitive.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
SQL> soda create TestCollection1;

 Failed to execute: soda create TestCollection1

SQL>

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>

-- Works
SODA list
soda list

-- Fails
soda LIST
soda List
2

Create a Test Database User

We need a new database user for our testing. We'll run all the examples from this user.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
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;

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

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.
4

Create a Collection

Create a new collection using the command. 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. This is essentially a table holding key-value pairs, with the key being the column and the value being the column. Because the table name matches the collection name, we can create another collection with a similar name, but using a different case. You may be accessing these collections from URLs, so make sure the collection name is URL friendly.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SQL>
soda create TestCollection1
Successfully created collection: TestCollection1

SQL>

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>

SQL>
soda create TESTCOLLECTION1
Successfully created collection: TESTCOLLECTION1

SQL>
5

List All Collections

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
SQL>
soda list
List of collections:

        TESTCOLLECTION1
        TestCollection1

SQL>
6

Drop a Collection

The command removes a collection. The supporting table has been removed from the schema.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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>

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

SQL>
7

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).
8

Create a Document

The 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. 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. We can see a row containing the document has been added to the associated table. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
{
  "employees": [ 
    { "employee_number": 7369, "employee_name": "SMITH", "department_number": 20 }, 
    { "employee_number": 7499, "employee_name": "ALLEN", "department_number": 30 } 
  ]
}

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>

SELECT COUNT(*) FROM "TestCollection1";

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

1 row selected.

SQL>

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>
9

Retrieve Document

The command allows us to retrieve some or all of the documents in a collection. The flag gives us the key value and the date it was created. The option allows us to retrieve a specific document by key. The option allows us to retrieve multiple documents based on a list of keys. The option allows us to retrieve multiple documents based on QBE (Query By Example) .

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
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>

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>

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>

SQL>
soda get TestCollection1 -f {"fruit": "apple"}
Key:             4D606ABF36C04E37BCDE088B30C4778D
Content:         {"fruit": "apple"}
-----------------------------------------

 1 row selected.

SQL>
10

Update a Document

The 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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>
11

Delete a Document

The command deletes a document from the collection. Here we check the contents of the collection, delete a specific document and check the contents again. We could have used the or option as we did for the command.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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>
12

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. We've not committed any data changes, so we can remove the data by rolling back all the changes. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL> soda count TestCollection1

 1 row selected.

SQL>

SQL> rollback;

Rollback complete.

SQL> soda count TestCollection1

 No matching records found.

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!