APEX on the Edge

Photo by Alan Tang on Unsplash

APEX on the Edge

Integrating Oracle APEX with non-ORDS web services

ยท

14 min read

Overview

This past week at the KScope 23 conference, I presented a session called "APEX on the Edge". This session explored using APEX to integrate with non-ORDS-based web services and the complexities & nuances involved in getting them to work with APEX.

This post will walk you through the steps that I took to create each of the three main demonstrations.

๐Ÿ’พ
The slides from that session can be downloaded from here.

OpenWeather

The first site that I built an integration to was openweathermap.org. OpenWeather offers a blend of free and paid APIs that you can use for your purposes on your site. For my demonstration, I stuck to the free one, specifically the 16-day forecast API.

Using this API, I created a combination chart in APEX that showed the high & low forecast as a bar chart and the percent chance of clouds & rain as a bar chart.

๐ŸŒฉ
Create your own API key for OpenWeather here.

Create a REST data source for OpenWeather

The first thing we will do is create the REST Data Source in APEX.

  1. Use Postman to explore the URL for the Movie DB:
    api.openweathermap.org/data/2.5/forecast/da.. KEY]
    Be sure to replace [API KEY] with your API Key

  2. Create a new REST data source for the Movie DB using the same URL.

  3. Take all defaults during the creation.

Add a new column to the data profile to convert the DT column to a date

Once we've created the REST data source, we need to add an additional column. This column will convert the "DT" column - which stores the current data in epoch time - to an Oracle DATA datatype. This way, we will be able to use this column in a chart and it will be more readable.

  1. Edit The Movie DB REST data source

  2. Edit the Data Profile

  3. Click the Add Column button

  4. Set the following options:

    Column Type: SQL Expression

    Name: FORECAST_DATE

    Data Type: DATE

    SQL Expression: TO_DATE('01-jan-1970', 'dd-mon-yyyy') + DT/86400

  5. Click Create to add the new column.

Create a chart based on the OpenWeather REST data source

Now that we have the REST data source defined, let's create a chart based on it.

  1. Create a new page in your application for the chart. We'll use page 10 in this example.

  2. Edit Page 10 of your application.

  3. Create a new Chart region and set the type to Combination.

  4. Under Settings, set the Time Axis Type to Enabled.

  5. Set Legend to Show and set Position to Bottom.

  6. Next, create two new Series for High and Low using the following values:

Identification

Name

High

Low

Type

Line

Line

Source

Location

REST Source

REST Source

Source

OpenWeatherMap

OpenWeatherMap

Column Mapping

Label

FORECAST_DATE

FORECAST_DATE

Value

MIN

MAX

Appearance

Color

#ff0000

#0000ff

Label

Show

Enabled

Enabled

Position

Below Marker

Below Marker

  1. Create two additional series for Cloud and Rain using the following values:

Identification

Name

Cloud

Rain

Type

Bar

Bar

Source

Location

REST Source

REST Source

Source

OpenWeatherMap

OpenWeatherMap

Column Mapping

Label

FORECAST_DATE

FORECAST_DATE

Value

CLOUDS

RAIN

Appearance

Color

#dddddd

#0000ff

Assigned to Y2 Axis

Enabled

Enabled

Label

Show

Enabled

Enabled

Position

Below Marker

Below Marker

  1. Make any additional changes to the chart's attributes to your liking.

Create a page item & map it to the parameter for each series in the chart

Finally, let's enable users to enter any city they want to see a forecast for. We'll do this by adding a page item.

  1. Create a new page item โ€“ P10_SEARCH.

  2. Expand all four Series and then expand the Parameters section for each of them.

  3. Select all four instances of โ€œqโ€ and set the Type to Item and the Item to P10_SEARCH.

  4. Save and run the page. You should now be able to enter city names into the Search field and get their respective forecasts.

The Movie DB

The Movie DB (TMDB) is a community-driven website that catalogs all movies, TV shows and other content globally. With a catalog in the millions, many developers turn to IMDB for their integration needs.

