DBA Hub

📋Steps in this guide1/15

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.

oracle miscconfigurationintermediate
by OracleDba
14 views
1

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

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

Code/Command (click line numbers to comment):

1
2
3
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.
3

Create a Test Database User

We need a new database user for our testing. Notice the grant for the role. Without the database patch this role will not be present.

Code/Command (click line numbers to comment):

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

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. Web services from the schema can now be referenced using the following base URL. The base URL for all SODA actions below will be as follows. Notice the schema alias is part of the URL. 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. We can recreate this privilege mapping if we want to try working with authentication. We are now ready to start.

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

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

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

begin
  ords.delete_privilege_mapping('oracle.soda.privilege.developer', '/soda/*');
  commit;
end;
/

begin
  ords.create_privilege_mapping('oracle.soda.privilege.developer', '/soda/*');
  commit;
end;
/
5

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

Create a Collection

Create a new collection by making a call with the name of the new collection after the base SODA URL. The flag shows us the header information. 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. This is essentially a table holding key-value pairs, with the key being the column and the value being the column. From Oracle 23ai onward the structure of collection tables has changed to contain a single column called or type . Usage remains the same.

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

$

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

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 flag to make curl run in silent mode. The returned document is minified, so we use to pretty-print it for us.

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
$
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
}
$
8

Delete a Collection

Drop a collection by making a DELETE call using the full collection URL, as shown below. The table has been removed from the schema. The remaining examples assume the "TestCollection1" collection is present, so if you deleted it previously, recreate it.

Code/Command (click line numbers to comment):

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

$

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

SQL>

$ curl -i -X PUT http://localhost:8080/ords/sodauser/soda/latest/TestCollection1
9

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

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. The data is used as a payload in a POST call to the collection URL. The output of the call includes metadata about the document that was created, including the of the document. 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. Create a new file called "newdoc2.json" with the following contents. The data is used as a payload in a POST call to the collection URL. It's possible to do batch uploads from a single payload. Create a new file called "newdoc3.json" with the following contents. 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. You can see from the previous output that two documents were created.

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
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
{ "employees": [ 
   { "employee_number": 7369, "employee_name": "SMITH", "department_number": 20 }, 
   { "employee_number": 7499, "employee_name": "ALLEN", "department_number": 30 } 
 ]}

$
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
}
$

conn sodauser/sodauser1@pdb1

select count(*) from "TestCollection1";

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

1 row selected.

SQL>

{
  "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" }
}

$
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
}
$

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

$
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
}
$
11

Retrieve Document

A call to the collection URL returns an array containing all the documents in the collection, including the metadata and document content. To retrieve a specific document, add the document on to the collection URL. Notice the document is returned without the metadata. It is also possible to search, or filter, a list of documents, similar to adding a clause in SQL. Create a document called "filter.json" with the following contents. The following call will return documents matching the filter. Notice the "?action=query" parameter at the end of the collection URL.

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
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
$
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"
        }
    ]
}
$

$
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
}
$

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

$
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
}
$
12

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. The data is used as a payload in a PUT call to the document URL. Checking the document contents reveals it has changed.

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
{ "employees": [ 
   { "employee_number": 7499, "employee_name": "ALLEN", "department_number": 30 } 
 ]}

$
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

$

$
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
        }
    ]
}
$
13

Delete a Document

A document is deleted by making a call to the document URL. Checking the document contents reveals it has been deleted. Making a call to the collection URL with the "?action=delete" parameter deletes all the documents from the collection. Making a call to the collection URL with the "?action=truncate" parameter truncates the collection table.

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

$

$
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"}
$

$
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}
$

$
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

$
14

Authentication

If you removed the privilege mapping during testing, you will need to recreate before attempting to use authentication. 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. When running in standalone mode, the following commands would create a user associated with the correct role. 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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
begin
  ords.create_privilege_mapping('oracle.soda.privilege.developer', '/soda/*');
  commit;
end;
/

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

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

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. 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. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

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

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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!