Simple Oracle Document Access (SODA) installation and usage

Preamble

Simple Oracle Document Access (SODA) is a document store like any other NoSQL document store database (MongoDB to name most used one) base on a feature we have seen called Oracle REST Data Services (ORDS). As my test environment is still there this is perfect timing to continue with SODA.

SODA allows you to store, retrieve and manipulate documents without speaking SQL fluently.

Testing has been done on a virtual machine running Oracle Linux Server release 7.5 and Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit. ORDS version is:

[oracle@server1 ords]$ java -jar ords.war version
Oracle REST Data Services 18.1.1.95.1251

SODA setup

With DBA account grant SODA_APP role to your test account:

SQL> GRANT soda_app TO hr;
 
GRANT succeeded.

With HR account, following the official documentation:

SQL> EXEC ords.create_role('SODA Developer');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC ords.create_privilege(p_name => 'oracle.soda.privilege.developer', p_role_name => 'SODA Developer');
BEGIN ords.create_privilege(p_name => 'oracle.soda.privilege.developer', p_role_name => 'SODA Developer'); END;
 
*
ERROR AT line 1:
ORA-00001: UNIQUE CONSTRAINT (ORDS_METADATA.PRIVILEGES_UNQ_NAME) violated
ORA-06512: AT "ORDS_METADATA.ORDS_SERVICES_INTERNAL", line 1062
ORA-06512: AT "ORDS_METADATA.ORDS_SERVICES_INTERNAL", line 1112
ORA-06512: AT "ORDS_METADATA.ORDS_SERVICES", line 466
ORA-06512: AT "ORDS_METADATA.ORDS", line 357
ORA-06512: AT "ORDS_METADATA.ORDS", line 377
ORA-06512: AT line 1
 
 
SQL> EXEC ords.create_privilege_mapping('oracle.soda.privilege.developer', '/soda/*');
BEGIN ords.create_privilege_mapping('oracle.soda.privilege.developer', '/soda/*'); END;
 
*
ERROR AT line 1:
ORA-00001: UNIQUE CONSTRAINT (ORDS_METADATA.SEC_PRIV_MAP_PATTERN_UNQ) violated
ORA-06512: AT "ORDS_METADATA.ORDS_SERVICES_INTERNAL", line 1705
ORA-06512: AT "ORDS_METADATA.ORDS_SERVICES_INTERNAL", line 1683
ORA-06512: AT "ORDS_METADATA.ORDS", line 538
ORA-06512: AT "ORDS_METADATA.ORDS", line 553
ORA-06512: AT line 1

Apparently with ORDS 18.1 all is already in place and ready to use (the role creation is re-runnable)…

If you want to get rid of any security you can simply execute:

SQL> EXEC ords.delete_privilege_mapping('oracle.soda.privilege.developer','/soda/*');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> COMMIT;
 
COMMIT complete.

From official documentation:

ORDS supports many different authentication mechanisms. JSON document store REST services are intended to be used in server-to-server interactions. Therefore, two-legged OAuth (the client-credentials flow) is the recommended authentication mechanism to use with the JSON document store REST services. However, other mechanisms such as HTTP basic authentication, are also supported.

So used the lazy approach with:

[oracle@server1 ords]$ java -jar ords.war user soda_user "SODA Developer"
Enter a password for user soda_user:
Confirm password for user soda_user:
Apr 27, 2018 11:13:53 AM oracle.dbtools.standalone.ModifyUser execute
INFO: Created user: soda_user in file: /u01/app/oracle/product/12.2.0/dbhome_1/ords/config/ords/credentials

You can know confirm SODA is up and running with (still using Insomnia as REST client):

SODA testing

Collection creation/deletion

Creating Collection01 with:

Can be confirmed with SQL:

SQL> DESC "Collection01"
 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

Can also be confirmed with:

To delete the collection use the exact same url and DELETE method…

Use https://server1.domain.com:8443/ords/pdb1/hr/soda/latest/ url to list all available collections.

