There have been many new REST features and improvements added to APEX over the past few releases, so I thought now would be a good time to take a look at some of the newer REST features available within APEX in a series of blogs.
In my first blog I’m going to take a look at how you can use REST data sources for the List of Values shared component. This feature was introduced version 19.2 along with declarative display/return mappings for columns.
There are two options available for using REST as a data source for LOVs.
Let’s start by building an LOV using a Web Source as the data source. I already have a GET method Web Service to return employee data, so the first step is to declare it in my APEX application as a new Web Source Module.
Now we have a web source declared we can use it to create a list of values to use in our APEX application for item types such as Popup LOV, Select List, etc.
Now we have a List of values based on a web source we can test it by adding a Popup LOV item to an APEX page. In this example I have added a Popup LOV item to the page using the following properties.
|Display As||Modal Dialog|
|List of Values|
|List of Values||EMPLOYEES|
When I run the page and activate the Popup LOV I get a list of all employees returned via the web source with the ability to filter on the data by default.
With the new multiple column mapping feature added to APEX 19.2 we can also include additional columns from the Web source to our LOV. For example I would like to see the first name, last name, email address and commission percentage when selecting an employee. To do this we can re-open the declared LOV within shared components and use the “Additional Display Columns” option to select the extra columns to display.
Now when we return to our test page and activate the popup LOV we can see the additional column, which are searchable by default.
However, there are some more features we can use to improve the use of the Web Source. I would like rename the column headings, return the full employee name into the item and restrict the LOV to employees that have a commission percentage.
select EMPLOYEE_ID, FIRST_NAME || ' ' LAST_NAME FULL_NAME, EMAIL, COMMISSION_PCT from #APEX$SOURCE_DATA# where NVL(COMMISSION_PCT,0) > 0
Now when we return to our test page and activate the popup LOV we can see it displays and returns the full employee name, has our defined column headings and excludes employees with no commission. By default we can also search on any displayed column.
We can also create an LOV for a REST Enabled SQL data source. I already have another Oracle database that has been enabled for REST Enabled SQL, so the first thing I need to do is define my data source within shared components.
Now we have declared REST Enabled SQL data source we can use it to create a List of values.
Now we have a List of values on a REST Enabled SQL Service we can test it. This time I will add a Select List item to the page using the following properties.
|List of Values|
|List of Values||CUSTOMER|
When I run the page and activate the Select List I get a list of all customers returned via the REST Enabled SQL Service.
As we did with the LOV based on the Web Source you add multiple columns to an LOV based on a REST Enabled SQL Service. However, there is no option to use Post Processing SQL as this is unnecessary given you can write the SQL query as required when you create it.
In my next blog I will be taking a look at REST enabled Forms, so if that’s an area you want to know more about please check back in a couple of weeks.
Author: Colin Archer
Job Title: Senior Oracle APEX Development Consultant
Bio: Colin is a Senior Development Consultant at DSP-Explorer with 20 years’ experience of analysis, design, and development of bespoke Oracle applications for a wide variety of business functions. Building on his previous experience of Forms and PL/SQL he is now focusing on developing high quality fit for purpose solutions using APEX.