The role of the developer has never been as important as it is today. If you’re an Oracle database developer, then PL/SQL and SQL are already skills that you possess. In addition to these skills, you should already be using Oracle Application Express to build beautiful low code applications with ease, while leveraging all the great features in the Oracle database.
One of the best combinations of toolkits available to technologists today is the Oracle RAD stack. While RAD is a well-known acronym for rapid application development, Oracle is using this acronym for a combination of the following Oracle technologies:
Rest Data Services
Application Express
Database
What I find personally attractive about the RAD stack is that for starters, it’s free. You can use the free Oracle 18c Express Edition (18c XE) as your database layer, and while it has some limitations on how much CPU, memory, and storage is available, it still includes almost all of the same features as Enterprise Edition. Some of my favorite features are:
- Multitenant
- In-Memory
- Partitioning
- Advanced Analytics
- Advanced Security
Continue on with Oracle Application Express, which lives inside of the Oracle database and is a low code development tool. If you are a current developer using PL/SQL, you are already an APEX developer. Once you learn some of the basic navigation and functionalities of the tool, you’ll be amazed after a few short weeks how quickly you get the hang of using APEX.
When you add the final piece—Oracle Rest Data Services (ORDS)—to this puzzle, you now have the ability to REST-enable your database objects. ORDS will translate those common HTTP(S) commands (GET, POST, PUT, DELETE) into easy-to-understand database commands (SELECT, INSERT, UPDATE, DELETE). While transforming the output of the SQL result set into JSON. Those familiar with APEX will recognize ORDS as the Oracle APEX web listener. The fact is, ORDS can run without APEX, so you can take advantage of RESTful web services in your Oracle database.
The best part of the RAD stack is that it can be leveraged immediately to prototype solutions for your current business needs. The beauty of Oracle Application Express is that you can easily prototype an application and then use an agile approach to make iterative changes extremely quickly. Since it’s extremely low code, changes are typically fast and easy, and it’s not uncommon to create applications in minutes or just a few hours as compared to days of normal development efforts using other tools. Sure, complex applications can also be created too, and that will take longer, but the point is you don’t have to implement all the bells and whistles right at the outset—you can add them in later development cycle iterations.
APIs are Ruling the World!
At the moment there is an explosion in the area of application programming interfaces (APIs). All IT professionals and especially developers need to understand and leverage the creation and use of APIs. A lot of these APIs are free while some of them can be accessed through a service for a fee. Just because they are free does not mean they are not worthy of using for meaningful analytics or user applications.
Additionally, with the use of ORDS, you can easily create your own APIs by REST-enabling your own database objects such as tables, views, or PL/SQL, and that allows you to easily communicate with other pieces of technology inside your business or even expose them, securely of course, to the outside world.
Some API sites will require you to request special tokens that are used at the time of an API request. Additionally, you may find that you will need to configure an Oracle HTTPS wallet that will contain trusted certificates of the site that is providing the API.
For example, we can see the growth in WEB APIs from Programmable Web has grown at a staggering rate, surpassing over 21K available APIs for you to use.
Putting It All Together
Now, let’s look at how we can get this all working. For these examples I’ve installed the Oracle DB, ORDS, and APEX on my Windows laptop using traditional installation methods, but of course I could have leveraged other variants that are possible nowadays using Vagrant, VirtualBox, Docker Containers, or even the new Oracle Cloud Always Free tier.
The Oracle RAD stack version used for my demonstrations were Oracle DB 18cXE, ORDS 18.4, and APEX 18.2. This should also work for versions higher than this, but I can’t guarantee that this functionality will work with versions lower than this, as some of these features are recent additions.
Starting with APEX 18.1, the ability to call REST services directly for read-only type components, like charts and interactive reports, is available. This is through the new feature of Web Source Modules. What I like about this is I now have the ability to call the APIs directly in APEX and use the great charting or interactive report features to look at data.
For my data set, I’ve decided to use one of the open APIs available from the City of Chicago, from the Chicago Data Portal, https://data.cityofchicago.org/ I’ve chosen one that I thought was interesting, the Affordable Rental Housing Developments. This data point has been described as:
The rental housing developments listed below are among the thousands of affordable units that are supported by City of Chicago programs to maintain affordability in local neighborhoods.
Let’s take a look at how quickly we can put together an application using this data set with Oracle APEX using an example use case of an organization that is helping a family find some affordable housing.
Oracle Wallet
The first thing that needs to be done is to create an Oracle wallet to hold the trusted certificates for the site I plan to pull data from. In this case, I’ll navigate with my browser to https://data.cityofchicago.org and download both the root as well as the intermediate certificates. I typically save these to the same directory as the location of the wallet.
Opening a Windows command prompt, I’ll create a new Oracle wallet like this:
Next, I’ll import my two certificates.
As the Oracle APEX instance owner, I’ll navigate to Manage Instance > Instance Settings and configure the location of my Oracle Wallet.
Creating an APEX Application
Once I’ve logged into APEX, I’ll create a new Application.
I click the menu App Builder > Create
Then I click on New Application and Next.
I enter a name for my application and leave the remaining options as defaulted.
At this point, I have only have a skeleton of an application. I’ll set up access to my data via the public API available from the City of Chicago. To do this, I’ll need to click on Shared Components.
Under the Data Sources option, I click Web Source Modules.
Next, click on Create and then Next.
Here I enter in my Web Source information as seen below. The URL for the data used is https://data.cityofchicago.org/resource/s6ha-ppgi.json
On the Create Web Source page, it will automatically determine the Base URL and Service URL Path. I then click on Next.
Finally, I click on Discover.
Here we can see that a preview of the data is presented to us. I then click on Create Web Source.
Now my Web Source Module is ready to be used in my application.
Within the APEX application, I’ll create a new page for an interactive report. Click on Create Page.
I’ll choose Report and click on Next.
I’ll choose Interactive Report and then click on Next.
I’ll fill out the page attributes with the following and then click Next.
I’ll choose the option to Create a new navigation menu entry and click on Next.
For the Report Source, I’ll choose Web Source, choose the columns, and enter my Web Source Module name that we created in the earlier step. Then click on Create.
Now we can see that we have retrieved all of the data from our data set from the API.
Let’s create a chart from the data set to be able to visually see what’s available by community in Chicago.
In this simple example, I’ll create a bar chart based on the number of units available in each community.
Wow, that was incredibly simple! Now I’ll save the underlying data as a report:
I’ll give the report a name, Number By Community, then click Apply.
Now that I’ve saved the report, in the future I can quickly retrieve it, and have it display my chart without having to re-create it. Best of all, remember none of the data resides locally in our database—it’s all being pulled from an external web source.
Upping Our Game, Geographically
Let’s see if we can step it up a notch. How much work would it take to put together a visual representation within Google Maps? Fortunately, the APEX community is a far-reaching, global community with free resources available on numerous sites, including volumes of easy-to-use plug-ins.
I found a Google report map plugin by Jeffrey Kemp here, and once I downloaded it, I installed the plugin directly into APEX and then set up the data from the external web source to let us show a visual representation of where these developments are located.
Note: Using this plugin will require that you obtain a Google Maps API key. This is free once you sign up for a developer account with Google. See this link for instructions on how to sign up for that account in a matter of minutes.
Next, back in my APEX development environment I’ll navigate to Shared Components > Plug-ins:
Click on Import
Choose the SQL file for the plugin and click on Next.
You’ll need to enter your Google API key on this screen.
Now that we have the plugin installed, we’ll need to add an SQL statement to retrieve the data to create pins on our map. In order to do this, we’ll need to create a few database objects to allow us to SELECT the data elements, including latitude and longitude, which is needed for the pins on the map. Again, since the data doesn’t live natively in our database, we must create an object type and function to be able to retrieve it properly.
The code that I used was provided by Carsten Czarski on the Oracle APEX blog. This fits the bill and kept me from having to re-invent the wheel (which would have been mostly square if I had tried). For the map, I only need some of the data points. Obviously, we’ll need latitude and longitude for the pins, but I’ll also add the community, address, management company, phone number, property type, and number of units.
Now that we have our database objects created, let’s go and add a new region to our application that includes the map. Navigate to Page two of our new application. Right-mouse click on Regions and choose Create Region.
In the Region section to the right, we’ll add a Title and change the Type to JK64 Report Google Map R1 [Plug-In]. We’ll also add a query in the SQL Query section to retrieve our data for the map.
For the query, I’ve decided to add some HTML tags to help format it better when you click on the pin for the map.
In the attributes of the map region under the section titled Layout, I increase the number of rows to 500 (default is 15). If you leave it at the default it will only show you 15 pins.
Now we have all the locations on an easy-to-navigate map. When you click on a pin, it will provide you with all the information you would need about the housing development.
Summary
As has been demonstrated, with just a little bit of work, I was able to come up with a nice little application. I am not a developer; my daily job is far removed from creating applications or reports. Those with a development and/or analytical background will be able to create much more sophisticated application components than these examples! With the Oracle RAD stack, you have a free and capable toolkit available that will get you building applications in no time.
Make sure to learn APIs and the value that they may bring to your company. We were able to use a publicly available API providing us with a free source of data. What can be accomplished with APIs is up to you to dream up. Dream even bigger, and who knows—you could become your company’s new application or analytics hero.
About the Author:
Alfredo Abate is an Oracle ACE that has been using Oracle technologies since 1999. He is currently the President of the Chicago Oracle Users Group (COUG) and President of the Oracle Real Application-Special Interest Group (RAC SIG). He has presented at COUG, NCOAUG, GLOC, OATUG, MOUS, Collaborate, and recently at KSCOPE19. He currently works for a manufacturing company in the suburbs of Chicago where he is responsible for and enjoys working with a full suite of Oracle technologies. You can follow Alfredo on Twitter @HeyAlfredoDBA
