Calling a RESTful service in ORACLE APEX

 

Introduction
Calling a RESTful service in Oracle APEX enables your application to interact with external systems, APIs, and web services seamlessly. Whether you want to fetch data from a third-party service or integrate with your own backend, Oracle APEX provides powerful tools to consume RESTful endpoints directly within your application. This blog will guide you through the process of calling RESTful services in APEX, including setup, usage, and best practices to ensure efficient and secure integrations.

How to Call a RESTful Service in Oracle APEX

1. Define a REST Data Source
   In Oracle APEX, start by creating a REST Data Source that points to the external RESTful service you want to consume. Use the RESTful Services interface or the Web Source Modules feature to define the endpoint URL, HTTP method (GET, POST, etc.), headers, and query parameters.

2. Configure Authentication
   Many REST APIs require authentication. Oracle APEX supports several authentication methods such as Basic Authentication, OAuth2, and API keys. Configure the appropriate authentication scheme for your REST Data Source to securely connect with the external service.

3. Test the REST Endpoint
   Use the built-in testing tools in APEX to verify that your REST Data Source returns the expected data. Check the response format, usually JSON or XML, and ensure that you handle any errors or unexpected responses gracefully.

4. Use REST Data in APEX Components
   Once the REST Data Source is defined, you can consume its data in various APEX components like Interactive Reports, Interactive Grids, Classic Reports, and Forms. Use SQL or RESTful Web Source references to bind REST data dynamically within your application.

5. Use PL/SQL for REST Calls (Optional)
   For advanced use cases, you can call RESTful services programmatically using APEX_WEB_SERVICE or UTL_HTTP packages within PL/SQL processes. This approach provides greater control over HTTP headers, request bodies, and response handling.

Best Practices

• Cache REST responses when appropriate to reduce latency and API call costs

• Handle exceptions and timeouts to avoid disrupting user experience

• Secure sensitive API credentials using APEX environment variables or encrypted storage

• Use pagination for large datasets to improve performance

• Log REST call activity and errors for monitoring and troubleshooting

• Keep your REST Data Sources up to date with any changes in the external API

Oracle APEX Documentation
For comprehensive details and examples on calling RESTful services in Oracle APEX, visit the official documentation here:
https://docs.oracle.com/en/database/oracle/application-express/

Calling a RESTful Service by ID number in Oracle APEX , you need to pass the ID as part of the URL or query parameters. This can be done in a variety of ways, depending on the structure of the RESTful service you're calling.

We have 3 options, with the last option being the simplest (It also has step by step descriptions)

Key Concepts:

1. RESTful Web Services: REST (Representational State Transfer) is an architectural style used for designing networked applications. RESTful web services allow you to perform operations like GET, POST, PUT, and DELETE over HTTP, typically using JSON or XML as the data format.

2. RESTful Web Service in APEX: Oracle APEX provides built-in functionality to consume and interact with external REST APIs directly through APEX components, such as Web Source or RESTful Services.

3. Data Synchronization: The goal of synchronization is to keep the data in APEX consistent with the data on an external system. This can involve:

• Pulling data from an external REST API to populate your APEX application (e.g., fetching remote data and displaying it in APEX forms or reports).

• Pushing data to an external system by sending data from APEX to the REST API (e.g., submitting a form and sending the data to a remote service).

• Updating or deleting data from APEX to keep both systems in sync.

Scenario: Calling a RESTful Service by ID

Let’s assume you have an existing RESTful Service for a resource, such as employees, and you want to retrieve details for a specific employee based on an ID number.

Steps to Call a RESTful Service by ID Number in Oracle APEX 23:

Step 1: Define the RESTful Web Service

Let’s say the resource employees is exposed as a RESTful Web Service and you have a query parameter for the id.

1. Define the RESTful Resource in APEX: 

• The Resource Path can be something like /employees/{employee_id}.

• The employee_id would be a parameter in the RESTful Web Service URL.

Example of defining the resource path:

/employees/{employee_id}

Where {employee_id} is a placeholder for the actual ID passed by the user or application.

Step 2: Prepare the Call to the RESTful Service in APEX

To call this service by employee_id, you can either pass the ID in the URL as a path parameter or use a query parameter.

 

Step 1: Define the RESTful Web Service in Oracle APEX

Suppose you have a RESTful service that exposes employee data and supports filtering by an employee_id. You want to define the resource path as /employees/{employee_id}, where {employee_id} is a placeholder for the actual employee ID.

How to define this in Oracle APEX:

1. Navigate to the RESTful Services Interface

• In Oracle APEX, go to SQL Workshop → RESTful Services.

