Export Multiple Tables for a Date Range
A simple POST message to create an Export Request for two tables containing rows that have been inserted or updated within a given date range.
Use Case Description
- Export content that has been inserted or updated in the SHIPMENT and SHIPMENT_REFNUM tables in the month of September 2022 (times are all UTC) and send to an existing bucket on Oracle Cloud Object Storage.
- Identify unique request ID.
- Check status for completion.
curl -u username:password -X POST -H "Content-Type: application/json" -H "Prefer: respond-async" -d '
{
"schema": "PRIMARY",
"contentType": "text/plain",
"rangeAfter" : {
"value": "2022-09-01T00:00:00Z"
},
"rangeBefore": {
"value": "2022-09-30T23:59:59Z"
},
"targetSystem": {
"targetURL": "https://objectstorage.us2.oraclecloud.com/...bucket-details.../o/",
"appendName": true,
"httpMethod": "PUT"
},
"tables" : {
"items" : [
{
"tableName": "SHIPMENT",
"selectList": "SHIPMENT_GID,SHIPMENT_NAME,PERSPECTIVE,INSERT_DATE,UPDATE_DATE"
},
{
"tableName": "SHIPMENT_REFNUM"
}
]
}
}'
https://servername.us2.oraclecloud.com/logisticsRestApi/data/v1/exportRequests
Note:
- Message syntax errors will return an HTTP "400 Bad Request" error message and, where possible, a detailed explanation of the error.
- The "Content-Type" HTTP header identifies the media type of the API request message and must be "application/json".
- The "Prefer" HTTP header identifies that the request should be processed asynchronously. If not present, and there is only one table in the request message, the request will be processed synchronously, the HTTP status will be "200 OK" and the exported content will be the response data. In all other cases it will be processed asynchronously, the HTTP status will be "202 Accepted" and the response to the request will be an export status message.
- The "contentType" request message property specifies the media type of the exported content. This property is optional and defaults to "text/plain".
- "rangeAfter" and "rangeBefore" provide the date range used to filter matching records from all tables in the request message where either the "INSERT_DATE" or "UPDATE_DATE" is within the range.
- "targetSystem" identifies the destination for the exported content. In this
example it is an Oracle Cloud Object Storage Bucket Pre-Authenticated
Request (PAR) URI, which requires the following additional options
- HTTP METHOD is PUT (default would be POST if not specified) to create a new file.
- "appendName" is true which means the name of each table part must be appended to the bucket URI to give a unique file name within the bucket.
- Only the list of columns in the "selectList" property will be exported for the SHIPMENT table whereas ALL columns will be exported for the SHIPMENT_REFNUM table.
- There is no control specified for content size so the system defaults will be used to determine maximum exported volume and number of parts used. This use case will assume that the volume of exported data is within these system limits and that one "part" will be exported for each table. See Content Management for a full description of all options to control the exported data.
An example export status response will be as follows:
{
"progress": "pending",
"completed": false,
"result": {
"body": {
"requestID": 303,
"submitDate": "2022-11-29T15:03:11Z",
"tables": {
"items": [
{
"itemID": 1,
"itemName": "SHIPMENT",
"progress": "pending",
"offset": 0,
"parts": {
"count": 0,
"items": []
}
},
{
"itemID": 2,
"itemName": "SHIPMENT_REFNUM",
"progress": "pending",
"offset": 0,
"parts": {
"count": 0,
"items": []
}
}
]
}
}
},
"links": [
{
"rel": "self",
"href": "https://servername.us2.oraclecloud.com /logisticsRestApi/data/v1/exportStatuses/303"
}
]
}
Note:
- The request, and each item within it, is initially in the "pending" state.
- The part count for each item is initially "0" but will be updated as each item is processed and content is sent to the external system.
- The "self" link contains the URI to poll for status.
Identify Request ID
Each export request is allocated a unique request ID sequence number on submission.
The ID allocated to a request, which in this example is "303," is made available in two places within the status response to the request:
- "requestID" property of the result.body object within the status
- The "self" link URI. The request ID is the terminal path element in this URI.
Poll Export Status
The following command can be used to issue a GET request to see the status of the
request:
curl -u username:password -H "Accept:application/json"
https://servername/logisticsRestApi/data/v1/exportStatuses/303
The response payload would be as follows if the request was completed successfully:
{
"progress": "succeeded",
"startTime": {
"value": "2022-11-29T15:21:58Z"
},
"completed": true,
"endTime": {
"value": "2022-11-29T15:22:19Z"
},
"result": {
"body": {
"requestID": 303,
"submitDate": "2022-11-29T15:21:56Z",
"tables": {
"items": [
{
"itemID": 1,
"itemName": "SHIPMENT",
"startTime": "2022-11-29T15:21:58Z",
"endTime": "2022-11-29T15:22:17Z",
"progress": "succeeded",
"totalResults": 92,
"offset": 0,
"totalRecordCount": 92,
"parts": {
"count": 1,
"items": [
{
"partName": "DATA_303_SHIPMENT_1_1.csv",
"recordCount": 92,
"checksum": "c9179dae8947cb56abaed4f6c2ea09b3"
}
]
}
},
{
"itemID": 2,
"itemName": "SHIPMENT_REFNUM",
"startTime": "2022-11-29T15:22:18Z",
"endTime": "2022-11-29T15:22:19Z",
"progress": "succeeded",
"totalResults": 7,
"offset": 0,
"totalRecordCount": 7,
"parts": {
"count": 1,
"items": [
{
"partName": "DATA_303_SHIPMENT_REFNUM_2_1.csv",
"recordCount": 7,
"checksum": "43558be67a958164af19bead3c908420"
}
]
}
}
]
}
}
},
"links": [..etc..]
}
Note:
- The "startTime" at the request level identifies the time when the request items were added to the internal work queue.
- The "endTime" at the request level identifies the time when the last request item completed and notified the request.
- Each item contains a "startTime" and "endTime" that measures the time taken for all parts to be exported.
- Each item also contains properties to identify how much content was exported
and information for the number of parts needed to export the complete
content:
- "totalResults" is a count of the total records in the table which matched the filter criteria.
- "totalRecordCount" is the total number of rows for all parts exported
- "recordCount" is the number of rows contained within the part item.
- "checksum" is a hash of the content to verify the file received at the external system. By default the hash algorithm is MD5.
- The Oracle Cloud Object Storage bucket referenced will contain two files on
completion. The file names are:
- DATA_303_SHIPMENT_1_1.csv
- DATA_303_SHIPMENT_REFNUM_2_1.csv