Use SODA for REST with Autonomous Database

Autonomous Database supports Simple Oracle Document Access (SODA) for REST.

Overview of Using SODA for REST
SODA for REST is a predeployed REST service that can be used to store JSON documents in your database.
Load Purchase-Order Sample Data Using SODA for REST
Oracle provides a substantial set of JSON purchase-order documents, in plain-text file POList.json, as a JSON array of objects, where each such object represents a document.
Use SODA for REST with OAuth Client Credentials
You can access SODA for REST on Autonomous Database using OAuth authentication. Depending on your application, accessing SODA for REST with OAuth authentication can improve performance and security.

Parent topic: Developing RESTful Services in Autonomous Database

Overview of Using SODA for REST

SODA for REST is a predeployed REST service that can be used to store JSON documents in your database.

SODA enables flexible, NoSQL-style application development without having to use SQL. With SODA, JSON documents are stored in named collections and managed using simple CRUD operations (create, read, update and delete). And while SQL isn't required, JSON stored in SODA collections is still fully accessible from SQL when needed. For example, an operational application may be fully built using SODA (without SQL) but then the data may be later analyzed using SQL from outside of the application. Autonomous Database SODA gives application developers the best of the NoSQL and SQL worlds - fast, flexible, and scalable application development without losing the ability to leverage SQL for analytics and reporting.

SODA for REST is deployed in ORDS under the following URL pattern, where schema corresponds to a REST-enabled database schema.

