Unlocking the Power of REST Services in Oracle APEX
Before diving into the exciting journey of prototyping, it's crucial to lay the groundwork by enabling REST Services for the database objects imported into Oracle APEX. In the real world, this typically involves two paths: 1) leveraging existing REST APIs to integrate functionalities (like incorporating freecurrencyapi which wel'll do later on); or 2) REST-enabling database objects in a remote database schema to then utilize those REST endpoints for defining REST Data Sources in an application where APEX is installed, within the local database. Let's embark on this exploration together, as outlined below.
In our adventure, we'll navigate both terrains. However, for the latter, we'll focus on REST-enabling services for our local database and use these datapoints in our app. Once we define the REST Data Source in an APEX application, the magic happens: instead of calling a local database object, the APEX Engine reaches out to the REST endpoint, fetching data from the remote database. We'll also introduce PUT, POST, and DELETE handlers, empowering our APEX application to perform updates, inserts, and deletes on the remote database objects.
After REST-enabling our tables and views, we'll define REST Data Sources against REST endpoints and embark on building an application that harnesses the power of REST Data Sources. It might sound like a maze at first, but fear not 🙂. Embracing REST APIs will help us achieve two pivotal goals: 1) ensuring interoperability with external data sources, like freecurrencyapi; and 2) crafting a great user experience (UX) that allows users to exchange and manipulate data seamlessly, without direct access to our database.
The Path to Enabling REST Services in Oracle APEX
The process is surprisingly straightforward. Simply navigate to SQL Workshop > RESTful Services and REST-enable the schema. Choose an alias – I'll stick with ‘kalasarpa’ (if anybody is wondering the term 'Kal' means time and 'Sarpa' means serpent, so time serpent it is / I haven't had any other better ideas :)). This alias becomes part of the URL used to access all our REST-enabled datapoints.
REST-Enabling the Database Objects: A Theoretical Dive
Before we get our hands dirty, let's understand the theory behind REST-enabling database objects. Oracle REST Data Services (ORDS) component stands at the forefront of bridging the Oracle database with the realm of REST web services, offering a modern and efficient method to access data. It ensures that data exchange between the database and any external sources adheres to REST principles, allowing web clients to interact with database data via HTTP methods. ORDS translates these into native SQL statements in the database and is seamlessly integrated into Oracle APEX, facilitating rapid app development and easy integration of data sources.
In ORDS, we can define a REST web service using three basic components: 1) module; 2) template; and 3) handler. The synergy between these components is illustrated in the image below.
In a nutshell, modules group similar REST sources and can encompass numerous templates. Templates specify the data source, like a table or a view, and can be linked to up to four handlers that define the database action performed upon accessing these data sources. Handlers, in turn, specify the SQL statement and, for example, the server response.
REST-Enabling a Database Object
Let's REST-enable a database object ('client' table) to access our clients' data.
Create a Module: First, select Modules and click ‘Create module’. In the form that appears, enter the module name (let's call it "Business Backend"), its base path (‘backend’), and leave the pagination size at its default. Click the button at the top right to create your new module, and observe how Oracle APEX crafts the URL for our new data source.
Create a Template: Next, create a template by clicking the button below our module. Here, we'll only need to provide the URI template (‘clients’) and leave everything else as default. Click the 'Create Template' button at the top right to create it.
Create a Handler: Oracle APEX simplifies everything for us; we just need to select a method (GET), source type (we'll just use a collection query) and input the SQL that retrieves all data on our clients. You can leave the Pagination Size setting blank, we'll set it up later. Click the ‘Create handler’ button once you're ready.
Below is the diagram of the end result, an example of client's GET request to a web server.
Now, test it in your browser by copying the full URL from the handler level and pasting it into your browser window. Voilà, it works! Let me know if it does!
To add functionalities like creating a new client, modifying an existing one, deleting, or fetching a particular one, simply add more templates and handlers.
The end result, as shown in the table below, is a module (Business Backend) with two templates. One template (‘clients’) includes two handlers for fetching all client data and adding new clients. The second template (‘clients:id’) comes with three handlers for fetching, updating, or deleting a specific client.
We're making significant strides with our app! By enabling REST services in our APEX environment and REST-enabling them, we've laid a solid foundation for integrating external data sources and enhancing user experience. This journey not only demystifies the process of REST-enabling in Oracle APEX but also opens up a world of possibilities for developers looking to create more dynamic, interconnected applications. Stay tuned as we continue to explore the vast capabilities of Oracle APEX and REST services, paving the way for innovative app development.