Configuring REST APIs in ORDS (A beginners guide)

Dan Batty
May 5
5 min read

DISCLAIMER - this blog is aimed at creating APIs through the RESTful services section of APEX which was deprecated in ORDS release 25.1. If you are working with ORDS after this patch, chances are this blog will be outdated.

Don’t worry, it’s not just you! Setting up APIs in ORDS can be infuriating. The overly-simplified GUI. The lack of good documentation. The condescending replies on stack overflow. It’s all too much.

That’s what this blog aims to solve. So if this sounds familiar, you’re in the right place.

The problem

On a recent project development, I found myself needing to setup an API call to request data stored on one of our internal applications. The problem was, for security purposes, I couldn’t use a simple ‘POST’ request to populate a table or view on the client side - which I could then query with standard SQL. Instead I needed to use a parameter which I would send to the data source in order to query the source table and send a response back. This would allow for a much more dynamic approach and ensure that the client application only received the data needed from the table.

If you’re an Oracle veteran, you may be thinking “That sounds simple enough!”, and you might be right. However, when looking into it there was next to nothing online to help guide a newcomer to setting up APIs within RESTful services. There were a couple of YouTube tutorials here and there, and other blogs documenting their findings with APIs, but nothing to help solve my particular issue. Even when looking at Oracle's own documentation, I found a lack of specificity due to the deprecation of RESTful services that often muddied the waters even further. Although the RESTful services section in SQL workshop has been deprecated, it’s still a quick and easy tool to spin up an API endpoint in legacy environments that cannot be upgraded.

So after days of research, trial and error, and banging my head against a wall, I finally created this fool-proof guide that anyone should be able to follow along with to configure your very own API.

The process

First I began by creating a RESTful data service inside the schema which I needed to query: navigate to the SQL workshop inside APEX and select ‘RESTful Services’. If you haven’t already, you might need to configure ORDS. To do this, simply click ‘Register Schema with ORDS’ and enter an alias.

Dashboard interface with icons for Object Browser, SQL Commands, SQL Scripts, Utilities, and RESTful Services. Dark background.

Configure the data service

Now we can begin to configure our data service - this will be the endpoint for our API request. Start by creating a module and a template inside of this. It’s worth noting that in some instances this step would fail for me when naming the ‘URI template’ something other than the table that is being queried, however it is not strictly necessary. Also, be sure to copy the endpoint URL that was created as we will need this later.

Software interface showing a "Module Definition" screen with fields like "Module Name" and "Base Path." A cursor selects "Create Module."

Next, create a resource handler inside the template. Here we will select a ‘GET’ method and enter an SQL query which will define our source. For best practices, try not to use a “SELECT * FROM” query and instead only define the specific columns we will require - this ensures we aren’t exposing any unnecessary data that may become a vulnerability.

Finally, we need to create the parameters inside our handler. First, we will create a parameter with access method ‘IN’, source type ‘URI’ and name it the column we want to query in the table (in this case ‘email’). Here, we can also use a bind variable which will help us to query the table by inserting our parameter into it.. After that, create an ‘OUT’ parameter with the name ‘response’ and source type ‘RESPONSE’: this is what our client side will receive once the table has been queried.

SQL query code on dark screen, selecting employee data with parameters below, highlighting SELECT and ESCAPE in red.

During an API request the parameter is sent via HTTP. This means that any illegal characters will be replaced so they can be added to the request URL. In this example, I am using an email address as my parameter input that will contain illegal characters reserved for computation. Therefore, to ensure my query is properly computed, I need to ensure these characters are restored once the request has been received: this is done with the use of the UTL_URL.ESCAPE() function seen on line 9. This takes a string input (url) and a boolean (escape_reserved_chars) to indicate whether reserved characters should be escaped. If your parameter doesn’t contain any illegal characters, you can skip this step.

It's worth noting that by default, this endpoint will be insecure and accessible by anyone over the internet - we will configure the security attributes of the API in a coming blog.

Create the client data source

Now we have configured the data service, we can switch over to our client application and set up a data source. Navigate to shared components and open ‘REST Data Sources’. Create a new one from scratch, give it a name and enter the URL endpoint you copied earlier. Make sure the ‘Service URL Path’ is auto-populated to ensure the URL has been recognised. For now, turn off authentication and create a REST source (we will configure this in a separate blog post).

Once created, make note of the static ID created for the REST source as we will need to reference this inside our procedure later on.

Creating a REST data source screen with details like data type, name, and URL endpoint. Dark interface with active Next button.

Next, navigate to the ‘GET’ operation within the REST data source. Here we will recreate the parameters we made on the service side. Make sure you use the same name set up on the service side and ensure the type and direction are configured as shown below.

Dark interface of a REST Source Operation setup. Fields include URL, HTTP method (GET), and parameters like EMAIL. Apply Changes button present.

Test the API

Our data source is now ready to use. To test this, I will create a simple procedure which we will call with a button in order to call the data source dynamically. Here we are using the apex_exec function in order to call the rest source dynamically. We first use the ‘add_parameter’ function to use our page item as the parameter value. For the purpose of this example, I’ve used a simple page item as our input for the API parameter (seen on line 8). Next, the ‘execute_rest_source’ function references the data source using the static ID we made note of earlier and passes in the parameter we just established.

Code editor displaying PL/SQL code for executing a REST API. Syntax highlighting shows varying colors for commands and parameters.

The response is given as a JSON string. Therefore, we need to store this in a clob and then parse out the response using the json_table function which we can then use to set our page items displaying the query response.

After creating the procedure, we can demonstrate that the API is working correctly by inputting our email parameter and running the procedure.

App interface showing "Test_App" with an email input field and "Call API" button. Displays "API Response" showing ID 10003 and Admin N.

Final thoughts

After following this guide you should now be able to:

Configure an API endpoint in ORDS
Set up a REST data source in APEX
Create a simple procedure to call your API

If you found this useful, make sure to follow us on LinkedIn and stay posted for my next blog where we will enhance this and design a secure end-to-end API using client credentials in APEX.