TMDB also offers a free and paid tier. We'll use the free tier for this demonstration.

๐Ÿ“บ
Create your own API key for TMDB here.

Create a REST data source & Cards region for The Movie DB REST data source

  1. Create a new REST data source called The Movie DB using the following URL: api.themoviedb.org/3/search/movie?query=Star Wars&region=en&api_key=[API_KEY]
    Be sure to replace [API KEY] with your API Key

  2. Take all the default options.

  3. Switch over to APEX, create a new page (in this example, we'll use page 20) and create a cards report based on the web service using the following mappings:

Primary Key Column 1ID
TitleTITLE
SubtitleRELEASE_DATE
BodyOVERVIEW
Badge ColumnVOTE_AVERAGE
Media SourceImage URL
URLimage.tmdb.org/t/p/w500/&BACKDROP_PATH.
  1. Run the report and show what it looks like.

Notice that since we hard-coded "Star Wars" into the URL, that's what the report is searching for. We will change that shortly.

Configure pagination for The Movie DB REST data source

Since we did not configure pagination when we created the REST Data Source, we can only see the first 20 records. Let's configure paginations so that APEX will know how to request more records from the web service.

  1. Edit The Movie DB REST data source.

  2. Change the pagination to the following settings:

Pagination Type

Page Number

Page Number URL Parameter

page

Index of First Page

1

Fixed Page Size

20

Total Rows Selector

total_results

  1. Re-run the report and notice that there are far more than 20 records.

Since we're using a Cards report, the data will automatically scroll by default, so there's no need for a next & previous button.

Create a page item & parameter for The Movie DB REST data source

  1. Edit the report page and add a new item โ€“ P20_SEARCH.

  2. Map the new item to the query parameter in the Cards report.

  3. Re-run the report and search for any movie or television show you can imagine!

Change The Movie DB to use a bearer token & web credential

Since putting the API key in the URL is not very secure, we should use an alternative method when possible. TMDB offers an alternative way to authenticate to their web services using a read-access token or JWT.

The value of this token is static, which is not typical. Most sites that use JWTs will require you to get a new token periodically. In any case, it's far safer than using the URL to pass the API key.

We can use a Web Credential in APEX and set that to the value of our token. As long as we set this Web Credential's value after authentication, we should be good.

  1. Edit the Shared Components of your application.

  2. In the Workspace Objects region, click Credentials.

  3. Click Create.

  4. Enter the following values:

Name

TMDB

Static ID

TMDB

Authentication Type

HTTP Header

Now that we have a web credential, we need to point the REST Data Source at it.

  1. Edit The Movie DB REST data source and remove the api_key parameter.

  2. Set the Credentials to TMDB

  3. Save your changes.

Lastly, we need to pass our JWT to the web credential so that when the web service is called, APEX can present the token. We only need this to happen once, so we can use an Application Process that occurs after authentication.

  1. Create an Application Process โ€“ After Authentication

  2. Enter the following PL/SQL code:

apex_credential.set_session_credentials
  (
   p_credential_static_id => 'TMDB'
  ,p_key                  => 'Authorization'
  ,p_value                => 'Bearer ' || 'aaa.bbb.ccc'
  );

Be sure to replace aaa.bbb.ccc with your API read access token from TMDB, which can be found here: https://www.themoviedb.org/settings/api

You will need to logout & login to set the credentials. Once you do that, the search should work as it was before.

Customers

The third and final example is a simple web service based on a table called CUSTOMERS. I've exposed this web service via ORDS, but for this example, we're going to assume that it's not ORDS.

By doing this, we will need to map the pagination manually, as well as use the syntax that ORDS requires to perform server-side searching and sorting.

To keep as real-world as possible, the web service requires OAuth2 credentials to run, so it is not available to the public.

The CUSTOMERS table looks like this:

CUSTOMER_ID

NUMBER

N

FIRST_NAME

VARCHAR2(50 BYTE)

Y

LAST_NAME

VARCHAR2(50 BYTE)

Y

EMAIL

VARCHAR2(50 BYTE)

Y

JOB_TITLE

VARCHAR2(50 BYTE)

Y

HIRE_DATE

DATE

Y

SALARY

NUMBER

Y

To make searching a little easier, I created a view called CUSTOMERS_V. This view concatenates all values into a single column so that we can perform our searches easier, and is what the web service actually references. The SQL for CUSTOMERS_V is:

create or replace view customers_v 
as
select 
  customer_id, 
  first_name, 
  last_name,
  email,
  job_title,
  hire_date,
  salary, 
  customer_id || ' ' 
    || first_name || ' ' 
    || last_name || ' ' 
    || email || ' '
    || job_title || ' ' 
    || hire_date || ' '
    || salary as search 
from 
  customers
/

Create a Web Credential

Before we can create the REST data source, we need to create the Web Credential that we will use to talk to the Customers web service. The steps are similar to what we did for TMDB.

  1. Edit the Shared Components of your application.

  2. In the Workspace Objects region, click Credentials.

  3. Click Create.

  4. Enter the following values:

Name

Customers

Static ID

customers

Authentication Type

OAuth2 Client Credentials Flow

Client ID or Username

WqZcWG4OpbgrJZX1MW2afg..

Client Secret or Password

dVnKMY5T1AkJaNfa0TJoSg..

๐Ÿ›‘
NOTICE! You should never, ever share any type of credential on the public internet like this! I am doing this because this is a demonstration environment, and the credential referenced only allows you to read from the CUSTOMERS table, which is populated with fictitious data. Should this get abused, I can simply disable it and move on.

Create a REST Data Source in APEX from the Customers web service

Now that we have a new Web Credential, we can create the new REST Data Source for the CUSTOMERS web service.

  1. Create a new REST data source using the following URL: bzinxd8saxpiakh-atp.adb.us-ashburn-1.oracle..

  2. Leave Pagination Type alone for now; we can come back to that later.

  3. Set Authentication Required to enabled; then, set Credentials to Customers.

  4. For the OAuth Token URL, enter the following:
    bzinxd8saxpiakh-atp.adb.us-ashburn-1.oracle..

  5. Click Discover.

  6. Click Create REST Data Source.

Let's fix the pagination while we're here.

  1. Edit the Customers REST Data Source.

  2. Set the Pagination Type to Page Size and Fetch Offset.

  3. Next, fill out the following values:

Page Size URL Parameter

limit

Row Offset URL Parameter

offset

Row Offset Type

Rows to Skip

Has More Rows Selector

hasMore

Has More Rows When Value

Equals

Has More Rows Attribute Value

true

  1. Click Apply Changes.

Create a Classic Report on the Customers REST Data Source

Now that we have the REST Data Source created, we can create a new report that maps to it to try things out.

  1. Create a new page - we'll use Page 30 for this example.

  2. On Page 30, create a new Classic Report.

  3. For the Source of the Classic Report, set Location to REST Source and then REST Source to Customers.

  4. Run the page to test drive the Customers web service.

You should see a report on the CUSTOMERS table, complete with working pagination.

Adding Sorting & Searching

Even though it looks like you can sort the report, you're not sorting the entire dataset, only the rows that you have viewed in APEX.

Thus, we need to initiate the sorts (and searches) on the server side. This way, the records are sorted server-side and sent over in blocks of 25 pre-sorted result sets. Thus, we will see the data in the correct sorted order when it's displayed in APEX.

Since the web service is hosted by ORDS, we can refer to its documentation to determine how to call server-side sorting and searching. That document can be found here: docs.oracle.com/en/database/oracle/oracle-r..

Let's add the page items:

  1. Edit Page 30 of your application.

  2. Create a new page item called P30_SEARCH in the Customers region.

  3. In the Settings section, set Submit when Enter Pressed to enabled.

  4. Next, create another page item called P30_SORT. This should be a Select List.

  5. In the Settings section, set Page Action on Selection to Submit.

  6. In the Layout section, set Start New Row to disabled.

  7. In the List of Values section, set the Type to SQL Query.

  8. Enter the following SQL Query:

select
  heading,
  lower(column_alias) as col
from 
  APEX_APPLICATION_PAGE_RPT_COLS
where
  page_id = :APP_PAGE_ID
  and application_id = :APP_ID
  and region_name = 'Customers'
  and column_is_hidden = 'No'
order by
  display_sequence
  1. Set both Display Extra Values and Display Null Value to disabled.

  2. Create another page item called P30_SORT_DIRECTION. This should be a Radio Group.

  3. In the Settings section, enter 2 for Number of Columns and set Page Action on Selection to Submit Page.

  4. In the Layout section, set Start New Row to disabled.

  5. In the List of Values, set the Type to Static Values.

  6. Enter the following values and click OK:

Display ValueReturn Value
ASCasc
DESCdesc
  1. Set both Display Extra Values and Display Null Value to disabled.

  2. Create one more item - P30_JSON. This should start on a new line.

Now that we have the page items, let's create a computation that will take their values of them and convert them to JSON before sending it to the web service.

  1. In the Page Processing tree, create a new Computation that fires After Submit.

  2. In the Identification section, enter P30_JSON for the Item Name.

  3. In the Computation section, set Type to Function Body and Language to PL/SQL.

  4. Enter the following code for PL/SQL Function Body:

if :P30_SEARCH is not null then
  return '{"search":{"$instr":"' 
    || apex_escape.json(:P30_SEARCH) 
    || '"},"$orderby":{"' || :P30_SORT || '":"' 
    || :P30_SORT_DIRECTION || '"}}';
else
  return '{"$orderby":{"' || :P30_SORT || '":"' 
    || :P30_SORT_DIRECTION || '"}}';
end if;
  1. Save and run the page.

Adding and Mapping a REST Web Source Parameter

Lastly, we need to add a parameter to the REST Web Source and then map that parameter to the JSON that we generate when we submit the page. This will enable both the sort and search functionality.

  1. Edit the Shared Components of your application.

  2. Click REST Data Sources, and then click Customers to edit it.

  3. Click the Add Parameter button.

  4. Set the Type to URL Query String, enter q for the Name, and make sure that Omit when value is empty is enabled.

  5. Click Apply Changes.

  6. Navigate back to Page 30 of your application.

  7. Under the Customers region, expand the Parameters tree and select the node labeled q.

  8. In the Value section, set the Type to Item and then enter P30_JSON for the Item.

  9. Save the page and run your application.

At this point, you should be able to search the Customers web service, as well as select which column & order to sort by.

There are just a couple more things that we'll want to clean up before we can call this 100% done.

First, we need to disable APEX-level sorting, as we're doing that server-side now:

  1. Under the Customers region, expand the Columns tree and select all columns.

  2. In the Sorting section, set Sortable to disabled.

Next, we want to hide the SEARCH column. Its only purpose is to make searching easier, so we don't need to render it.

  1. Next, select only SEARCH.

  2. In the Identification section, set the Type to Hidden Column.

  3. Save your changes.

Conclusion

The world of web services is largely standards-based, but implementations will vary greatly from site to site. Learning the basic components and how to interact with them is a critical first step to consuming them in your APEX application.

The APEX team has done a tremendous job in creating a robust set of tooling that should be able to handle anything thrown at it. And when that does fail, you can fall back to the API layer - something we did not need to do with these examples.

Integration with web services is only going to get more important, as an organization's SaaS platforms need to exchange and consolidate data to help people make sound decisions. APEX is an ideal tool to act as the "hub" when data consolidation is a key goal.

๐Ÿ’ป
Want to see these demos in action? Click here.
ย