By Joel Kallman
Oracle Application Express (Oracle APEX) is the low-code application development framework of Oracle Database, enabling the easy creation of modern and responsive web apps. Oracle APEX runs in Oracle Database and provides zero-latency access to your application data. But increasingly in enterprises and in the cloud, the datasource for your application may not be resident in your local Oracle Database instance. Using web and RESTful APIs over HTTP has become the industry-standard method to provide programmatic access to APIs and data in heterogeneous, distributed systems. Oracle Application Express 18.1, with guided step-by-step wizards, now makes it easy to build powerful, feature-rich applications against remote, web, and REST datasources.
In this Oracle Magazine article, you’re going to build a web application on top of the Apple iTunes Search API, which is a simple API over HTTP that takes input arguments via parameters in the URL. You will first create a report against the iTunes music video catalog. Next, you will simplify the report to include a preview of the music video and a link to watch a clip of the music video. Finally, you will add a form field to the Oracle APEX application to permit dynamic searching of the iTunes music video catalog.
This article’s sample application is built in Oracle Application Express 18.1. If you’re not already running Oracle Application Express 18.1 or later locally, you can request a free workspace at https://apex.oracle.com. Alternatively, you can sign up for a free trial of Oracle Database Cloud Service. Oracle APEX can be used wherever you have an Oracle Database instance, including every Oracle Database cloud service.
Creating the Application
Begin your exploration of RESTful web services with Oracle Application Express 18.1 by creating the initial application.
If you were to run the application now, it would look a bit empty, with nothing but a home page. But now is where the fun begins. You first need to create a Web Source Module for the iTunes Search API. A Web Source Module is a shared component in Oracle APEX, and it is used to maintain the definition of a remote datasource—the URL of the REST endpoint, data type mapping, credentials, and more. APIs returning JSON are the lingua franca of the internet, and using the native JSON capabilities of Oracle Database, the Oracle APEX engine can efficiently parse and process JSON results.
You have now created a shareable component, a Web Source Module, which will return a list of music videos that include Beyoncé in any of the searchable attributes. Figure 1 shows a preview of the data profile for this module.
Figure 1: The data profile of the JSON response, during Web Source Module creation
Now you will expose this in your Oracle APEX application by creating a classic report on the Web Source Module.
Voilà! You just created a page in your Oracle APEX application that will display the results of the iTunes Search API, searching for Beyoncé. Click the Run Application icon at the upper right to run your newly created Oracle APEX application and report. Using the same credentials you used to log in to your Oracle APEX workspace, authenticate yourself to the application and click the iTunes navigation menu entry on the left. Scroll to the far right and paginate forward to see the second set of results.
Your application should look similar to Figure 2.
Figure 2: The initial report application page
Refining the Report
Improve the display of the report page by reducing the number of columns and making use of graphics.
About Oracle Application Express
Oracle Application Express (Oracle APEX) is a high-productivity, low-code platform for creating modern, responsive, and accessible web applications. A no-cost feature of Oracle Database, it is a compelling application development platform available in all Oracle Database Cloud services. |
Once the results of a REST service are fetched by Oracle APEX, you can perform postprocessing on the results, using ordinary SQL. With postprocessing you can reduce the number of columns in the SELECT clause, limit the results further by adding a WHERE clause, or even join results to local tables. In this article, you will use a SQL postprocessing mechanism to reduce the number of columns displayed in the report.
select ARTISTNAME, TRACKNAME, PREVIEWURL, ARTWORKURL100, COLLECTIONPRICE, RELEASEDATE, COUNTRY, CURRENCY from #APEX$SOURCE_DATA#
Your application now includes a much-better-looking report, with an image of the video preview. Additionally, if you click the image, it should open a new tab in your browser and start playing the video.
Your application should look similar to Figure 3.
Figure 3: The application with a reduced number of columns in the report and a clickable preview of each associated video
Making the Report Searchable
You have produced a nice report that shows all of Beyoncé’s videos on iTunes, and it fetches these results dynamically via the iTunes Search API on the internet. The last remaining step is to make this report searchable, so that the end user can search for any video, not just Beyoncé’s.
Your application should look similar to Figure 4. Enter any search term you wish, such as tupac or your favorite music artist, and click Go. You now have an application that uses the iTunes Search API, which makes a request via HTTP, returns the results in JSON, and parses the results before further manipulating the results via SQL postprocessing.
Figure 4: The application with a search field for dynamic searching of the iTunes music video catalog
Conclusion
Oracle Application Express enables you to create applications that integrate data from local Oracle Database tables and remote datasources. These remote datasources can be simple HTTP data feeds, as in this article, but they can also be OAuth2 or Basic Authentication–protected resources, REST services provided by Oracle REST Data Services, or even Oracle cloud applications (SaaS) REST services. Oracle APEX also provides, uniquely, the ability to manipulate the data from remote datasources with industry-standard SQL. The full breadth of Oracle Database SQL functionality is at your disposal to properly present the results of remote data. All of this is accomplished with no code other than a simple SQL statement. Now that’s low code!
Next Steps
DOWNLOAD more about Oracle Application Express 18.1.
REQUEST more about a free Oracle Application Express workspace.
READ more about
Oracle Application Express.
what’s new in Oracle Application Express 18.1.
TRY Oracle Database Cloud Service.
Illustration by Wes Rowell