8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

Oracle REST Data Services (ORDS) : Simple Oracle Document Access (SODA)

This article gives an overview of the Simple Oracle Document Access (SODA) functionality of Oracle REST Data Services (ORDS), which allows you to use Oracle as a JSON document store.

Related articles.

Assumptions and Comments

This article assumes the following.

I wrote a simple HTML page, which can be used to test your local SODA services with the examples in this article. Download it here.

Database Patches

If you are using Oracle database 12.2 or later you can ignore this section. SODA will work without any additional patches.

The SODA functionality will not work in an unpatched 12.1.0.2 instance. To proceed you have two options.

The rest of this article will assume the SODA database patch is present.

Create a Test Database User

We need a new database user for our testing.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;

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;

Notice the grant for the SODA_APP role. Without the database patch this role will not be present.

Enable ORDS and SODA

There are a number of steps that are necessary to enable ORDS and the SODA functionality.

Installing ORDS configures a default database connection called "apex", available from the base URL "/ords/". We could use this existing database connection, but if you want to create a new database connection follow the instructions below.

$ cd /u01/ords
$ $JAVA_HOME/bin/java -jar ords.war setup --database pdb1
Enter the name of the database server [ol7-121.localdomain]:
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:
Enter the database service name [pdb1]:
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:2
Jun 18, 2016 12:49:50 PM oracle.dbtools.common.config.file.ConfigurationFilesBase update
INFO: Updated configurations: pdb1_pu
Jun 18, 2016 12:49:50 PM oracle.dbtools.rt.config.setup.SchemaSetup install
INFO: Oracle REST Data Services schema version 3.0.5.124.10.54 is installed.
$

We create a URL mapping to the new database connection.

$ cd /u01/ords
$ $JAVA_HOME/bin/java -jar ords.war map-url --type base-path /pdb1 pdb1
Jun 18, 2016 12:51:45 PM oracle.dbtools.url.mapping.file.MapURL execute
INFO: Creating new mapping from: [base-path,/pdb1] to map to: [pdb1,,]
$

Web services can now be referenced using the following base URL.

http://ol7-121.localdomain:8080/ords/pdb1/

Enable REST web services for the test schema. We could use any unique and legal URL mapping pattern for the schema, so it is not necessary to expose the schema name as we have done here.

CONN sodauser/sodauser1@pdb1

BEGIN
  ORDS.enable_schema(
    p_enabled             => TRUE,
    p_schema              => 'SODAUSER',
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => 'sodauser',
    p_auto_rest_auth      => FALSE
  );
    
  COMMIT;
END;
/

Web services from the schema can now be referenced using the following base URL.

Default Connection : http://ol7-121.localdomain:8080/ords/sodauser/
New Connection     : http://ol7-121.localdomain:8080/ords/pdb1/sodauser/

We are going to ignore any form of authentication for this article, to remove the following privilege mapping.

BEGIN
  ORDS.delete_privilege_mapping('oracle.soda.privilege.developer',
                                '/soda/*'
  );
  
  COMMIT;
END;
/

The base URL for all SODA actions below will be as follows.

http://ol7-121.localdomain:8080/ords/sodauser/soda/latest/

We are now ready to start.

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 by making a PUT call with the name of the new collection after the base SODA URL.

$ curl -i -X PUT http://ol7-121.localdomain:8080/ords/sodauser/soda/latest/TestCollection1
HTTP/1.1 201 Created
Server: Apache-Coyote/1.1
X-Frame-Options: SAMEORIGIN
Cache-Control: private,must-revalidate,max-age=0
Location: http://ol7-121.localdomain:8080/ords/sodauser/soda/latest/TestCollection1/
Content-Length: 0
Date: Mon, 04 Jul 2016 19:21:16 GMT

$

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

CONN sodauser/sodauser1@pdb1

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.

List All Collections

