REST Services and Application Express 5.1 - Part 1

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:
  
  

  Packaged application REST Client Assistant

• Sample REST Services:
  
  

  Packaged Application REST Client Assistant

Sample REST Services is, as the name indicates, a sample application containing a report, a form and a chart. Unlike a normal application, all data come from a REST service and not from a table. When the report page is being rendered, its data is being fetched from the REST service - when the form page is being submitted, the changes are submitted to the REST service.

As all sample applications, Sample REST Services will be unlocked by default - all its components can be examined immediately after installation. You'll find a lot of PL/SQL and SQL code which is responsible for the actual communication with the REST service. This code has actually been generated with the second packaged application, the REST Client Assistant. So, REST Client Assistant is the far more interesting application, since it helps the developer with generating SQL and PL/SQL code not only for one specific, but for any REST service. This document will walk you through the REST Client Assistant application based on a public HTTP/JSON data feed. So, if not done already, install the application into your Application Express workspace and run it right now.

First run of the REST Client Assistant application

On its first invocation, the application will display some prerequisites, which must be met in order to invoke external REST services from your Application Express instance. That basically means that HTTP requests to external endpoints must be possible. In more detail, that means:

• The database server must be physically able to establish a network connection to the remote server hosting the REST service.
• When the REST service is on the internet and the database server is behind a firewall, the proxy server must be configured. That can be done at the Application Express instance level or within the REST Client Application itself. In the latter case, click the Administration link in the upper right corner of the screen and choose the option to set the proxy server.
• A Network-ACL (Access Control List) must be configured for the Application Express engne user (APEX_050100) using the DBMS_NETWORK_ACL_ADMIN package. You might need the help of your database administrator for this.
• For HTTPS endpoints, it might be required to have a wallet configured for your Application Express instance. If not already done, create the wallet on the database server, then navigate to Instance Settings within the INTERNAL workspace and configure APEX to use the wallet. You might need the help of your database administrator for this.

When all these requirements are understood (and met), click Continue. Then you will be redirected to the home page of the REST Client Assistant. Then click on the Add REST Service Reference button in order to start working with a REST service.

REST Client Assistant home page

The REST service or HTTP/JSON data feed must also meet a few requirements for Application Express. Since we want to visualize the data with Application Express components like reports, forms or charts, REST services must return a data structure similar to a table. Today, most REST services return JSON responses - that response should contain an array which the individual data objects as array elements. Application Express will consider the array as the equivalent to a table, the array elements will be the rows and the JSON attributes of an array element will be the columns. Many REST services will provide exactly that kind of structure.

So let's get started with a first, simple service: GeoJSON Datafeeds des USGS. Actually, this is a HTTP/JSON data feed and not really a REST service. But to get some experience with the REST client assistant, it's a perfect example. Use the following data feed. https://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/all_day.geojson.

Note: On apex.oracle.com only specific HTTP endpoints can be used. This one will not work.

Create a new REST Service Reference within the REST Client Assistant

After providing the endpoint URL and a name for the REST service, REST Client Assistant will fetch and display the service response. In this example, the features attribute contains the above descibed JSON array, which will act as the equivalent to a table. Thus enter features in the Row Selector input field, make sure that Response Format is set to JSON and click the Add Service Reference button.

The "features" attribute contains the array which acts as the table equivalent

In the next step, REST Client Assistant will analyze the individual array elements and extract their attributes. The following dialog will display all detected attributes with their data types. This collection can be modified or accepted as-is. In most cases, not all attributes are really needed, so it might make sense to remove the unneeded ones. When done, click the Save and Finish button.

JSON attributes detected by REST Client Assistant

You will be redirected back to the application home page. It should now contain a new entry for the REST service reference which has just been created.

REST Client Assistant home page: We now have one REST service reference

The really interesting parts of the REST Client Assistant start here. Have a look at the PL/SQL Code (on Page Load) and SQL Query (region source) tabs. REST Client Assistant has already generated some PL/SQL and SQL code, which you can immediately start using in your own applications.

SQL and PL/SQL code generated by REST Client Assistant

But before using the generated code in another application, let's have a look whether the REST Service Reference in REST Client Assistant really works. Click on the REST Data tab, then on the Fetch REST Service Data button. You will see a sample of four columns and five rows. Clicking on the expand icon in the upper right corner of the region will open a modal dialog showing all columns and all rows.

Review data returned by the REST Service Reference

Now we can start using the generated SQL and PL/SQL code within our own application. Create a new, empty application (or navigate to an existing one). If you are using a proxy server in REST Client Assistant, you should add it to the target application as well. Navigate to Shared Components and then to Application Definition Attributes for that.