/ords/schema/soda/latest/*

The following examples use the cURL command line tool (http://curl.haxx.se/) to submit REST requests to the database. However, other 3rd party REST clients and libraries should work as well. The examples use database schema ADMIN, which is REST-enabled. You can SODA for REST with cURL commands from the Oracle Cloud Shell.

This command creates a new collection named "fruit" in the ADMIN schema:

> curl -X PUT -u 'ADMIN:<password>' \
"https://example-db.adb.us-phoenix-1.oraclecloudapps.com/ords/admin/soda/latest/fruit"

These commands insert three JSON documents into the fruit collection:

> curl -X POST -u 'ADMIN:<password>' \
 -H "Content-Type: application/json" --data '{"name":"orange", "count":42}' \
 "https://example-db.adb.us-phoenix-1.oraclecloudapps.com/ords/admin/soda/latest/fruit"

{"items":[{"id":"6F7E5C60197E4C8A83AC7D7654F2E375"...
 
> curl -X POST -u 'ADMIN:<password>' \
 -H "Content-Type: application/json" --data '{"name":"pear", "count":5}' \
 "https://example-db.adb.us-phoenix-1.oraclecloudapps.com/ords/admin/soda/latest/fruit"

{"items":[{"id":"83714B1E2BBA41F7BA4FA93B109E1E85"...
 
> curl -X POST -u 'ADMIN:<password>' \
 -H "Content-Type: application/json" \
 --data '{"name":"apple", "count":12, "color":"red"}' \
 "https://example-db.adb.us-phoenix-1.oraclecloudapps.com/ords/admin/soda/latest/fruit"

{"items":[{"id":"BAD7EFA9A2AB49359B8F5251F0B28549"...

This example retrieves a stored JSON document from the collection:

> curl -X POST -u 'ADMIN:<password>' \
 -H "Content-Type: application/json" --data '{"name":"orange"}' \
 "https://example-db.adb.us-phoenix-1.oraclecloudapps.com/ords/admin/soda/latest/fruit?action=query"

{
  "items": [
    {
      "id":"6F7E5C60197E4C8A83AC7D7654F2E375",
      "etag":"57215643953D7C858A7CB28E14BB48549178BE307D1247860AFAB2A958400E16",
      "lastModified":"2019-07-12T19:00:28.199666Z",
      "created":"2019-07-12T19:00:28.199666Z",
      "value":{"name":"orange", "count":42}
    }
  ],
  "hasMore":false,
  "count":1
}

This SQL query accesses the fruit collection:

SELECT 
     f.json_document.name,
     f.json_document.count,
     f.json_document.color
FROM fruit f;

The query returns these three rows:

name      count     color
--------- --------- -------
orange    42        null
pear      5         null
apple     12        red

Note:

If you are using Always Free Autonomous Database with Oracle Database 21c, Oracle recommends the following:

For projects that were started using a database release prior to Oracle Database 21c, explicitly specify the metadata for the default collection as specified in the example in the section SODA Drivers. For projects started using release Oracle Database 21c or later, just use the default metadata. See SODA Drivers for more information.

These examples show a subset of the SODA and SQL/JSON features. See the following for more information:

SODA for REST for complete information on Simple Oracle Document Access (SODA)
SODA for REST HTTP Operations for information on the SODA for REST HTTP operations

Parent topic: Use SODA for REST with Autonomous Database

Load Purchase-Order Sample Data Using SODA for REST

Oracle provides a substantial set of JSON purchase-order documents, in plain-text file POList.json, as a JSON array of objects, where each such object represents a document.

The following examples use the cURL command line tool (http://curl.haxx.se/) to submit REST requests to the database. However, other 3rd party REST clients and libraries should work as well. The examples use database schema ADMIN, which is REST-enabled. You can use SODA for REST with cURL commands from the Oracle Cloud Shell.

You can load this sample purchase-order data set into a collection purchaseorder on your Autonomous Database with SODA for REST, using these curl commands:

curl -X GET "https://raw.githubusercontent.com/oracle/db-sample-schemas/master/order_entry/POList.json" -o POList.json

curl -X PUT -u 'ADMIN:password' \
"https://example-db.adb.us-phoenix-1.oraclecloudapps.com/ords/admin/soda/latest/purchaseorder"

curl -X POST -H -u 'ADMIN:password' 'Content-type: application/json' -d @POList.json \
"https://example-db.adb.us-phoenix-1.oraclecloudapps.com/ords/admin/soda/latest/purchaseorder?action=insert"

You can then use this purchase-order data to try out examples in Oracle Database JSON Developer’s Guide.

For example, the following query selects both the id of a JSON document and values from the JSON purchase-order collection stored in column json_document of table purchaseorder. The values selected are from fields PONumber, Reference, and Requestor of JSON column json_document, which are projected from the document as virtual columns (see SQL NESTED Clause Instead of JSON_TABLE for more information).

SELECT id, t.*
  FROM purchaseorder
    NESTED json_document COLUMNS(PONumber, Reference, Requestor) t;

See the following for more information:

SODA for REST for complete information on Simple Oracle Document Access (SODA)
SODA for REST HTTP Operations for information on the SODA for REST HTTP operations

Parent topic: Use SODA for REST with Autonomous Database

Use SODA for REST with OAuth Client Credentials

You can access SODA for REST on Autonomous Database using OAuth authentication. Depending on your application, accessing SODA for REST with OAuth authentication can improve performance and security.

Perform the following steps to use OAuth authentication to provide limited access to SODA for REST on Autonomous Database:

As the ADMIN user, access Database Actions and create a user with the required privileges.
1. Access Database Actions as ADMIN.
  See Access Database Actions as ADMIN for more information.
2. In Database Actions, click to show the available actions.
3. In Database Actions, under Administration select Database Users.
4. Click Create User.
5. In the Create User area, on the User tab enter User Name and a Password and confirm the password.
6. Select Web Access.
7. In the Create User area, select the Granted Roles tab and grant DWROLE to the user.
8. Click Create User.
See Manage Users and User Roles on Autonomous Database - Connecting with Database Actions for more information.
Use a SQL worksheet in Database Actions to grant user privileges required to load data.
1. Access Database Actions as ADMIN.
  See Access Database Actions as ADMIN for more information.
2. In Database Actions, click to show the available actions.
3. In Database Actions, under Development click SQL to open a SQL worksheet.
4. Grant user privileges required to load data to the user from Step 1.
```
GRANT UNLIMITED TABLESPACE TO user_name;
```
See Manage User Privileges on Autonomous Database - Connecting with a Client Tool for more information.
Sign out as the ADMIN user.
Sign in to Database Actions as the user that is setting up to use OAuth authentication.
In Database Actions, use a SQL worksheet to register the OAuth client.
1. Register the OAuth client.
  For example, enter the following commands into the SQL worksheet, where you supply the appropriate values for your user and your client application.
```
BEGIN
  OAUTH.create_client(
    p_name            => 'my_client',
    p_grant_type      => 'client_credentials',
    p_owner           => 'Example Company',
    p_description     => 'A client for my SODA REST resources',
    p_support_email   => 'user_name@example.com',
    p_privilege_names => 'my_priv'
  );
 
  OAUTH.grant_client_role(
    p_client_name => 'my_client',
    p_role_name   => 'SQL Developer'
  );
 
  OAUTH.grant_client_role(
    p_client_name => 'my_client',
    p_role_name   => 'SODA Developer'
  );
  COMMIT;
END;
/
```
2. In the SQL worksheet, click Run Script to run the command.
See OAUTH PL/SQL Package Reference for more information.

This registers a client named my_client to access the my_priv privilege using OAuth client credentials.
Obtain the client_id and client_secret required to generate the access token.
For example, in the SQL worksheet run the following command:
```
SELECT id, name, client_id, client_secret FROM user_ords_clients;
```
Obtain the access token. To get an access token you send a REST GET request to database_ORDS_urluser_name/oauth/token.

The database_ORDS_url is available from Database Actions, under Related Services, on the RESTful Services and Soda card. See Access RESTful Services and SODA for REST for more information.

In the following command, use the client_id and the client_secret you obtained in Step 6.
The following example uses the cURL command line tool (http://curl.haxx.se/) to submit REST requests to Autonomous Database. However, other 3rd party REST clients and libraries should work as well.
You can use the cURL command line tool to submit the REST GET request. For example:
```
> curl -i -k --user SBA-iO9Xe12cdZHYfryBGQ..:vvUQ1AagTqAqdA2oN7afSg.. --data "grant_type=client_credentials"https://mqssyowmqvgac1y-doc.adb.region.oraclecloudapps.com/ords/user_name/oauth/token
HTTP/1.1 200 OK
Date: Mon, 22 Jun 2020 15:17:11 GMT
Content-Type: application/jsonTransfer-Encoding: chunked
Connection: keep-alive
X-Frame-Options: SAMEORIGIN  

{"access_token":"JbOKtAuDgEh2DXx0QhvPGg","token_type":"bearer","expires_in":3600}
```
To specify both the client_id and the client_secret with the curl --user argument, enter a colon to separate the client_id and the client_secret. If you only specify the user name, client_id, curl prompts for a password and you can enter the client_secret at the prompt.

Use the access token to access the protected resource.

The token obtained in the previous step is passed in the Authorization header. For example:

> curl -i -H "Authorization: Bearer JbOKtAuDgEh2DXx0QhvPGg" -X GET https://database_id.adb.region.oraclecloudapps.com/ords/user_name/soda/latest
HTTP/1.1 200 OK
Date: Mon, 22 Jun 2020 15:20:58 GMT
Content-Type: application/json
Content-Length: 28
Connection: keep-alive
X-Frame-Options: SAMEORIGIN
Cache-Control: private,must-revalidate,max-age=0


{"items":[],"hasMore":false}

See Configuring Secure Access to RESTful Services for complete information on secure access to RESTful Services.

Parent topic: Use SODA for REST with Autonomous Database