List the available collections by making a GET call using the base SODA URL, which returns a JSON document containing an array of collection definitions available in the schema.

$ curl -i -X GET http://ol7-121.localdomain:8080/ords/sodauser/soda/latest/
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
X-Frame-Options: SAMEORIGIN
Cache-Control: private,must-revalidate,max-age=0
Content-Type: application/json
Content-Length: 627
Date: Mon, 04 Jul 2016 19:23:00 GMT

{"items":[{"name":"TestCollection1","properties":{"schemaName":"SODAUSER","tableName":"TestCollection1","keyColumn":{"name":"ID","sqlType":"VARCHAR2","maxLength":255,"assignmentMethod":"UUID"},"contentColumn":{"name":"JSON_DOCUMENT","sqlType":"BLOB","compress":"NONE","cache":true,"encrypt":"NONE","validation":"STANDARD"},"versionColumn":{"name":"VERSION","type":"String","method":"SHA256"},"lastModifiedColumn":{"name":"LAST_MODIFIED"},"creationTimeColumn":{"name":"CREATED_ON"},"readOnly":false},"links":[{"rel":"canonical","href":"http://ol7-121.localdomain:8080/ords/sodauser/soda/latest/TestCollection1"}]}],"more":false}
$

Delete a Collection

Drop a collection by making a DELETE call using the full collection URL, as shown below.

$ curl -i -X DELETE http://ol7-121.localdomain:8080/ords/sodauser/soda/latest/TestCollection1
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
X-Frame-Options: SAMEORIGIN
Cache-Control: private,must-revalidate,max-age=0
Content-Length: 0
Date: Mon, 04 Jul 2016 19:23:25 GMT

$

The table has been removed from the schema.

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

SQL>

The remaining examples assume the "TestCollection1" collection is present, so if you deleted it previously, recreate it.

$ curl -i -X PUT http://ol7-121.localdomain:8080/ords/sodauser/soda/latest/TestCollection1

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). The document identifier can be assigned by the client, but in these examples we will rely on the server generating them for us.

Create a Document

A new document is added to the collection using a POST call to the collection URL, passing a JSON document as the payload.

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

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

The data is used as a payload in a POST call to the collection URL.

$ curl -i -X POST --data-binary @newdoc1.json -H "Content-Type: application/json" http://ol7-121.localdomain:8080/ords/sodauser/soda/latest/TestCollection1
HTTP/1.1 201 Created
Server: Apache-Coyote/1.1
X-Frame-Options: SAMEORIGIN
Cache-Control: private,must-revalidate,max-age=0
Location: http://ol7-121.localdomain:8080/ords/sodauser/soda/latest/TestCollection1/E55470C329DE407C8A8785D957392A18
Content-Type: application/json
Content-Length: 238
Date: Mon, 04 Jul 2016 19:24:15 GMT

{"items":[{"id":"E55470C329DE407C8A8785D957392A18","etag":"F5737FB3015053262992DFDBD7664B84DC6E90E39B5972301B5787279D6D8D8F","lastModified":"2016-07-04T19:24:15.743977Z","created":"2016-07-04T19:24:15.743977Z"}],"hasMore":false,"count":1}
$

The output of the call includes metadata about the document that was created, including the ID of the document.

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

CONN sodauser/sodauser1@pdb1

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. Create a new file called "newdoc2.json" with the following contents.

{
  "department": {
    "department_number": 10,
    "department_name": "ACCOUNTING",
    "employees": [
      { "employee_number": 7782, "employee_name": "CLARK" },
      { "employee_number": 7839, "employee_name": "KING" },
      { "employee_number": 7934, "employee_name": "MILLER" }
    ]},
  "metadata": { "published_date": "04-APR-2016", "publisher": "oracle-base.com" }
}

The data is used as a payload in a POST call to the collection URL.

