Overview

Photo by Alan Tang on Unsplash

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.

Use Postman to explore the URL for the Movie DB:
https://api.openweathermap.org/data/2.5/forecast/daily?q=Austin&cnt=14&units=imperial&appid=[API KEY]
Be sure to replace [API KEY] with your API Key
Create a new REST data source for the Movie DB using the same URL.
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.

Edit The Movie DB REST data source
Edit the Data Profile
Click the Add Column button
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
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.

Create a new page in your application for the chart. We'll use page 10 in this example.
Edit Page 10 of your application.
Create a new Chart region and set the type to Combination.
Under Settings, set the Time Axis Type to Enabled.
Set Legend to Show and set Position to Bottom.
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

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

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.

Create a new page item – P10_SEARCH.
Expand all four Series and then expand the Parameters section for each of them.
Select all four instances of “q” and set the Type to Item and the Item to P10_SEARCH.
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

Create a new REST data source called The Movie DB using the following URL: https://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
Take all the default options.
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 1	ID
Title	TITLE
Subtitle	RELEASE_DATE
Body	OVERVIEW
Badge Column	VOTE_AVERAGE
Media Source	Image URL
URL	https://image.tmdb.org/t/p/w500/&BACKDROP_PATH.

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.

Edit The Movie DB REST data source.
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

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

Edit the report page and add a new item – P20_SEARCH.
Map the new item to the query parameter in the Cards report.
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.

Edit the Shared Components of your application.
In the Workspace Objects region, click Credentials.
Click Create.
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.

Edit The Movie DB REST data source and remove the api_key parameter.
Set the Credentials to TMDB
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.

Create an Application Process – After Authentication
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.

Edit the Shared Components of your application.
In the Workspace Objects region, click Credentials.
Click Create.
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.

Create a new REST data source using the following URL: https://bzinxd8saxpiakh-atp.adb.us-ashburn-1.oraclecloudapps.com/ords/kscope/customers/customers
Leave Pagination Type alone for now; we can come back to that later.
Set Authentication Required to enabled; then, set Credentials to Customers.
For the OAuth Token URL, enter the following:
https://bzinxd8saxpiakh-atp.adb.us-ashburn-1.oraclecloudapps.com/ords/kscope/oauth/token
Click Discover.
Click Create REST Data Source.

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

Edit the Customers REST Data Source.
Set the Pagination Type to Page Size and Fetch Offset.
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

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.

Create a new page - we'll use Page 30 for this example.
On Page 30, create a new Classic Report.
For the Source of the Classic Report, set Location to REST Source and then REST Source to Customers.
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: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/23.1/orddg/developing-REST-applications.html#GUID-091748F8-3D14-402B-9310-25E6A9116B47

Let's add the page items:

Edit Page 30 of your application.
Create a new page item called P30_SEARCH in the Customers region.
In the Settings section, set Submit when Enter Pressed to enabled.
Next, create another page item called P30_SORT. This should be a Select List.
In the Settings section, set Page Action on Selection to Submit.
In the Layout section, set Start New Row to disabled.
In the List of Values section, set the Type to SQL Query.
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

Set both Display Extra Values and Display Null Value to disabled.
Create another page item called P30_SORT_DIRECTION. This should be a Radio Group.
In the Settings section, enter 2 for Number of Columns and set Page Action on Selection to Submit Page.
In the Layout section, set Start New Row to disabled.
In the List of Values, set the Type to Static Values.
Enter the following values and click OK:

Display Value	Return Value
ASC	asc
DESC	desc

Set both Display Extra Values and Display Null Value to disabled.
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.

In the Page Processing tree, create a new Computation that fires After Submit.
In the Identification section, enter P30_JSON for the Item Name.
In the Computation section, set Type to Function Body and Language to PL/SQL.
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;

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.

Edit the Shared Components of your application.
Click REST Data Sources, and then click Customers to edit it.
Click the Add Parameter button.
Set the Type to URL Query String, enter q for the Name, and make sure that Omit when value is empty is enabled.
Click Apply Changes.
Navigate back to Page 30 of your application.
Under the Customers region, expand the Parameters tree and select the node labeled q.
In the Value section, set the Type to Item and then enter P30_JSON for the Item.
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:

Under the Customers region, expand the Columns tree and select all columns.
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.

Next, select only SEARCH.
In the Identification section, set the Type to Hidden Column.
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.

APEX on the Edge

Comments (1)

More from this blog

Surfacing Invisible Business Rules with SQL Assertions & AI

Second Time Around

Using SQLcl Projects

Using Claude Desktop to Create Dashboards for APEX

Exploring MCP Server for Oracle Database & APEX

Overview

OpenWeather

Create a REST data source for OpenWeather

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

Create a chart based on the OpenWeather REST data source

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

The Movie DB

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

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

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

Customers

Create a Web Credential

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

Create a Classic Report on the Customers REST Data Source

Adding Sorting & Searching

Adding and Mapping a REST Web Source Parameter

Conclusion

Command Palette

Comments (1)

More from this blog

Overview

OpenWeather

Create a REST data source for OpenWeather

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

Create a chart based on the OpenWeather REST data source

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

The Movie DB

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

Configure pagination for The Movie DB REST data source

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

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

Customers

Create a Web Credential

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

Create a Classic Report on the Customers REST Data Source

Adding Sorting & Searching

Adding and Mapping a REST Web Source Parameter

Conclusion