JSON relational duality views present relational rows as JSON documents that applications can read and write, with optimistic locking built in. ORDS publishes them like any view with AutoREST, so a document API needs no handler code. This guide publishes a duality view of routes, updates a document, and shows the built-in conflict check.
Before You Start
You need ORDS installed and running against your database, and a schema to work in. The examples use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder of the Oracle Database 26ai code repository on GitHub. The schema comes from Oracle Database 26ai SQL and PL/SQL Book.
ORDS in the examples answers at https://localhost:8443/ords/, and NIMBUS is REST-enabled with the URL alias nimbus. Replace the host and port with your own ORDS address. The curl commands use -k because the test server has a self-signed certificate; leave it out when your certificate is trusted. JSON responses are formatted for reading; ORDS returns them on one line.
Syntax
create json relational duality view view_name as
table @update -- allowed operations: @insert, @update, @delete
{ _id : primary_key, field : column, ... };
ords.enable_object(p_object => 'VIEW_NAME', p_object_type => 'VIEW',
p_object_alias => 'alias', ...);A Duality View of Routes
ROUTE_DV shows each route as a document. It allows updates only, and only blockMinutes is updatable, so the API cannot add or remove routes or change their airports.
Example:
create or replace json relational duality view route_dv as
routes @update
{
_id : route_id,
origin : origin @noupdate,
destination : destination @noupdate,
distanceKm : distance_km @noupdate,
blockMinutes : block_minutes
};
begin
ords.enable_object(p_enabled => true,
p_schema => 'NIMBUS',
p_object => 'ROUTE_DV',
p_object_type => 'VIEW',
p_object_alias => 'route_docs',
p_auto_rest_auth => false);
commit;
end;
/Output:
View ROUTE_DV created. PL/SQL procedure successfully completed.
Read a Document
Example:
curl -k https://localhost:8443/ords/nimbus/route_docs/7
Output:
{
"_id": 7,
"origin": "DXB",
"destination": "AMS",
"distanceKm": 5168,
"blockMinutes": 378,
"_metadata": {
"etag": "CA9464B082EF18FD5613AE62A2318CCA",
"asof": "00000000011CBB5C"
},
"links": [
{
"rel": "self",
"href": "https://localhost:8443/ords/nimbus/route_docs/7"
},
{
"rel": "describedby",
"href": "https://localhost:8443/ords/nimbus/metadata-catalog/route_docs/item"
},
{
"rel": "collection",
"href": "https://localhost:8443/ords/nimbus/route_docs/"
}
]
}The _metadata etag identifies this version of the document.
Update a Document
The client sends the whole document back with a changed blockMinutes and the etag it read:
Example:
{"_id":7,"origin":"DXB","destination":"AMS","distanceKm":5168,"blockMinutes":385,"_metadata":{"etag":"CA9464B082EF18FD5613AE62A2318CCA"}}Example:
curl -k -X PUT https://localhost:8443/ords/nimbus/route_docs/7 \ -H "Content-Type: application/json" --data-binary @put-body.json
Output (HTTP 200 OK, start of the response):
{
"_id": 7,
"origin": "DXB",
"destination": "AMS",
"distanceKm": 5168,
"blockMinutes": 385,
"_metadata": {
"etag": "8F4C6634FA600536CE42618FB927EDFA",
"asof": "00000000011CBB63"
},
"links": [
{
...The update succeeded, and the document has a new etag.
A Stale Document Is Refused
Sending the same body again, with the old etag, means the client did not see the latest version:
Output (HTTP 412 Precondition Failed):
{
"code": "PredconditionFailed",
"message": "Predcondition Failed",
"type": "tag:oracle.com,2020:error/PredconditionFailed",
"instance": "tag:oracle.com,2020:ecid/WE01x10M0XEsNRZ9AW2AKQ"
}The spelling PredconditionFailed is exactly what ORDS returned. Inserting a document fails as well, since the view allows no @insert:
Output (HTTP 400 Bad Request):
{
"code": "BadRequest",
"title": "Bad Request",
"message": "The request could not be processed for a user defined resource",
"o:errorCode": "ORDS-25001",
"action": "Verify that the URI and payload are correctly specified for the requested operation. If the issue persists then please contact the author of the resource",
...A document that changes a field marked @noupdate, such as distanceKm, is rejected too, and the table keeps its value:
Output (HTTP 400 Bad Request):
{
"code": "BadRequest",
"title": "Bad Request",
"message": "The request could not be processed for a user defined resource",
"o:errorCode": "ORDS-25001",
...After the test, route 7 was set back to 378 minutes with another PUT.
Filter Documents
The q parameter works on the documents' field names:
Example:
curl -k -G https://localhost:8443/ords/nimbus/route_docs/ \
--data-urlencode 'q={"distanceKm":{"$gt":13000}}'The response lists four routes: DXB to LAX and AKL, and back. In this test, equality filters on the CHAR(3) fields origin and destination, such as {"origin":"SIN"}, returned no documents, while numeric filters and {"_id":7} worked; test string filters on your own views.
Things to Know
- Give a duality view only the operations the API should allow; the annotations are the API's write rules.
- Clients must send the etag they read; ORDS and the database refuse stale updates with 412.
- Nested objects and arrays in a duality view come from joined tables, and ORDS returns them as they are defined.
Related Guides
Conclusion
ORDS publishes JSON relational duality views as document APIs with AutoREST: GET returns documents with etags, PUT updates them, and stale etags are refused with 412. Control what clients may change through the view's annotations.