$ curl -i -X POST --data-binary @newdoc2.json -H "Content-Type: application/json" http://ol7-121.localdomain:8080/ords/sodauser/soda/latest/TestCollection1
HTTP/1.1 201 Created
Server: Apache-Coyote/1.1
X-Frame-Options: SAMEORIGIN
Cache-Control: private,must-revalidate,max-age=0
Location: http://ol7-121.localdomain:8080/ords/sodauser/soda/latest/TestCollection1/401BFC467C1C49A385D13C9AB8B92760
Content-Type: application/json
Content-Length: 238
Date: Mon, 04 Jul 2016 19:25:02 GMT

{"items":[{"id":"401BFC467C1C49A385D13C9AB8B92760","etag":"E4580B4E249164CDBA95690BDCF573676DE3416BB35F163D23E8399CBDE31FD4","lastModified":"2016-07-04T19:25:02.691672Z","created":"2016-07-04T19:25:02.691672Z"}],"hasMore":false,"count":1}
$

It's possible to do batch uploads from a single payload. Create a new file called "newdoc3.json" with the following contents.

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

The following call loads each item in the JSON array as a separate document. Notice the "?action=insert" paramter on the end of the collection URL causes.

$ curl -i -X POST --data-binary @newdoc3.json -H "Content-Type: application/json" http://ol7-121.localdomain:8080/ords/sodauser/soda/latest/TestCollection1?action=insert
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
X-Frame-Options: SAMEORIGIN
Cache-Control: private,must-revalidate,max-age=0
Content-Type: application/json
Content-Length: 435
Date: Mon, 04 Jul 2016 19:25:57 GMT

{"items":[{"id":"489E3D4437C24D74AE2804833AA32802","etag":"E96BA3AC0D83D4F3322FB94A814ADEB417A1FF3C77A373BED5B75B32510D8D2C","lastModified":"2016-07-04T19:25:57.959800","created":"2016-07-04T19:25:57.959800"},{"id":"1A6F2C80878A4743B36B58D5D206DE21","etag":"505C57CA1579BE91DA5C059B2FFC55669FF12B56AB480E164B10109FC76140B7","lastModified":"2016-07-04T19:25:57.959800","created":"2016-07-04T19:25:57.959800"}],"hasMore":false,"count":2}
$

You can see from the previous output that two documents were created.

Retrieve Document

A GET call to the collection URL returns an array containing all the documents in the collection, including the metadata and document content.

$ curl -i -X GET http://ol7-121.localdomain:8080/ords/sodauser/soda/latest/TestCollection1/
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
X-Frame-Options: SAMEORIGIN
Cache-Control: private,must-revalidate,max-age=0
Content-Type: application/json
Transfer-Encoding: chunked
Date: Mon, 04 Jul 2016 19:26:21 GMT

