Integrate application data from anywhere with Oracle Application Express 18.1.

By Joel Kallman

September/October 2018

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.

1. In Oracle Application Express, click the App Builder tab.
2. Click the Create icon and then the New Application icon.
3. Enter APEX and REST for Name, and click the Create Application button.

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.

1. From within Application Builder, click the Shared Components icon.
2. In the Data Sources region, click Web Source Modules and then Create. You will want to create this “from scratch,” so click Next.
3. For Web Source Type, choose Simple HTTP (the default). Enter iTunes Search for Name, and for URL Endpoint, copy and paste the URL https://itunes.apple.com/search. Click Next.
4. Oracle APEX will parse the URL into its Base URL and Service URL path. Click Next again.
5. Because authentication will not be required for this API, leave the value for Authentication Required as the default No and click Advanced.
6. Choose a Parameter Type of Query String variable. For Parameter Name, enter term. For Value, enter beyonce.
7. On the second row, choose a Parameter Type of Query String variable. Enter a Parameter Name of entity. For Value, enter musicVideo. Change Is Static to Yes, and click Discover.
8. Oracle APEX will invoke the REST API and attempt to parse the results and create a data profile. Click the Data Profile subtab to view the entities parsed from the JSON result and see how they will be mapped to specific column names and data types. Click Create Web Source.

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.

Now you will expose this in your Oracle APEX application by creating a classic report on the Web Source Module.

1. Click the Application breadcrumb in the upper left to return to the top-level definition of your application in Application Builder.
2. Click Create Page. Click Report and then Classic Report.
3. Enter iTunes for Page Name, and then click Next.
4. Select Create a new navigation menu entry, and click Next.
5. For Data Source, choose Web Source. Then select iTunes Search for Web Source Module. Click Create twice.

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.

Refining the Report

Improve the display of the report page by reducing the number of columns and making use of graphics.

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.

1. In the developer toolbar at the bottom of the page, click the Edit Page 2 link.
2. In the tree view on the left of Page Designer, click Report 1 to select the report region.
3. In the properties on the right, in the Local Post Processing section, choose SQL Query for Type.
4. In the SQL Query region, overwrite the existing query with a copy-and-paste of the following query:

   select ARTISTNAME,
             TRACKNAME,
             PREVIEWURL,
             ARTWORKURL100,
             COLLECTIONPRICE,
             RELEASEDATE,
             COUNTRY,
             CURRENCY
        from #APEX$SOURCE_DATA#
   

5. In the Appearance section of the properties on the right, choose Blank with Attributes for Template. This will simplify the region display and remove the unnecessary region title.
6. In the tree view on the left of Page Designer, under the report region, expand the column list by clicking > to the left of Columns.
7. Select ARTISTNAME, and change the heading to Artist. Change the other headings: TRACKNAME to Track, COLLECTIONPRICE to Price, RELEASEDATE to Release Date, and COUNTRY to Country.
8. Select the ARTWORKURL100 column, and in the properties on the right, change Type to Hidden Column.
9. In the tree view on the left, select the CURRENCY column. In the properties on the right, change Type to Hidden Column.
10. In the tree view on the left, select the PREVIEWURL column. In the properties on the right, change Heading to Preview. Change Type to Link.
11. In the Link region of the PREVIEWURL column, click No Link Defined for the Target attribute. In the dialog box for the Link attribute, change Type to URL. In the URL attribute, enter #PREVIEWURL#. This will create an HTML anchor to the value of the Preview URL returned in the REST results. Click OK.
12. For Link Text, enter <img src="#ARTWORKURL100#"/>. This will display the image returned in the value of the Artwork column in the REST results.
13. For Link Attributes, enter target="_new". This will cause the video preview to open in a new browser tab when clicked.
14. In the tree view on the left, select the COLLECTIONPRICE column. In the properties on the right, enter the HTML expression #COLLECTIONPRICE# #CURRENCY#. This will include the currency code after the price value.
15. Click the Run Application icon at the upper right to save all your changes and run your Oracle APEX application.

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.

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.

1. In the developer toolbar at the bottom of the page, click the Edit Page 2 link.
2. In the Component Gallery at the bottom of the Page Designer page, ensure that Items is selected. Select the Text Field icon, drag it up to the ITEMS section of the Report 1 region in the Page Layout area, and drop it.
3. In the properties on the right of Page Designer, for Name, enter P2_SEARCH, and for Label, enter Search.
4. In the Component Gallery at the bottom of the Page Designer, ensure that Buttons is selected. Select the Text icon, drag it to the right of P2_SEARCH in the Report 1 region in the Page Layout area, and drop it.
5. In the properties on the right of Page Designer, enter GO for Button Name and Label. In the Appearance section, change Hot to Yes. Click Template Options, change Size to Large and Spacing Top to Large, and then click OK. In the Behavior section, change Action to Defined by Dynamic Action.
6. In the tree view on the left of Page Designer, select P2_SEARCH, hover over it, and right-click the mouse to display the context-sensitive menu. Choose Create Dynamic Action.
7. In the attributes on the right of Page Designer, change the dynamic action Name to Refresh Report with Search Term. In the When section, change Event to Click; change Selection Type to Button; and for Button, select the newly created Go button.
8. In the tree view on the left of Page Designer, select True for the dynamic action (it will be Show). Change Action to Refresh; change Selection Type to Region; and for Region, select Report 1.
9. In the tree view on the left of Page Designer, under the Report 1 report region, expand the parameter list by clicking the > to the left of Parameters.
10. Select the term parameter. In the properties on the right of Page Designer, change Type to Item. For Item, enter P2_SEARCH, which is the name of your search term item.
11. Select the Report 1 report region in the tree view on the left of Page Designer. In the properties on the right, for Page Items to Submit, enter P2_SEARCH. This will ensure that the value of the P2_SEARCH text item will be submitted to the Oracle APEX engine when the region is refreshed.
12. Click the Run Application icon on the upper right to save all your changes and run your Oracle APEX application.

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.

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