The REST Data Source functionality in Oracle APEX has evolved significantly over the past few years and has become a great tool to allow for an easy way for developers to work with external data sources with minimal effort and little (if any) custom code. In this blog, I will go through some of the tips that will help you get the most out of REST Data Sources with APEX.
If you are using a REST Data Source in a:
List of Values (LOV)
Interactive Report
Interactive Grid
and your REST API supports query string parameters, for example:
?query={query-term}
Although this can be used for any query parameter, such as name if applicable, you can easily integrate it into your region/item. This can be done using the 'Use for Row Search' switch in the query parameters settings. It is worth noting that only one parameter can be set in this manner.
Once enabled:
For LOVs: The parameter will be marked as Use for Search, and APEX will automatically pass the search term.
For Interactive Reports and Grids: The parameter is automatically attached to the region, and when querying using the built-in search functionality, this will be added to the URL path, i.e. "prepared Query String: query=TEST&".
Also, if you're trying to find where REST Data sources like the above are used in your app, you can find information under the ‘Utilization tab’, which uses the wwv_flow_restds_usage_dev APEX view.
When creating a new REST data source, there have been times I have configured things correctly on initial setup, but the data that is showing incorrectly is due to the format of the JSON payload, as APEX can default to using the first array as the row selector, in this case, vendor.
For example, this JSON
{"ledgerBatchId": "LB-2026-00482","companyCode": "US-NY-01","postingDate": "2026-03-01","documentCurrency": "USD","exchangeRate": 1.0000,"vendor": [{"vendorId": "VND-78341","vendorName": "Hudson Industrial Supplies LLC","taxRegistrationNumber": "98-7654321"}]}
Row selector defaulted to vendor, which produced 3 columns:
To resolve this, update the row selector to "." which should hopefully resolve this issue and select the correct number of columns.
The APEX_REST_SOURCE_SYNC package allows for manually triggering a synchronization with the associated table of a REST data source. The interactive report page below can be easily set up using this package.
To get the last synchronisation time, we can use the code below to populate a data item:
SELECTto_char(apex_rest_source_sync.get_last_sync_timestamp(p_module_static_id => 'rest_synchronization_demo'),'DD/MM/YYYY HH24:MI') syncFROMdual
To synchronise the data manually using the Sync Data button, we can use the code below:
BEGINapex_rest_source_sync.synchronize_data(p_module_static_id => 'rest_synchronization_demo');END;
There is also the option to call the code from outside of your app and run the synchronisation process in the background with different procedure parameters.
As part of your synchronisation setup, you may want to have the data put into the table with modifications to the data before inserting. To do this, APEX provides the ability to create additional columns.
The first allows your REST source to access your own lookup tables and assign data based on matching IDs, for example.
The second, derived columns, allow you to have more control over what data you want for this new column, including the use of functions to generate custom data based on the REST data:
Having the columns here also helps if there are multiple references to this REST endpoint without the need for a view or custom code for each time the REST endpoint is referenced. If you are also making a lot of changes to your REST data sources here, another tip is to check this setting on the right-hand side of your screen:
Further functionality for your current Fusion apps can be added very easily by creating a custom APEX application on the same database your Fusion is hosted on. This integration can be easily enabled using APEX's REST data sources communications with Fusion REST APIs, and as of APEX 24.2, adding this functionality has been made simpler with a new template app, which will set up a remote server, a web credential, an authentication scheme and a REST data source.
Because of Fusion's wealth of API endpoints and APEX's option to add in Oracle Cloud Applications (SaaS) REST Service data sources, the data can be easily ingested and used for custom reporting and data processing through your own custom app. With this integration, you can also make use of the business logic and querying on your Fusion instance, as well as easily update your data profile and reporting when needed on your APEX companion app with no code needed.
As integration between systems becomes increasingly important, APEX continues to provide a secure, low-code solution for working with external data sources. Another way to greatly add to the functionality of your apps using external sources is to add generative AI, which can now be configured as easily within your APEX instance. My colleague goes into great detail on this in his blog here, and with APEX's REST data sources toolkit, you could also look at using your own custom AI integration. If you’re already working with REST in APEX, hopefully these tips help you refine and optimise your implementation even further.
For more information, check out our Oracle APEX Services, or contact us today, and one of our expert developers will be in touch.