{"items":[{"id":"1A6F2C80878A4743B36B58D5D206DE21","etag":"505C57CA1579BE91DA5C059B2FFC55669FF12B56AB480E164B10109FC76140B7","lastModified":"2016-07-04T19:25:57.959800Z","created":"2016-07-04T19:25:57.959800Z","links":[{"rel":"self","href":"http://ol7-121.localdomain:8080/ords/sodauser/soda/latest/TestCollection1/1A6F2C80878A4743B36B58D5D206DE21"}],"value":{"employee_number":7499,"employee_name":"ALLEN","department_number":30}},{"id":"401BFC467C1C49A385D13C9AB8B92760","etag":"E4580B4E249164CDBA95690BDCF573676DE3416BB35F163D23E8399CBDE31FD4","lastModified":"2016-07-04T19:25:02.691672Z","created":"2016-07-04T19:25:02.691672Z","links":[{"rel":"self","href":"http://ol7-121.localdomain:8080/ords/sodauser/soda/latest/TestCollection1/401BFC467C1C49A385D13C9AB8B92760"}],"value":{
  "department": {
    "department_number": 10,
    "department_name": "ACCOUNTING",
    "employees": [
      { "employee_number": 7782, "employee_name": "CLARK" },
      { "employee_number": 7839, "employee_name": "KING" },
      { "employee_number": 7934, "employee_name": "MILLER" }
    ]},
  "metadata": { "published_date": "04-APR-2016", "publisher": "oracle-base.com" }
}
},{"id":"489E3D4437C24D74AE2804833AA32802","etag":"E96BA3AC0D83D4F3322FB94A814ADEB417A1FF3C77A373BED5B75B32510D8D2C","lastModified":"2016-07-04T19:25:57.959800Z","created":"2016-07-04T19:25:57.959800Z","links":[{"rel":"self","href":"http://ol7-121.localdomain:8080/ords/sodauser/soda/latest/TestCollection1/489E3D4437C24D74AE2804833AA32802"}],"value":{"employee_number":7369,"employee_name":"SMITH","department_number":20}},{"id":"E55470C329DE407C8A8785D957392A18","etag":"F5737FB3015053262992DFDBD7664B84DC6E90E39B5972301B5787279D6D8D8F","lastModified":"2016-07-04T19:24:15.743977Z","created":"2016-07-04T19:24:15.743977Z","links":[{"rel":"self","href":"http://ol7-121.localdomain:8080/ords/sodauser/soda/latest/TestCollection1/E55470C329DE407C8A8785D957392A18"}],"value":{ "employees": [
   { "employee_number": 7369, "employee_name": "SMITH", "department_number": 20 },
   { "employee_number": 7499, "employee_name": "ALLEN", "department_number": 30 }
 ]}
}],"hasMore":false,"count":4,"offset":0,"limit":100,"totalResults":4,"links":[]}
$

To retrieve a specific document, add the document ID on to the collection URL. Notice the document is returned without the metadata.

$ curl -i -X GET http://ol7-121.localdomain:8080/ords/sodauser/soda/latest/TestCollection1/E55470C329DE407C8A8785D957392A18
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
X-Frame-Options: SAMEORIGIN
Cache-Control: no-cache,must-revalidate,no-store,max-age=0
ETag: F5737FB3015053262992DFDBD7664B84DC6E90E39B5972301B5787279D6D8D8F
Last-Modified: Mon, 04 Jul 2016 20:24:15 BST
Content-Type: application/json
Content-Length: 189
Date: Mon, 04 Jul 2016 19:27:29 GMT

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

It is also possible to search, or filter, a list of documents, similar to adding a WHERE clause in SQL. Create a document called "filter.json" with the following contents.

{ "department.employees.employee_name": "KING" }

The following POST call will return documents matching the filter. Notice the "?action=query" parameter at the end of the collection URL.

$ curl -i -X POST --data-binary @filter.json -H "Content-Type: application/json" http://ol7-121.localdomain:8080/ords/sodauser/soda/latest/TestCollection1?action=query
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
X-Frame-Options: SAMEORIGIN
Cache-Control: private,must-revalidate,max-age=0
Content-Type: application/json
Transfer-Encoding: chunked
Date: Mon, 04 Jul 2016 19:28:09 GMT

{"items":[{"id":"401BFC467C1C49A385D13C9AB8B92760","etag":"E4580B4E249164CDBA95690BDCF573676DE3416BB35F163D23E8399CBDE31FD4","lastModified":"2016-07-04T19:25:02.691672Z","created":"2016-07-04T19:25:02.691672Z","value":{
  "department": {
    "department_number": 10,
    "department_name": "ACCOUNTING",
    "employees": [
      { "employee_number": 7782, "employee_name": "CLARK" },
      { "employee_number": 7839, "employee_name": "KING" },
      { "employee_number": 7934, "employee_name": "MILLER" }
    ]},
  "metadata": { "published_date": "04-APR-2016", "publisher": "oracle-base.com" }
}
}],"hasMore":false,"count":1}
$

Update a Document

