REST Services and Application Express 5.1 - Part 1
| Publish Date
|| APEX version
|| Database version
||5.1 or higher
||11.2 or higher
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 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.
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
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
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
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 ...
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