Managing document in a collection

I will use and extract of the EMPLOYEES table of HR schema. Using well known Steven King user in item01.json file:

{
  "employee_id": 100,
  "first_name": "Steven",
  "last_name": "King",
  "email": "SKING",
  "phone_number": "",
  "phone_number": [
    {
      "type": "Office",
      "number": "515.123.4567"
    }
  ],
  "hire_date": "2003-06-16T22:00:00Z",
  "job_id": "AD_PRES",
  "salary": 24000,
  "commission_pct": null,
  "manager_id": null,
  "department_id": 90
}

To insert my above document use POST method specifying the file as body and an header of “application/json”:

You can also check in SQL that it has been done:

SQL> SET lines 200
SQL> col id FOR a40
SQL> col created_on FOR a30
SQL> SELECT id, created_on FROM "Collection01";
 
ID                                       CREATED_ON
---------------------------------------- ------------------------------
CE580C73CB254596A8595B83BA1ED409         27-APR-18 10.41.21.840041 AM

In you want to insert multiple documents at same time add “?action=insert” to the url…

Needless to say hat the only type of document you can store are JSON documents. If you try to change the header to “application/pdf” and try to POST file you get below error message:

{
  "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html#sec10.4.16",
  "status": 415,
  "title": "Unsupported content type application/pdf.",
  "o:errorCode": "REST-02002"
}

To use other type of file you could store a reference to those file located in a directory or in another table in a BLOB column…

To retrieve the inserted document I use https://server1.domain.com:8443/ords/pdb1/hr/soda/latest/Collection01/CE580C73CB254596A8595B83BA1ED409 url with GET method. You must specify the ID field after the collection name:

To delete a document use same exact url as above with DELETE method.

If you do not specify the ID field you retrieve all the document of your collection…Which might not be efficient versus number of documents in your collection…

You can filter what you want to display when fetching all documents. Examples:

https://server1.domain.com:8443/ords/pdb1/hr/soda/latest/Collection01?fields=id
https://server1.domain.com:8443/ords/pdb1/hr/soda/latest/Collection01?fields=all&limit=10

Refer to GET collection documentation for a complete list of possible parameters…

You can also put your query in a JSON file like example below. Do not forget to append ?action=query at the end of your url or it will create a document instead:

When looking at table created to store the collection:

SQL> SET LONG 2000000
SQL> SET pagesize 1000
SQL> col ddl FOR a100
SQL> SELECT DBMS_METADATA.GET_ddl('TABLE','Collection01','HR') AS ddl FROM dual;
 
DDL
----------------------------------------------------------------------------------------------------
 
  CREATE TABLE "HR"."Collection01"
   (    "ID" VARCHAR2(255) NOT NULL ENABLE,
        "CREATED_ON" TIMESTAMP (6) DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL ENABLE,
        "LAST_MODIFIED" TIMESTAMP (6) DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL ENABLE,
        "VERSION" VARCHAR2(255) NOT NULL ENABLE,
        "JSON_DOCUMENT" BLOB,
         CHECK ("JSON_DOCUMENT" IS json format json strict) ENABLE,
         PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
 LOB ("JSON_DOCUMENT") STORE AS SECUREFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
  CACHE  NOCOMPRESS  KEEP_DUPLICATES
  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))

We see that the JSON_DOCUMENT field has the useful constraint to check it contains valid JSON format. So we can even use the so called Simple Dot-Notation Access to JSON Data of your collection:

SQL> col employee_id FOR a15
SQL> col first_name FOR a15
SQL> col last_name FOR a15
SQL> SELECT c.json_document.employee_id, c.json_document.first_name, c.json_document.last_name FROM "Collection01" c;
 
EMPLOYEE_ID     FIRST_NAME      LAST_NAME
--------------- --------------- ---------------
100             Steven          King

You cannot update partially a document, you have to replace its whole content with PUT method.

References

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts

IT World