REST Services and Application Express 5.1: Pagination

More and more Application Express developers are faced with the requirement to integrate REST services or HTTP/JSON data feeds into their applications. Application Express provides great support for SOAP web services, but for REST services using JSON to exchange data, the built-in functionality is limited. For instance, all the JSON parsing has to be done manually by employing PL/SQL code or SQL functions.

Application Express 5.1 contains two new packaged applications are provided for REST service integration:

• REST Client Assistant:
  
  

  Abbildung 1: Packaged Application REST Client Assistant

• Sample REST Services:
  
  

  Abbildung 2: Packaged Application REST Client Assistant

In the first part of the article series we introduced, how to use the REST Client Assistant packaged application to generate SQL and PL/SQL code in order to display or process data from an external REST service within an APEX application. In this part, we'll take the next step: Typical REST services return their data not all at once, but in pages.

First, we'll need an example of a REST service returning their data in pages. The "USGS Earthquake" data feed, which we used in the first part, returns its data all at once - so we won't use it here any more. With the Oracle Database and Oracle REST Data Services up and running (as most APEX developers have) we can create such a service within only minutes. Make sure that you have a table containing some data (the EMP table with its 14 rows is not enough); we'll need at least a few hundred rows.

Oracle REST Data Services (ORDS) allows to expose database tables as REST services. Make sure to use the most current version 3.0 or higher. Please do not use the old ORDS 2.x versions to create the REST services.

To create the REST service, please also do not use RESTful Services within Application Express SQL Workshop. This has been developed for the ORDS 2.x feature set and you'll exclude yourself from a lot of ORDS features. Until APEX SQL Workshop supports the full ORDS 3.x feature set, it's best to work with a SQL script and the ORDS package.

The following PL/SQL calls are already sufficient to create a REST service, the first one activates the database schema for REST, the second one actually creates the REST API on top of the table (please adjust the placeholders to the schema and table names of your environment).

begin
    ords.enable_schema(
        p_enabled        => true,
        p_schema         => '{schema-name}',
        p_auto_rest_auth => false );
    
    ords.enable_object(
        p_enabled        => true,
        p_schema         => '{schema-name}',
        p_object         => '{table-name}',
        p_object_type    => 'TABLE',
        p_object_alias   => 'my-rest-table',
        p_auto_rest_auth => false );
end;
/

commit
/

The REST interface will be available immediately after you have committed your change (we are within a database). You can use a plain browser to perform the first test.

http://{server}:{port}/ords/{schema-name}/my-rest-table

You should see a browser window similar to the following screenshot. Having a browser plug-in to format JSON will be very useful. The concrete data, of course, depends on the table you are actually using.

First test of the new REST API for our database table

At the first glance, this pretty much looks like the "Earthquake" example from the first part of the series. But it gets interesting when scrolling down to the end of the JSON response.

This REST service returns its data in pages

The JSON feed indicates that it consists of 25 data items (table rows). To get more data, we have to invoke the REST interface another time. The REST response even contains the URL we have to call, in order to get the next page of data: it's contained in the links[rel=next] attribute. When we examine these links a bit, we'll see that pagination is controlled by the URL parameters offset and limit.

http://{server}:{port}/ords/{schema-name}/my-rest-table?offset=100&limit=50
http://{server}:{port}/ords/{schema-name}/my-rest-table?limit=500

Thus we can also construct URLs ourselves and request a very specific (smaller or larger) page. But the server (ORDS) imposes some limits: by default, the maximum page size is 500 rows. The reason for this is simply scalability: Limiting response sizes allows to serve more clients simultaneously.

When integrating the REST service into an APEX application, pagination is important. Think about displaying the data as a report (as we have done in the first part of the series). An APEX report also provides pagination to the end user. Below the report you'll see links, buttons or a select list which allows to navigate through the data. But APEX is SQL-driven: navigating to the next page leads to the report SQL query being re-executed and a specific window (the "next page") of the result set being returned.

For a REST service, the report SQL query parses the result JSON. Re-executing it parses the same JSON again; it won't get any new data from the REST service. So that kind of pagination will simply not work with a REST service. We have to implement report pagination differently: When the end user navigates to the next or previous page, we have to invoke the REST service - with new values for limit and offset, so that we get the requested page of data.

How a REST service does pagination is, by the way, not standardized. offset and limit are Oracle-specific. Oracle product development uses an internal Oracle REST standard which determines how pagination and other things are done for REST services within Oracle products. So you can expect limit and offset to work not only with ORDS, but also with other Oracle Products in the Cloud or on-premise.

