REST Improvements for APEX since 20.1

It’s been a while since I last wrote a blog on REST features in APEX, in fact, it was way back in 2019! Since 2020 there have been five APEX releases, which collectively contain quite a few new and improved REST features. I thought now would be an excellent time to take a look at them.

REST Data Source Synchronization

In APEX 20.2 the new feature REST Data Source Synchronization was released. This improvement introduces data synchronisation from a REST data source to a local table, allowing you to use the returned data like any other oracle table or view. It is very easy to configure using the declarative interface and synchronization can be run on a scheduled at the desired frequency or executed programmatically via the API APEX_REST_SOURCE_SYNC.

In my application, I have already configured a REST data source and built an interactive report over the returned data. I have also edited the data profile and set the primary key, which is important when using synchronization.

I now want to configure the synchronisation of the REST Data Source to a local table and schedule it to refresh daily. To do this I need to navigate into Shared Components, open my data source and select the Manage Synchronization option from the right-side menu.

The first step is to enter the name of the new table (which is going to be BEER_LOCAL) and then click on Create Table. APEX will then use the data profile to create an empty table for the returned data.

Next, I need to configure the synchronization details and schedule. As my data source has a primary key, I will use the Merge type to update/create my local table as needed and will set the schedule to run daily at 9:30am. Then I simply click on the Save and Run button to activate the schedule and populate my local table with a call to the REST Data Source.

The final step is to ensure I update the source settings of my interactive report and turn on the Use Synchronization Table option to ensure the local table is used instead of calling the REST Data Source.

CSV Data Support

The ability to create a REST Data Source over an API that returns CSV data was introduced in APEX 21.1 This improvement enables you to use a CSV file returned via an API as a table, making it simple to create reports and charts etc. over the data.

The Synchronization feature can also be used on CSV REST Data Sources, meaning it can be used as a simple way to schedule importing of data into a local table for processing.

REST Data Source Conversions

Another useful new feature released in APEX 21.1 was the addition of built-in data conversions for REST Data Sources. These are configured by opening the Data Profile of your REST Data Source and editing the column you want to apply a conversion to. Here you will find the option to specify a conversion rule within the Transformation section.

This allows you to add rules to trim whitespace, convert case, perform replacements etc.

REST Catalogs and Improvements

APEX 21.2 introduced REST Source Catalogs which allow you to create catalog at the Workspace level of REST Data Sources within the individual applications. To create a new catalog select REST Source Catalogs from the Workspace Utilities menu. Each catalog must belong to an organisational group, therefore you must first create one using the Manage Catalog Groups option in the right-side menu. Once you have a group you can use the Create Catalog button to add a new catalog to the workspace.

To add REST Data Sources to a catalog open it from the REST Data Sources menu within Shared Components and use the Save to Catalog button to select the catalog to use.

When creating new data sources within an application, developers can browse the catalog and select data sources from other applications. The metadata from the Catalog is then used to create copies of the selected Rest Data Source within the current application. Once a Data Source has been added from a catalog, it can be refreshed from the REST Source Catalog section.

A catalog can also be exported from one workspace and imported into another, making it easy to share REST Data Catalogs and their REST Data Sources.

REST Sources Support for REST Enabled SQL Query

One improvement I think is very useful was introduced recently in APEX 22.1, this is support for using REST Enabled SQL as a REST Data Source. You can now create a REST Data Source over a REST Enabled SQL, which gives all the benefits a REST Data Source has that a REST Enabled SQL Query doesn’t, such as:

• Local Post Processing
• REST Source Catalogs
• REST Data Synchronization

Once you have configured your REST Enabled SQL connection, select the option to create a new REST Data Source from scratch. For the data source type, use REST Enabled SQL Query and then enter a name and select the REST Enabled SQL Reference.

Next enter the query you wish to run on the remote database.

Select the Authentication credentials to use.

Click on Discover to return sample data and the data profile, then click Create REST Data Source to finish.

Now the REST Data Source is created, you can use it like any other, such as creating an Interactive report with local post-processing, adding it to a REST Source Catalog or even setting up a REST Data Source Synchronization.

Other Improvements

If you would like to speak to one of our Oracle APEX experts, get in touch through enquiries@dsp.co.uk or book a meeting...