USING External REST API in Oracle APEX

 In the previous article, I explained how to CREATE  a RESTful Web Services inside Oracle APEX.

https://lidagholizadeh.blogspot.com/2025/08/all-about-restful-webservice-in-oracle.html

In this article, we will focus on the opposite scenario:
How to consume external REST APIs from Oracle APEX and PL/SQL.

External API integration is one of the most common requirements in APEX applications.

In this article I will explain all supported methods for calling external REST APIs in Oracle APEX:

1. Calling REST APIs Using Web Source Modules
2. Calling REST APIs from PL/SQL Using APEX_WEB_SERVICE

3. Use Rest Data Sources in Our Application

But first of all let me answer this question

❓Where Can You Use Your Newly Created Web Source Module in Oracle APEX❓

The short answer: Almost everywhere in APEX💖 As soon as you see the Source → Type property on a component and one of these options appears, you can directly plug in your external REST API (Web Source):

Interactive Report	Interactive Grid	Classic Report	Cards
Faceted Search	Chart (all types)	JET Chart	Calendar
Form	Master-Detail	Maps	Tree
Smart Filters	Approval Component	Gantt Chart	Timeline

👉Now, let's get started and use an External REST API 

1. Calling REST APIs Using Web Source Modules

Steps to Create a Web Source Module

1. Navigate to Shared Components

2. Select Data Sources → Web Source Modules

3. Click Create

4. Choose From Scratch

5. Fill Items as I showed in pictures below

TIP: I used this free public API:

https://jsonplaceholder.typicode.com/users

https://jsonplaceholder.typicode.com/posts?userId=1

Now your Rest Dta Source is ready

2. Calling REST APIs from PL/SQL Using APEX_WEB_SERVICE

While Web Source Modules are the recommended declarative approach, you sometimes need to call an external REST API directly from PL/SQL code for example, inside a page process, dynamic action, package, or scheduled job.

Step 1 – Grant Network ACL  

Run this once as SYS or a privileged user:

This PL/SQL block grants your Oracle APEX schema permission to make outbound HTTP/HTTPS calls to the internet and to jsonplaceholder.typicode.com, your API path😊

host => 'jsonplaceholder.typicode.com' allow connection only to this site 'connect' and 'resolve' required privileges to open a TCP connection and resolve the domain name principal_name => 'YOUR_APEX_SCHEMA' the database user that runs APEX ( APEX_240100)

BEGIN
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
        host => 'jsonplaceholder.typicode.com',
        ace => xs$ace_type(
            privilege_list => xs$name_list('connect','resolve'),
            principal_name => 'YOUR_APEX_SCHEMA',  
            principal_type => xs_acl.ptype_db
        )
    );
END;
/

Step 2 – Simple reusable GET function

CREATE OR REPLACE FUNCTION get_json_from_url(p_url IN VARCHAR2) 
RETURN CLOB
IS
    l_clob CLOB;
BEGIN
    APEX_WEB_SERVICE.G_REQUEST_HEADERS.DELETE();
    APEX_WEB_SERVICE.G_REQUEST_HEADERS(1).NAME  := 'User-Agent';
    APEX_WEB_SERVICE.G_REQUEST_HEADERS(1).VALUE := 'My-APEX-App/1.0';
    l_clob := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
        p_url         => p_url,
        p_http_method => 'GET'
    );
    IF APEX_WEB_SERVICE.G_STATUS_CODE != 200 THEN
        RAISE_APPLICATION_ERROR(-20001,
            'API Error – HTTP ' || APEX_WEB_SERVICE.G_STATUS_CODE);
    END IF;
    RETURN l_clob;
END;
/

Step 3 – Quick test in SQL Workshop

SELECT get_json_from_url('https://jsonplaceholder.typicode.com/users') FROM DUAL;

3. Use Rest Data Sources in Our Application

Now that you've created your Web Source Module(s) (from Section 1) or are ready to call APIs via PL/SQL (Section 2), integrate them into a real APEX page. We'll build a simple two-page dashboard:

• Page 1: List of users  
• Page 2: Posts for a selected user  

Choose Method A for declarative integration using Web Source Modules Choose Method B for PL/SQL-driven calls use when you need custom logic, POSTs, or non-report components.

Method A: Web Source Modules

1. Open Page 1 in Page Designer.
2. Edit the Interactive Report region:
   • Source Type: Web Source.
   • Web Source: Select JSONPlaceholder Users (from Section 1).
3. Customize columns:
   • Show: id, name, username, email, address.city.
   • Make name a Link Column:
     • Target: Page 2.
     • Set Items: P2_USER_ID = #ID#.
4. Add a Refresh button (Dynamic Action: Click → Refresh Region).

Run Page 1: See 10 live users. Click one → Navigates to Page 2.

Method B: PL/SQL-Driven Integration with APEX_WEB_SERVICE

This path calls the API via your function from Section 2, parsing into SQL for reports. Use for processes, non-UI elements, or complex payloads.

CREATE OR REPLACE FUNCTION get_json_from_url(p_url IN VARCHAR2) RETURN CLOB
IS
    l_clob CLOB;
BEGIN
    APEX_WEB_SERVICE.G_REQUEST_HEADERS.DELETE();
    APEX_WEB_SERVICE.G_REQUEST_HEADERS(1).NAME := 'User-Agent';
    APEX_WEB_SERVICE.G_REQUEST_HEADERS(1).VALUE := 'My-APEX-App/1.0';
    l_clob := APEX_WEB_SERVICE.MAKE_REST_REQUEST(p_url => p_url, p_http_method => 'GET');
    IF APEX_WEB_SERVICE.G_STATUS_CODE != 200 THEN
        RAISE_APPLICATION_ERROR(-20001, 'API Error: HTTP ' || APEX_WEB_SERVICE.G_STATUS_CODE);
    END IF;
    RETURN l_clob;
END;
/

1. Open Page 1.
2. Edit Interactive Report region:
• Source Type: PL/SQL Function Body Returning SQL Query.
• Code:

    DECLARE

    l_json CLOB;

        DECLARE

    l_json CLOB;

    BEGIN

    l_json := get_json_from_url('https://jsonplaceholder.typicode.com/users');

    RETURN q'~

        SELECT 

               jt.id AS user_id,

               jt.name,

               jt.username,

               jt.email,

               jt.address.city AS city

        FROM JSON_TABLE(:l_json, '$[*]'

            COLUMNS (

                id NUMBER PATH '$.id',

                name VARCHAR2(100) PATH '$.name',

                username VARCHAR2(50) PATH '$.username',

                email VARCHAR2(100) PATH '$.email',

                city VARCHAR2(50) PATH '$.address.city'

            )

        ) jt

    ~';

END;

1. Link name column to Page 2 (set P2_USER_ID = #USER_ID#).
2. Items to Submit/Return: None (static URL).

THANK YOU FOR YOUR READING AND BEST REGARDS !