A step-by-step walkthrough is provided for the basic SODA for REST operations, using examples that you can run. The examples use command-line tool cURL to send REST requests to the server.
The examples assume that you started ORDS as instructed in Installing SODA for REST, enabling ORDS in schema.
Some examples also use the sample JSON documents included in the zip file that you downloaded in installation step 3. They are in the directory ORDS_HOME/examples/soda/getting-started.
See Also:
http://curl.haxx.se/ for information about command-line tool cURL
Oracle REST Data Services Installation, Configuration, and Development Guide
Steps
An example is given of creating a new collection.
To create a new collection, run this command, where MyCollection is the name of the collection:
curl -i -X PUT http://localhost:8080/ords/schema/soda/latest/MyCollection
The preceding command sends a PUT request with URL http://localhost:8080/ords/schema/soda/latest/MyCollection, to create a collection named MyCollection. The -i command-line option causes cURL to include the HTTP response headers in the output. If the operation succeeds then the output looks similar to this:
HTTP/1.1 201 Created Cache-Control: private,must-revalidate,max-age=0 Location: http://localhost:8080/ords/schema/soda/latest/MyCollection/ Content-Length: 0
Response code 201 indicates that the operation succeeded. A PUT operation that results in the creation of a new collection—a PUT collection operation—returns no response body.
A successful PUT collection operation creates a database table to store the new collection. One way to see the details of this table is using SQL*Plus command describe:
SQL> describe "MyCollection"
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
The preceding table reflects the default collection configuration. To create a custom collection configuration, provide a collection specification as the body of the PUT operation. For information about collection specifications, see Collection Specifications.
Caution:
To drop a collection, proceed as described in Deleting a Collection. Do not use SQL to drop the database table that underlies a collection. Collections have persisted metadata, in addition to the documents that are stored in the collection table.
See Also:
PUT collection for more information about this operation
An example is given of listing available collections.
To obtain a list of the collections available in schema, run this command:
curl -X GET http://localhost:8080/ords/schema/soda/latest
That sends a GET request with the URL http://localhost:8080/ords/schema/soda/latest and returns this response body:
{
"items" : [
{
"name":"MyCollection",
"properties": {
"schemaName":"SCHEMA",
"tableName":"MyCollection",
...
}
"links" : [
{
"rel" : "canonical",
"href" :"http://localhost:8080/ords/schema/soda/latest/MyCollection"
}
]
}
],
"more" : false
}
The response body includes all available collections in schema, which in this case is only collection MyCollection.
A successful GET collection operation returns response code 200, and the response body is a JSON object that contains an array of available collections and includes the collection specification for each collection.
An example is given of deleting a collection.
To delete MyCollection, run this command:
curl -i -X DELETE http://localhost:8080/ords/schema/soda/latest/MyCollection
The preceding command sends a DELETE request with the URL http://localhost:8080/ords/schema/soda/latest/MyCollection and returns:
HTTP/1.1 200 OK Cache-Control: private,must-revalidate,max-age=0 Content-Length: 0
Response code 200 indicates that the operation succeeded. A DELETE operation that results in the deletion of a collection—a DELETE collection operation—returns no response body.
To verify that the collection was deleted, get the list of available collections in schema:
curl -X GET http://localhost:8080/ords/schema/soda/latest
If MyCollection was deleted, the preceding command returns:
{
"items" : [],
"more" : false
}
Create MyCollection again, so that you can use it in the next step:
curl -X PUT http://localhost:8080/ords/schema/soda/latest/MyCollection
An example is given of inserting a document into a collection.
The example uses file po.json, which was included in the download. The file contains a JSON document that contains a purchase order. To load the JSON document into MyCollection, run this command:
curl -X POST --data-binary @po.json -H "Content-Type: application/json"
http://localhost:8080/ords/schema/soda/latest/MyCollection
The preceding command sends a POST request with the URL http://localhost:8080/ords/schema/soda/latest/MyCollection. It outputs something like this:
{
"items" : [
{
"id" : "2FFD968C531C49B9A7EAC4398DFC02EE",
"etag" : "C1354F27A5180FF7B828F01CBBC84022DCF5F7209DBF0E6DFFCC626E3B0400C3",
"lastModified":"2014-09-22T21:25:19.564394Z",
"created":"2014-09-22T21:25:19.564394Z"
}
],
"hasMore" : false,
"count" : 1
}
A successful POST object operation returns response code 200. The response body is a JSON document that contains the identifier that the server assigned to the document when you inserted it into the collection, as well as the current ETag and last-modified time stamp for the inserted document.
Note:
If you intend to retrieve the document then copy the document identifier (the value of field "id"), to use for retrieval.
See Also:
POST object for more information about this operation
An example is given of retrieving a document from a collection.
To retrieve the document that was inserted in Inserting a Document into a Collection, run this command, where id is the document identifier that you copied when inserting the document:
curl -X GET http://localhost:8080/ords/schema/soda/latest/MyCollection/id
A successful GET document operation returns response code 200. The response body contains the retrieved document.
If id does not exist in MyCollection then the response code is 404, as you can see by changing id to such an identifier:
curl -X GET http://localhost:8080/ords/schema/soda/latest/MyCollection/2FFD968C531C49B9A7EAC4398DFC02EF
{
"type" : "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html#sec10.4.1",
"status" : 404,
"title" : "Key 2FFD968C531C49B9A7EAC4398DFC02EF not found in collection MyCollection.",
"o:errorCode" : "REST-02001"
}
An example is given of deleting a document from a collection.
To delete, from MyCollection, the document that you retrieved in Retrieving a Document from a Collection, run this command (where id is the document identifier):
curl -i -X DELETE http://localhost:8080/ords/schema/soda/latest/MyCollection/id
The preceding command sends a DELETE request with URL http://localhost:8080/ords/schema/soda/latest/MyCollection/id, and it returns this:
HTTP/1.1 200 OK Cache-Control: private,must-revalidate,max-age=0 Content-Length: 0
Response code 200 indicates that the operation succeeded. A DELETE operation that results in the deletion of an object from a collection—a DELETE object operation—returns no response body.
An example is given of bulk-inserting a set of documents into a collection from a JSON array of documents. The bulk insert operation is also called POST array insert.
This example uses file POList.json, which was included in the download. The file contains a JSON array of 70 purchase orders. To load the purchase orders into collection MyCollection, run this command:
curl -X POST --data-binary @POList.json -H "Content-Type: application/json" http://localhost:8080/ords/schema/soda/latest/MyCollection?action=insert
Parameter value action=insert causes the array to be inserted as a set of documents, rather than as a single document.
The preceding command sends a POST request with the URL http://localhost:8080/ords/schema/soda/latest/MyCollection, and it outputs something like this:
{
"items" : [
{
"id" : "6DEAF8F011FD43249E5F60A93B850AB9",
"etag" : "49205D7E916EAED914465FCFF029B2795885A1914966E0AE82D4CCDBBE2EAF8E",
"lastModified" : "2014-09-22T22:39:15.546435Z",
"created" : "2014-09-22T22:39:15.546435Z"
},
{
"id" : "C9FF7685D48E4E4B8641D8401ED0FB68",
"etag" : "F3EB514BEDE6A6CC337ADA0F5BE6DEFC5D451E68CE645729224BB6707FBE1F4F",
"lastModified" : "2014-09-22T22:39:15.546435Z",
"created":"2014-09-22T22:39:15.546435Z"
},
...
],
"hasMore":false,
"count":70
}
A successful POST array insert operation returns response code 200. The response body is a JSON document that contains the identifier, ETag, and last-modified time stamp for each inserted document.
Copy an "id" field value returned by your own POST array insert operation (not a value from the preceding example). Query the collection using SQL*Plus or SQL Developer, substituting your copied value for identifier:
SELECT json_value(json_document FORMAT JSON, '$.Reference')
FROM "MyCollection" WHERE id = 'identifier';
JSON_VALUE(JSON_DOCUMENTFORMATJSON,'$.REFERENCE')
--------------------------------------------------------------------------------
MSULLIVA-20141102
Note:
In the SQL SELECT statement, you must specify the table name MyCollection as a quoted identifier, because it is mixed-case (the table name is the same as the collection name).
Because MyCollection has the default configuration, which stores the JSON document in a BLOB column, you must include FORMAT JSON when using the SQL/JSON function json_value. You cannot use the simplified JSON syntax.
An example is given of listing the documents in a collection, using a GET operation.
You can use parameters to control the result. For example, you can:
Limit the number of documents returned
Return only document identifiers (keys), only document contents, or both keys and contents
Return a range of documents, based on keys or last-modified time stamps
Specify the order of the list of returned documents
To list the documents in MyCollection, returning their keys and other metadata but not their content, run the following command.
curl -X GET http://localhost:8080/ords/schema/soda/latest/MyCollection?fields=id
The preceding command outputs something like this:
{
"items" : [
{
"id" : "023C4A6581D84B71A5C0D5D364CE8484",
"etag":"3484DFB604DDA3FBC0C681C37972E7DD8C5F4457ACE32BD16960D4388C5A7C0E",
"lastModified" : "2014-09-22T22:39:15.546435Z",
"created":"2014-09-22T22:39:15.546435Z"
},
{
"id" : "06DD0319148E40A7B8AA48E39E739184",
"etag" : "A19A1E9A3A38B1BAE3EE52B93350FBD76309CBFC4072A2BECD95BCA44D4849DD",
"lastModified" : "2014-09-22T22:39:15.546435Z",
"created" : "2014-09-22T22:39:15.546435Z"
},
...
],
"hasMore" : false,
"count" : 70,
"offset":0,
"limit":100,
"totalResults":70
}
A successful GET collection operation returns response code 200, and the response body is a JSON document that lists the documents in the collection. If the collection is empty, the response body is an empty items array.
To list at most 10 documents in MyCollection, returning their keys, content, and other metadata, run this command:
curl -X GET "http://localhost:8080/ords/schema/soda/latest/MyCollection?fields=all&limit=10"
The preceding command outputs something like this:
{
"items": [ ... ],
"hasMore" : true,
"count" : 10,
"offset" : 0,
"limit" : 10,
"links" : [{
"rel" : "next",
"href" :
"http://localhost:8080/ords/schema/soda/latest/MyCollection?offset=10&limit=10"
}]
}
Note:
Including document content makes the response body much larger. Oracle recommends including the content in the response body only if you will need the content later. Retrieving the content from the response body is more efficient that retrieving it from the server.
The metadata in the response body shows that 10 documents were requested ("limit" : 10)) and 10 documents were returned ("count" : 10)) , and that more documents are available ("hasMore" : true). To fetch the next set of documents, you can use the URL in the field "links"."href".
The maximum number of documents returned from a collection by the server is controlled by the following:
URL parameter limit
Configuration parameters soda.maxLimit and soda.defaultLimit
Note:
If you intend to update the document then copy the document identifier (value of field "id"), to use for updating.
See Also:
Using a Filter Specification to Select Documents From a Collection, which lets you list documents based on content
Oracle REST Data Services Installation, Configuration, and Development Guide for information about configuration parameters soda.maxLimit and soda.defaultLimit
An example is given of updating a document in a collection, that is, replacing it with a newer version. For this, you use a PUT operation.
The behavior of the PUT operation for a nonexistent document depends on the key-assignment method used by the collection.
If the collection uses server-assigned keys (as does collection MyCollection) then an error is raised if you try to update a nonexistent document (that is, you specify a key that does not belong to any document in the collection).
If the collection uses client-assigned keys, then trying to update a nonexistent document inserts into the collection a new document with the specified key.
Retrieve a document from MyCollection by running this command, where id is the document identifier that you copied in Listing the Documents in a Collection:
curl -X GET http://localhost:8080/ords/schema/soda/latest/MyCollection/id
The preceding command outputs the retrieved document.
To update this document with the content of file poUpdated.json, which was included in the download, execute this command:
curl -i -X PUT --data-binary @poUpdated.json -H "Content-Type: application/json" http://localhost:8080/ords/schema/soda/latest/MyCollection/id
The preceding command outputs something like this:
HTTP/1.1 200 OK
Cache-Control: no-cache,must-revalidate,no-store,max-age=0
ETag: A0B07E0A6D000358C546DC5D8D5059D9CB548A1A5F6F2CAD66E2180B579CCB6D
Last-Modified: Mon, 22 Sep 2014 16:42:35 PDT
Location: http://localhost:8080/ords/schema/soda/latest/MyCollection/023C4A6581D84B71A5C0D5D364CE8484/
Content-Length: 0
The response code 200 indicates that the operation succeeded. A PUT operation that results in the successful update of a document in a collection—a PUT object operation—returns no response body.
To verify the document has been updated, rerun this command:
curl -X GET http://localhost:8080/ords/schema/soda/latest/MyCollection/id
The preceding command returns:
{
"PONumber": 1,
"Content" : "This document has been updated...."
}
Examples are given of using a filter specification, or query-by-example (QBE), to define query criteria for selecting documents from a collection.
The examples use the QBE.*.json files that are included in the zip file that you downloaded in installation step 3. They are in directory ORDS_HOME/examples/soda/getting-started.
See Also:
Oracle Database Introduction to Simple Oracle Document Access (SODA) for information about filter specifications and QBE
The query-by-example (QBE) in file QBE.1.json returns a list of nine documents, each of which has "TGATES" as the value of field User.
This is the query in file QBE.1.json:
{ "User" : "TGATES" }
To execute the query, run this command:
curl -X POST --data-binary @QBE.1.json -H "Content-Type: application/json" http://localhost:8080/ords/schema/soda/latest/MyCollection?action=query
A successful POST query operation returns response code 200 and a list of documents that satisfy the query criteria.
Because the command has no fields parameter, the default value fields=all applies, and the response body contains both the metadata and the content of each document.
Note:
Including document content makes the response body much larger. Oracle recommends including the content in the response body only if you need the content for a subsequent operation. Retrieving the content from the response body is more efficient that retrieving it from the server.
To execute the queries in the other QBE.*.json files, run commands similar to the preceding one.
The query-by-example (QBE) in file QBE.2.json selects documents where the value of field UPCCode equals "13023015692". UPCCode is a field of object Part, which is a field of array LineItems. Because no array offset is specified for LineItems, the query searches all elements of the array.
This is the query in file QBE.2.json:
{ "LineItems.Part.UPCCode" : "13023015692" }
Note:
Keyword "$eq" in the query is implied. See Oracle Database SODA for Java Developer's Guide for more information.
The query-by-example (QBE) in file QBE.3.json selects documents where the value of field ItemNumber, in object LineItems, is greater than 4. Keyword "$gt" is required.
This is the query in file QBE.3.json:
{ "LineItems.ItemNumber" : { "$gt" : 4 }}
The query-by-example (QBE) in file QBE.4.json selects documents where the value of field UPCCode equals "13023015692" and the value of field ItemNumber equals 3. Keyword $and is optional.
This is the query in file QBE.4.json:
{ "$and" : [
{ "LineItems.Part.UPCCode" : "13023015692" },
{ "LineItems.ItemNumber" : 3 }
]
}