However, REST services provided by other vendors or products might work different. It might just be different parameter names, it might also be a different way of passing them to the REST service. Instead of URL parameters we could think about HTTP headers or information in the body of a POST request. So, when we're about to integrate a given REST service into an APEX application, we have to know something about the service; just the information that it's a "REST service", is not enough.

The REST Client Assistant packaged application supports pagination for ORDS REST services. It detects and supports ORDS 3.x services as well as ORDS 2.x, even though the latter one does not follow the "Oracle REST standard". Using the simple example ORDS REST Service, we can now try it out: Run REST Client Assistant and create a new REST Service Seference. As Endpoint URL use the address you used when invoking your REST service in the browser.

REST Client Assistant: Create a new REST Service Reference

When the reference has been created, click the REST Data tab to see whether it works and whether REST Client Assistant can work with the REST service. It should look similar to the following.

REST Client Assistant: Review REST service data

Clicking the symbol in the upper right corner of the window opens a modal dialog which contains all data. In the "Earthquake" example from the first part of the series, this dialog really contained all data returned by the REST service. With our ORDS REST service, it's different. We just see 25 rows, but the dialog contains buttons to navigate to the next and previous pages. You can also always see the URL pointing to the current data window.

REST Pagination with the REST Client Assistant

In the modal dialog, then clock the PL/SQL Code on Page Load supporting Pagination tab. You will - again - see generated PL/SQL code. But the simple Copy & Paste is not sufficient any more: To get working pagination buttons like this, in your own APEX applucation, a few steps more are required ...

Generated PL/SQL Code for your own APEX application - with pagination support

So, to integrate that REST service as a report into your application, we'll start with a new empty page. Then (as described in the generated code comments) ...

• First create a new classic report on your application page. As with the Earthquake example, use the query from the SQL Query (Region Source) tab as the region source.
  
  
• Then create 3 new hidden page items. The generated PL/SQL code comments contain a hint for the naming scheme: Create the {APEX_ITEM_URL_NEXT_PAGE} item as P1_URL_NEXT_PAGE; the other ones similar to that.
  
  
• Then do some Find & Replace on the generated code and replace the placeholders {APEX_ITEM_...} with the real item names, as you created them. Create a new PL/SQL process to be executed on page load - before header with this code.
  
  
• After that, create two buttons. One is supposed to navigate to the previous page; have it redirecting to the same application page setting the P1_SHOW_PAGE to prev. The other one is supposed to advance to the next page; it also redirects to the same APEX page and sets P1_SHOW_PAGE to next.
  
  
• Of course, on the first page we don't want to have a previous page button. So make it conditional; the generated code comments indicate, how. The same applies to the next page button: We only want to see it when there really is a next page to navigate to.

After having done all this, your application page should look similar to the following screen shot. The interesting bit is, that the actual report pagination is now done with two custom buttons, and the PL/SQL code generated by REST Client Assistant. Thus, turn off the reports own pagination in report attributes. Using the buttons, you can now walk through all pages of data retrieved from the REST service.

Application Express report based on a REST service: With pagination

REST Client Assistant can do even more. We might have the need to fetch all data from the REST service at once, for instance, to process it further or to do some computations with it. So we need a PL/SQL function or procedure which automatically walks through all pages fetching all data. REST Client Assistant can generate such a function for you: When being on the Service Reference "home page", click Load Data in the menu on the right.

REST Client Assistant: Load Data

Choose Table Function (that one is most flexible), pick a name and then click on the Generate Code button.

REST Client Assistant: Generate a table function fetching all REST service data

Then run the generated script in a database schema in order to create the table function. You now can get all data with one simple SQL query.

Get all data from a REST service with a table function

This table function starts with fetching the first row - the JSON is being parsed and data is returned as the table functions' result. Then it checks whether a link to the next page. If yes, that page is fetched, parsed and data is returned again. This continues until the last page has been reached and no link to another page is present. If obvious that this will lead to a lot of HTTP requests being executed for a REST service returning a large data set. So keep execution times and HTTP overhead in mind when working with this.

This concludes the second part of the REST and Application Express 5.1 article series. We now can access REST services returning their data in pages and we can navigate from one page to another. But the story goes on. In the next part we'll show how to add a filter thus only getting a subset of data from the REST service.

back to blogs.oracle.com/apex