• Click on Create to define a new RESTful Module or select an existing module to add a resource.

2. Create a New RESTful Module (if needed)

• Give it a meaningful Name and Base Path (for example, /api).

• The Base Path is the root URL under which your resources live.

3. Define the RESTful Resource

• Click Add RESTful Resource to define the resource.

• For Resource Template, enter:

 

Prepare the Call to the RESTful Service in APEX — Detailed Examples

In Oracle APEX, when you want to call a RESTful service by employee_id, you typically have two main ways to pass the ID:

1. As a Path Parameter — The ID is part of the URL path

2. As a Query Parameter — The ID is passed in the URL after a question mark (?)

Example 1: Passing employee_id as a Path Parameter

Suppose the REST endpoint URL looks like this:
https://api.example.com/employees/{employee_id}

You replace {employee_id} with the actual employee ID value.

Steps in Oracle APEX:

1. Create a REST Data Source or Web Source Module

   • Go to SQL Workshop → RESTful Services (or Shared Components → Web Source Modules)

   • Create a new REST Data Source

   • Enter the base URL: https://api.example.com/employees/

2. Define the Path Parameter

   • In the REST Data Source settings, define a path parameter, for example:
     Name: employee_id
     Type: Path Parameter

   • When calling the service, you will substitute this parameter with a page item value or bind variable.

3. Use Substitution to Pass the Parameter
   For example, in a report SQL query or process, use:

   SELECT * FROM APEX_WEB_SERVICE.make_rest_request(
     p_url => 'https://api.example.com/employees/' || :P1_EMPLOYEE_ID
   );
   

   or if using Web Source Modules, specify the path parameter as :P1_EMPLOYEE_ID

4. Example URL Called
   If P1_EMPLOYEE_ID = 101, the URL called will be:
   https://api.example.com/employees/101

Example 2: Passing employee_id as a Query Parameter

Suppose the REST endpoint URL looks like this:
https://api.example.com/employees?employee_id=101

Steps in Oracle APEX:

1. Create REST Data Source or Web Source Module

   • Base URL: https://api.example.com/employees

2. Add Query Parameter

   • Define a query parameter employee_id whose value will come from an APEX page item or bind variable, e.g., :P1_EMPLOYEE_ID

3. Use Substitution to Pass the Parameter
   For example, in a PL/SQL block or REST Data Source, build the URL with query parameter:

   DECLARE
     l_url VARCHAR2(4000);
   BEGIN
     l_url := 'https://api.example.com/employees?employee_id=' || :P1_EMPLOYEE_ID;
     -- Make the REST call using APEX_WEB_SERVICE or Web Source Module
   END;
   

4. Example URL Called
   If P1_EMPLOYEE_ID = 101, the URL called will be:
   https://api.example.com/employees?employee_id=101

Additional Tips:

• When creating Web Source Modules in APEX, you can define path and query parameters interactively. For path parameters, use curly braces {} syntax like /employees/{employee_id} and APEX will prompt you to map that to a page item or variable.

• For query parameters, use the “Add Query Parameter” button in the Web Source Module setup and set its value dynamically to an APEX page item or bind variable.

• Always validate and sanitize the input to prevent injection or malformed URLs.

• You can use Page Items (like :P1_EMPLOYEE_ID) to dynamically pass parameters from the user interface.

Summary Code Snippet Example — Calling with Path Parameter Using APEX_WEB_SERVICE

DECLARE
  l_response CLOB;
BEGIN
  l_response := APEX_WEB_SERVICE.make_rest_request(
    p_url => 'https://api.example.com/employees/' || :P1_EMPLOYEE_ID,
    p_http_method => 'GET'
  );
  -- Process l_response JSON as needed
END;

Summary Code Snippet Example — Calling with Query Parameter Using APEX_WEB_SERVICE

DECLARE
  l_response CLOB;
BEGIN
  l_response := APEX_WEB_SERVICE.make_rest_request(
    p_url => 'https://api.example.com/employees?employee_id=' || :P1_EMPLOYEE_ID,
    p_http_method => 'GET'
  );
  -- Process l_response JSON as needed
END;

These methods let you prepare and call RESTful services efficiently in Oracle APEX by dynamically passing employee_id either as a path or query parameter.

 

Conclusion
Integrating RESTful services within Oracle APEX empowers your applications to leverage external data and services, extending functionality and improving user experience. By following best practices in defining REST Data Sources, managing authentication, and handling responses, you can build reliable and maintainable integrations. Proper planning and testing will help ensure your RESTful service calls enhance your APEX applications effectively and securely.