Then navigate to the page where you want to display REST service data. With Page Designer, create a new process in the Pre-Rendering area. Then simply copy and paste the PL/SQL code from the PL/SQL Code (on Page Load) section in REST Client Assistant.

Create a new PL/SQL Pre-Rendering process with the code generated by REST Client Assistant

Then create a report region. You might use a classic report for now. The SQL Query can - again - simply be copied from the SQL Query (region source) tab of REST Client Assistant. If the SQL query returns too many columns, you might remove a few ...

Create a report region to actually display the REST service data

Finally save your changes and run the application page. It should then look similar to the following screenshot.

The REST service data is displayed in an APEX report - on an APEX page

You now can further configure your report details and layout. Of course, other Application Express components can also be used. If there is a DATE attribute in the REST service data, the Calendar component might be appropriate. Charts can also be used. The PL/SQL and SQL code generated by the REST Client Assistant translate between the JSON response of the REST service and the SQL interface which is being used by the Application Express components.

Now let's have a look into some other, interesting functionality of REST Client Assistant.

When, within REST Client Assistant, on the detail page of a REST Service Reference, click the hamburger menu icon in the upper right corner to open the actions menu for the REST service reference.

Available actions for a REST service reference within REST Client Assistant

Load Data should be a useful one for most Application Express developers. It allows to copy the data from the REST service directly into a table. REST Client Assistant can do this either immediately or it can generate code which can then be embedded into other PL/SQL business logic or be used as a scheduled job.

Load data from a REST service into a local table - with REST Client Assistant

Load Data Now reuiqres just a table name and will copy the data into that table immediately. However, that table will then reside in the schema of the REST Client Assistant, which might not be that what the developer wants. So we'll have a look into the code generation options, which get particularly important when we will work with a REST service which returns its data page-wise. REST Client Assistant can generate a PL/SQL block or a table function - the latter one provides more flexibility since it can be used within SQL queries. To try it out, provide a name for your PL/SQL procedure or table function and click on Generate Code.

A table function to download REST service data - generated by REST Client Assitant

Run the generated code in the target schema and try it out as follows ...

SQL> select title, mag from table("USGS_EQ_TABFUNC"());

TITLE                                                       MAG    
----------------------------------------------------------- ------
M 1.8 - 32km ENE of Fritz Creek, Alaska                     1.8    
M 0.9 - 5km NW of Glen Avon, CA                             0.89   
M 2.3 - 5km WSW of Volcano, Hawaii                          2.26   
M 1.5 - 52km WSW of Anchor Point, Alaska                    1.5    
M 4.8 - 134km ESE of Pondaguitan, Philippines               4.8    
M -0.3 - 59km N of Pahrump, Nevada                          -0.3   
M 0.8 - 10km ENE of Cloverdale, California                  0.78   
M 4.5 - Sea of Japan                                        4.5    
M 1.7 - 31km WNW of Ester, Alaska                           1.7    
M 1.3 - 52km WNW of Willow, Alaska                          1.3    
:

The code generated by REST Client Assistant does two things: First, it uses the APEX_WEB_SERVICE package to invoke the REST service and to retrieve the JSON response. The second part is to parse the JSON and to extract the data attributes. When the application runs on an 11g database, JSON parsing is done with the APEX_JSON package and the XMLTABLE SQL function. On a 12c database, the native SQL/JSON function JSON_TABLE is used.

It's recommended to run the REST Client Assistant on the same database version as the application where the generated code should be used. However, when it runs in a 12c database, it can also generate code for 11g. To configure this, navigate to the Administration section of REST Client Assistant.

Administration section of REST Client Assistant

Within the Configure Oracle12c JSON Functions you can enable or disable usage of the Oracle12c native JSON functions. When disabled, REST Client Assistant will generate PL/SQL and SQL code for Oracle11g, even when it runs on Oracle12c.

Enable or disable Oracle12c SQL/JSON functions

As a packaged application, REST Client Assistant is locked after installation. You can run the application, but not edit or browse it. As all other packaged applications, also REST Client Assistant can be unlocked, which enables you to browse its components or to edit or extend the application. However, unlocking the application will make it unsupported by Oracle and ineligible for future in-place upgrades,

If you have not already tried out REST Client Assistant, install and play with it and your favorite REST services. In the next part of this document, we will look into using "real" REST services, supporting POST, PUT or DELETE requests or page-wise results, with REST Client Assistant.

back to blogs.oracle.com/apex