ORDS RESTful Services

Overview

The point of this section is not to say everything that needs to be said about RESTful Services. Rather it will describe some patterns one is likely to encounter and how they might be implemented. Those patterns are as follows:

Data producing services or outbound integrations are one of the most likely patterns one will implement. Such services are typically pull producers that generate a chunk of data in response to an explicit request. A less common producer is one which continues to generate data without external prompting by invoking an external data consuming service. In this case, the service is either governed by the DBMS_SCHEDULER or by and external scheduling system.
Data consuming services or inbound integrations insert data into RDS. The inbound data originates in an external system. There is no need to insert data found in the participating products or subscribe to RICS data originating in these products because it is already being replicated.
Bulk import and export services represent another inbound and outbound integration pattern. In this case, however, data is transferred through object storage rather than through the service itself. The role of the service is to initiate the transfer.
The last services pattern concerns process orchestration and monitoring. These service patterns start, stop, and monitor jobs. In most cases, these services will run asynchronously. For example, they will submit a job for future execution and return immediately.

Oracle RESTful Data Services play a role in every integration. Services either directly transfer the data or initiate that transfer through object storage. For a pull pattern, the service queries ADW and then returns the query result as its response. For a push pattern, the service is invoked with a payload that is inserted in a ADW table. In the case of a bulk export integration, the service initiates the export and then returns a response indicating whether the export was successfully initiated or not. Lastly, a bulk import integration service initiates the import and then returns a response indicating whether the import was successful or not. Additional services are required to monitor the progress of import and export jobs.

In most cases, the pattern chooses itself for a given task. Synchronous data services that push or pull data in response to an explicit request are simple to implement. The problem is that simple producers do not tend to scale to large volumes of data. First, there is a non-negotiable hard limit of 300 seconds on query duration. If the query exceeds this limit, the caller will return a socket hang up error. Moreover, it is also worth remembering that the database is not infinitely scalable. Specifically, there are only so many connections available (a max of 100) and there is only so much CPU capacity. One can't split a huge bulk data task into a multitude of smaller subtasks and expect them to require fewer CPU seconds. In the worst case, a backlog of REST invocations builds, and invocations start timing out.

Implementing a RESTful Service in APEX

In order to implement the examples below, you will need:

Access to the APEX UI and Workspace so that you can create a service

The following paragraphs will only provide an overview of how one creates a RESTful service. Consult Chapter 7 of the SQL Workshop Guide, Enabling Data Exchange with RESTful Services. The chapter describes in detail how one creates a RESTful service in APEX. Bear in mind, documentation is version specific. Although documentation across versions tends to be quite similar, it is generally best to consult the documentation for the version of APEX one is using.

To begin, navigate to the APEX UI and select a workspace, such as MFCS, then follow the steps below:

From the APEX UI navigate to the RESTful Services page (i.e., from the APEX Navigation Bar SQL Workshop > RESTful Services).
Click Modules
Click Create Module
Name your module "imp_guide" and set the Base Path to "/imp_guide/"
Click Create Module

A module represents a collection of related services. Begin creating the first service by creating a template. The steps are as follows:

Click Create Template
Set the URI Template to "hello_world/:name"
Click Create Template

The ":name" path component allows us to introduce and demonstrate a bind variable.

The last step is to create a handler for the service. Create the handler by following steps below:

Click Create Handler
Set the Method to GET
Set Source Type to Collection Query
Set the Source to "select 'Hello World! ' || :name as response from dual"
Click Create Handler

The full URL is displayed on the ORDS Handler Definition page. The URL has the following form:

https://<host>/<tenant-name>/ords/<workspace>/imp_guide/hello_world/<your-name>

Note that the URL for service handler is displayed on the handler definition page.

Since this is a GET service, it can be tested from a browser. The response for this service, if your_name was john would be:

{
  "items": [
    {
      "response": "Hello World! john"
    }
  ],
  "hasMore": false,
  "limit": 25,
  "offset": 0,
  "count": 1,
  "links": [
    {
      "rel": "self",
      "href": "https://<host>/<tenant-name>/ords/mfcs/imp_guide/hello_world/john"
    },
    {
      "rel": "describedby",
      "href": "https://<host>/<tenant-name>/ords/mfcs/metadata-catalog/imp_guide/hello_world/item"
    },
    {
      "rel": "first",
      "href": "https://<host>/<tenant-name>/ords/mfcs/imp_guide/hello_world/john"
    }
  ]
}

Query parameters become bind variables. For example:

Edit the Source of your hello_world service to be "select 'Hello World! ' || :name || ' ' || nvl(:last_name, 'Smith') as response from dual"
Apply Changes.

The response for this service, if your_name was john?last_name=jones would be:

{
  "items": [
    {
      "response": "Hello World! john jones"
    }
  ],
  "hasMore": false,
  "limit": 25,
  "offset": 0,
  "count": 1,
  "links": [
    {
      "rel": "self",
      "href": "https://<host>/<tenant-name>/ords/mfcs/imp_guide/hello_world/john?last_name=jones"
    },
    {
      "rel": "describedby",
      "href": "https://<host>/<tenant-name>/ords/mfcs/metadata-catalog/imp_guide/hello_world/item"
    },
    {
      "rel": "first",
      "href": "https://<host>/<tenant-name>/ords/mfcs/imp_guide/hello_world/john?last_name=jones"
    }
  ]
}

Before proceeding:

Create the hello_world service in the APEX UI.
Test the service from a browser. You should be challenged (if you have not already been authenticated) when invoking the service. Use your IDCS login credentials to authenticate.

Invoking a RESTful Service from POSTMAN

In order to implement the example below, you will need:

The hello_world service described above.
Access to POSTMAN. The discussion below assumes familiarity POSTMAN.
The ORDS OAUTH credentials created in Obtaining ORDS Service Credentials.

Invoking a RESTful service from POSTMAN combines access token generation with endpoint access. Using POSTMAN allows you to test your services as well as simulate the fundamental tasks performed in service-based integration. To invoke your hello_world service using POSTMAN, follow these steps:

Open Postman and create a new request by clicking on the "New" button in the top left corner of the application. Select HTTP Request.
In the "Enter URL or paste text" field, enter the endpoint of the hello_world service.
The default HTTP method is GET. Examine the other methods, but leaving the setting as GET.
Click the Params tab and add the last_name parameter (i.e., key is last_name, value is Smith for example).
Click the Authorization tab.
1. Select the OAuth 2.0 type from the drop-down menu.
2. Click on the "Get New Access Token" button.
3. In the "Get New Access Token" popup window, fill in your access token URL (IDCS Authorization Server endpoint URL), client ID, client secret, grant type, and scopes.
4. Once you have filled in the required fields, click on the Get New Access Token button.
5. POSTMAN will then display the token details, such as the access token, refresh token, and token expiration time.
6. Finally, click the Use Token to apply the token to your service.
Once you have configured the request, click on the "Send" button to send the request to the RESTful service.
You will see the response from the RESTful service in the "Response" section of the request window. You can view the response headers, body, and status code to verify that the request was successful.

Before proceeding invoke your hello_world service from POSTMAN.

9 ORDS RESTful Services

Overview

Implementing a RESTful Service in APEX

Invoking a RESTful Service from POSTMAN