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

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

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

Assumptions and Comments
Database Patches
Create a Test Database User
Enable ORDS and SODA
Collections
Documents
Authentication
Using Autonomous JSON Database (AJD) and Autonomous Transaction Processing (ATP)

Assumptions and Comments

This article assumes the following.

You already have a functioning installation of ORDS.
The paths for the ORDS configuration match those from the ORDS installation article listed above.
You have an Oracle 12.1.0.2 database available with the correct patches, or a later release.
You have a way to call the web services. I used "curl" and the "Advanced REST client" extension for Chrome. In a real situation you would probably want to make these calls from Javascript, Java, C# etc.

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.

For a test installation you can just apply the 20885778 patch over a base 12.1.0.2 installation.
If this is a "real" instance, you will probably want to apply the latest CPU, which conflicts with this patch. In that case, you should apply the latest CPU and attempt to apply the 20885778 patch. The patch conflict message will read something like this.
```
Following patches have conflicts: [   20885778   22291127 ]
Use the MOS Patch Conflict Checker "https://support.oracle.com/epmos/faces/PatchConflictCheck" to resolve.
See MOS documents 1941934.1 and 1299688.1 for additional information and resolution methods.
```
Use the "MOS Patch Conflict Checker" to analyse the differences between the patches and generate a merge patch. Apply that patch to allow the use of the SODA functionality.

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

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

Enable ORDS and SODA

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@//localhost:1521/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.

HTTP  : http://localhost:8080/ords/sodauser/
HTTPS : https://localhost:8443/ords/sodauser/

The base URL for all SODA actions below will be as follows. Notice the schema alias is part of the URL.

http://localhost:8443/ords/sodauser/soda/latest/

If we want to ignore any form of authentication, we would remove the following privilege mapping. You should probably avoid this, but we will user it here as it makes the examples simpler.

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

We can recreate this privilege mapping if we want to try working with authentication.

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

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. The -i flag shows us the header information.

$ curl -i -X PUT http://localhost:8080/ords/sodauser/soda/latest/TestCollection1
HTTP/1.1 201
X-Frame-Options: SAMEORIGIN
Cache-Control: private,must-revalidate,max-age=0
Location: http://localhost:8080/ords/sodauser/soda/latest/TestCollection1/
Content-Length: 0
Date: Fri, 21 Aug 2020 09:01:55 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@//localhost:1521/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. In the following example we use the -s flag to make curl run in silent mode. The returned document is minified, so we use python3 -mjson.tool to pretty-print it for us.

$ curl -s -X GET http://localhost:8080/ords/sodauser/soda/latest/ | python3 -mjson.tool
{
    "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://localhost:8080/ords/sodauser/soda/latest/TestCollection1"
                }
            ]
        }
    ],
    "hasMore": 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://localhost:8080/ords/sodauser/soda/latest/TestCollection1