An existing document in the collection is updated using a PUT call to the document URL, passing the new JSON document as the payload.

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

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

The data is used as a payload in a PUT call to the document URL.

$ curl -i -X PUT --data-binary @updatedoc.json -H "Content-Type: application/json" http://ol7-121.localdomain:8080/ords/sodauser/soda/latest/TestCollection1/E55470C329DE407C8A8785D957392A18
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
X-Frame-Options: SAMEORIGIN
Cache-Control: no-cache,must-revalidate,no-store,max-age=0
ETag: CE948341264B26AA49E94563B2DBED43FFFCEC9F5CD4AF13BA80962FF4F6F11B
Last-Modified: Mon, 04 Jul 2016 20:29:18 BST
Location: http://ol7-121.localdomain:8080/ords/sodauser/soda/latest/TestCollection1/E55470C329DE407C8A8785D957392A18
Content-Length: 0
Date: Mon, 04 Jul 2016 19:29:18 GMT

$

Checking the document contents reveals it has changed.

$ curl -i -X GET http://ol7-121.localdomain:8080/ords/sodauser/soda/latest/TestCollection1/E55470C329DE407C8A8785D957392A18
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
X-Frame-Options: SAMEORIGIN
Cache-Control: no-cache,must-revalidate,no-store,max-age=0
ETag: CE948341264B26AA49E94563B2DBED43FFFCEC9F5CD4AF13BA80962FF4F6F11B
Last-Modified: Mon, 04 Jul 2016 20:29:18 BST
Content-Type: application/json
Content-Length: 105
Date: Mon, 04 Jul 2016 19:29:43 GMT

{ "employees": [
   { "employee_number": 7499, "employee_name": "ALLEN", "department_number": 30 }
 ]}
$

Delete a Document

A document is deleted by making a DELETE call to the document URL.

$ curl -i -X DELETE http://ol7-121.localdomain:8080/ords/sodauser/soda/latest/TestCollection1/E55470C329DE407C8A8785D957392A18
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
X-Frame-Options: SAMEORIGIN
Cache-Control: private,must-revalidate,max-age=0
Content-Length: 0
Date: Mon, 04 Jul 2016 19:30:15 GMT

$

Checking the document contents reveals it has been deleted.

$ curl -i -X GET http://ol7-121.localdomain:8080/ords/sodauser/soda/latest/TestCollection1/E55470C329DE407C8A8785D957392A18
HTTP/1.1 404 Not Found
Server: Apache-Coyote/1.1
X-Frame-Options: SAMEORIGIN
Content-Type: application/json
Transfer-Encoding: chunked
Date: Mon, 04 Jul 2016 19:30:37 GMT

{"type":"http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html#sec10.4.1","status":404,"title":"Key E55470C329DE407C8A8785D957392A18 not found in collection TestCollection1.","o:errorCode":"REST-02001"}
$

Making a POST call to the collection URL with the "?action=delete" parameter deletes all the documents from the collection.

$ curl -i -X POST --data-binary "{}" -H "Content-Type: application/json" http://ol7-121.localdomain:8080/ords/sodauser/soda/latest/TestCollection1?action=delete
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
X-Frame-Options: SAMEORIGIN
Cache-Control: private,must-revalidate,max-age=0
Content-Type: application/json
Content-Length: 11
Date: Mon, 04 Jul 2016 19:31:03 GMT

{"count":3}
$

Making a POST call to the collection URL with the "?action=truncate" parameter truncates the collection table.

$ curl -i -X POST --data-binary "{}" -H "Content-Type: application/json" http://ol7-121.localdomain:8080/ords/sodauser/soda/latest/TestCollection1?action=truncate
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
X-Frame-Options: SAMEORIGIN
Cache-Control: private,must-revalidate,max-age=0
Content-Length: 0
Date: Mon, 04 Jul 2016 19:31:26 GMT

$

For more information see:

Hope this helps. Regards Tim...

Back to the Top.