🛑
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: **https://bzinxd8saxpiakh-atp.adb.us-ashburn-1.oraclecloudapps.com/ords/kscope/customers/customers**
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:
**https://bzinxd8saxpiakh-atp.adb.us-ashburn-1.oraclecloudapps.com/ords/kscope/oauth/token**
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: 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:
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:
```sql
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 Value | Return Value |
| --- | --- |
| ASC | asc |
| DESC | desc |
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:
```sql
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.