HTTP/1.1 200
X-Frame-Options: SAMEORIGIN
Cache-Control: private,must-revalidate,max-age=0
Content-Length: 0
Date: Fri, 21 Aug 2020 09:03:03 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://localhost: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 -s -X POST \
  --data-binary @newdoc1.json \
  -H "Content-Type: application/json" \
  http://localhost:8080/ords/sodauser/soda/latest/TestCollection1 | python3 -mjson.tool
{
    "items": [
        {
            "id": "3EFD1AC4F0A443F18A61ED065C49FBCF",
            "etag": "E3B0C44298FC1C149AFBF4C8996FB92427AE41E4649B934CA495991B7852B855",
            "lastModified": "2020-08-21T09:03:39.715866000Z",
            "created": "2020-08-21T09:03:39.715866000Z"
        }
    ],
    "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 -s -X POST \
  --data-binary @newdoc2.json \
  -H "Content-Type: application/json" \
  http://localhost:8080/ords/sodauser/soda/latest/TestCollection1 | python3 -mjson.tool
{
    "items": [
        {
            "id": "3AF6A7B2220E4D4AAF7AC01601A7B0E4",
            "etag": "E4580B4E249164CDBA95690BDCF573676DE3416BB35F163D23E8399CBDE31FD4",
            "lastModified": "2020-08-21T09:04:03.367461000Z",
            "created": "2020-08-21T09:04:03.367461000Z"
        }
    ],
    "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" parameter on the end of the collection URL causes.

$ curl -s -X POST \
  --data-binary @newdoc3.json \
  -H "Content-Type: application/json" \
  http://localhost:8080/ords/sodauser/soda/latest/TestCollection1?action=insert | python3 -mjson.tool
{
    "items": [
        {
            "id": "2589D8780F66402287C6344CCEC9E11B",
            "etag": "E96BA3AC0D83D4F3322FB94A814ADEB417A1FF3C77A373BED5B75B32510D8D2C",
            "lastModified": "2020-08-21T09:04:37.035894",
            "created": "2020-08-21T09:04:37.035894"
        },
        {
            "id": "444B8485D3C2443D8796EFD3F0BBC69D",
            "etag": "505C57CA1579BE91DA5C059B2FFC55669FF12B56AB480E164B10109FC76140B7",
            "lastModified": "2020-08-21T09:04:37.035894",
            "created": "2020-08-21T09:04:37.035894"
        }
    ],
    "hasMore": false,
    "count": 2,
    "itemsInserted": 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 -s -X GET http://localhost:8080/ords/sodauser/soda/latest/TestCollection1/ | python3 -mjson.tool
{
    "items": [
        {
            "id": "2589D8780F66402287C6344CCEC9E11B",
            "etag": "E96BA3AC0D83D4F3322FB94A814ADEB417A1FF3C77A373BED5B75B32510D8D2C",
            "lastModified": "2020-08-21T09:04:37.035894000Z",
            "created": "2020-08-21T09:04:37.035894000Z",
            "links": [
                {
                    "rel": "self",
                    "href": "http://localhost:8080/ords/sodauser/soda/latest/TestCollection1/2589D8780F66402287C6344CCEC9E11B"
                }
            ],
            "value": {
                "employee_number": 7369,
                "employee_name": "SMITH",
                "department_number": 20
            }
        },
        {
            "id": "3AF6A7B2220E4D4AAF7AC01601A7B0E4",
            "etag": "E4580B4E249164CDBA95690BDCF573676DE3416BB35F163D23E8399CBDE31FD4",
            "lastModified": "2020-08-21T09:04:03.367461000Z",
            "created": "2020-08-21T09:04:03.367461000Z",
            "links": [
                {
                    "rel": "self",
                    "href": "http://localhost:8080/ords/sodauser/soda/latest/TestCollection1/3AF6A7B2220E4D4AAF7AC01601A7B0E4"
                }
            ],
            "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": "3EFD1AC4F0A443F18A61ED065C49FBCF",
            "etag": "E3B0C44298FC1C149AFBF4C8996FB92427AE41E4649B934CA495991B7852B855",
            "lastModified": "2020-08-21T09:03:39.715866000Z",
            "created": "2020-08-21T09:03:39.715866000Z",
            "links": [
                {
                    "rel": "self",
                    "href": "http://localhost:8080/ords/sodauser/soda/latest/TestCollection1/3EFD1AC4F0A443F18A61ED065C49FBCF"
                }
            ]
        },
        {
            "id": "444B8485D3C2443D8796EFD3F0BBC69D",
            "etag": "505C57CA1579BE91DA5C059B2FFC55669FF12B56AB480E164B10109FC76140B7",
            "lastModified": "2020-08-21T09:04:37.035894000Z",
            "created": "2020-08-21T09:04:37.035894000Z",
            "links": [
                {
                    "rel": "self",
                    "href": "http://localhost:8080/ords/sodauser/soda/latest/TestCollection1/444B8485D3C2443D8796EFD3F0BBC69D"
                }
            ],
            "value": {
                "employee_number": 7499,
                "employee_name": "ALLEN",
                "department_number": 30
            }
        }
    ],
    "hasMore": false,
    "count": 4,
    "offset": 0,
    "limit": 100,
    "totalResults": 4,
    "links": [
        {
            "rel": "query",
            "href": "http://localhost:8080/ords/sodauser/soda/latest/TestCollection1?action=query",
            "method": "POST"
        },
        {
            "rel": "bulk-create",
            "href": "http://localhost:8080/ords/sodauser/soda/latest/TestCollection1?action=insert",
            "method": "POST"
        },
        {
            "rel": "replace",
            "href": "http://localhost:8080/ords/sodauser/soda/latest/TestCollection1?action=replace",
            "method": "POST"
        },
        {
            "rel": "delete-matching",
            "href": "http://localhost:8080/ords/sodauser/soda/latest/TestCollection1?action=delete",
            "method": "POST"
        },
        {
            "rel": "patch-matching",
            "href": "http://localhost:8080/ords/sodauser/soda/latest/TestCollection1?action=update",
            "method": "POST"
        },
        {
            "rel": "create-index",
            "href": "http://localhost:8080/ords/sodauser/soda/latest/TestCollection1?action=index",
            "method": "POST"
        },
        {
            "rel": "delete-index",
            "href": "http://localhost:8080/ords/sodauser/soda/latest/TestCollection1?action=unindex",
            "method": "POST"
        }
    ]
}
$

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

$ curl -s -X GET http://localhost:8080/ords/sodauser/soda/latest/TestCollection1/444B8485D3C2443D8796EFD3F0BBC69D | python3 -mjson.tool
{
    "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 -s -X POST \
  --data-binary @filter.json \
  -H "Content-Type: application/json" \
  http://localhost:8080/ords/sodauser/soda/latest/TestCollection1?action=query | python3 -mjson.tool
{
    "items": [
        {
            "id": "3AF6A7B2220E4D4AAF7AC01601A7B0E4",
            "etag": "E4580B4E249164CDBA95690BDCF573676DE3416BB35F163D23E8399CBDE31FD4",
            "lastModified": "2020-08-21T09:04:03.367461000Z",
            "created": "2020-08-21T09:04:03.367461000Z",
            "links": [
                {
                    "rel": "self",
                    "href": "http://localhost:8080/ords/sodauser/soda/latest/TestCollection1/3AF6A7B2220E4D4AAF7AC01601A7B0E4"
                }
            ],
            "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://localhost:8080/ords/sodauser/soda/latest/TestCollection1/3AF6A7B2220E4D4AAF7AC01601A7B0E4
HTTP/1.1 200
X-Frame-Options: SAMEORIGIN
Cache-Control: no-cache,must-revalidate,no-store,max-age=0
ETag: CE948341264B26AA49E94563B2DBED43FFFCEC9F5CD4AF13BA80962FF4F6F11B
Last-Modified: Fri, 21 Aug 2020 09:16:44 UTC
Location: http://localhost:8080/ords/sodauser/soda/latest/TestCollection1/3AF6A7B2220E4D4AAF7AC01601A7B0E4
Content-Length: 0
Date: Fri, 21 Aug 2020 09:16:44 GMT

$

Checking the document contents reveals it has changed.

$ curl -s -X GET http://localhost:8080/ords/sodauser/soda/latest/TestCollection1/3AF6A7B2220E4D4AAF7AC01601A7B0E4 | python3 -mjson.tool
{
    "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://localhost:8080/ords/sodauser/soda/latest/TestCollection1/3AF6A7B2220E4D4AAF7AC01601A7B0E4
HTTP/1.1 200
X-Frame-Options: SAMEORIGIN
Cache-Control: private,must-revalidate,max-age=0
Content-Length: 0
Date: Fri, 21 Aug 2020 09:18:26 GMT

$

Checking the document contents reveals it has been deleted.

$ curl -i -X GET http://localhost:8080/ords/sodauser/soda/latest/TestCollection1/3AF6A7B2220E4D4AAF7AC01601A7B0E4
HTTP/1.1 404
X-Frame-Options: SAMEORIGIN
Cache-Control: max-age=0
Expires: Fri, 21 Aug 2020 09:19:14 GMT
Content-Type: application/json
Transfer-Encoding: chunked
Date: Fri, 21 Aug 2020 09:19:14 GMT

{"type":"http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html#sec10.4.5","status":404,"title":"Key 3AF6A7B2220E4D4AAF7AC01601A7B0E4 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://localhost:8080/ords/sodauser/soda/latest/TestCollection1?action=delete
HTTP/1.1 200
X-Frame-Options: SAMEORIGIN
Cache-Control: private,must-revalidate,max-age=0
Content-Type: application/json
Content-Length: 28
Date: Fri, 21 Aug 2020 09:20:26 GMT

{"count":3,"itemsDeleted":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://localhost:8080/ords/sodauser/soda/latest/TestCollection1?action=truncate
HTTP/1.1 200
X-Frame-Options: SAMEORIGIN
Cache-Control: private,must-revalidate,max-age=0
Content-Length: 0
Date: Fri, 21 Aug 2020 09:21:23 GMT

$

Authentication

If you removed the privilege mapping during testing, you will need to recreate before attempting to use authentication.

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

An authentication user must to be associated with the "SODA Developer" role for SODA authentication to work.

To use Tomcat basic authentication we could use the following role and user definition in the "$CATALINA_BASE/conf/tomcat-users.xml" file.

  <role rolename="SODA Developer"/>
  <user username="sodadev" password="sodadevpwd" roles="SODA Developer"/>

When running in standalone mode, the following commands would create a user associated with the correct role.

cd /u01/ords
$JAVA_HOME/bin/java -jar ords.war sodadev sodadevpwd "SODA Developer"

The documentation recommends the OAuth2 Client Credentials flow for server-to-server communication. You can read more about ORDS authentication in the following article.

Oracle REST Data Services (ORDS) : Authentication

Using Autonomous JSON Database (AJD) and Autonomous Transaction Processing (ATP)

If you are using Autonomous JSON Database (AJD) or Autonomous Transaction Processing (ATP) you have SODA by default. Under the "Service Console" screen there is a "Developer" link. Under that link you will see a "RESTful Services and SODA" section, with the base URL for SODA. It will look something like this.

https://{random-string-and-service}.adb.uk-london-1.oraclecloudapps.com/ords/

You can use this base URL in call the calls shown above. As an example, we would create a collection using the following call, including the credentials for the ORDS enabled schema in the Autonomous Database.

curl -i -X PUT -u "sodauser:MyPassword123" https://123456789012345-OBAJD.adb.uk-london-1.oraclecloudapps.com/ords/sodauser/soda/latest/TestCollection1

For more information see:

Hope this helps. Regards Tim...

Back to the Top.