# REST API Integration in Oracle APEX

[🇪🇸 Leer en Español](https://insightsapex.vinnyum.tech/integracion-rest-api-oracle-apex)

## 1\. Introduction: The API-First World

Modern applications rarely live in isolation. They exchange data with CRMs, ERPs, payment gateways, and internal microservices. For Oracle APEX developers, mastering **REST API integration** is no longer optional—it is a core professional skill.

In this edition of *APEX Insights*, we move beyond "Hello World". We will explore how to **architect maintainable integrations** that separate logic from configuration, a core principle we discussed in [Mastering Modularity](https://insightsapex.vinnyum.tech/mastering-modularity).

**What You Will Learn:**

*   **Declarative vs. Code:** When to use REST Data Sources vs. PL/SQL.
    
*   **Sync:** How to cache external data automatically for performance.
    
*   **The Modern Pattern:** Using `APEX_EXEC` to decouple URLs from code.
    
*   **Sending Data:** generating complex JSON payloads for POST requests.
    

* * *

### ⚠️ Prerequisite: Network Access Control Lists (ACLs)

Before your database can talk to the outside world, you must grant it permission. If you are on an On-Premises database (or OCI DB System), you will likely fail with `ORA-24247: network access denied` unless you configure an ACL.

```sql
BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host       => 'api.stripe.com',
    lower_port => 443,
    upper_port => 443,
    ace        => xs$ace_type(privilege_list => xs$name_list('connect'),
                              principal_name => 'APEX_230200', -- Your Schema Owner
                              principal_type => xs_acl.ptype_db));
END;
/
```

*Note: Autonomous Database handles this automatically for most public HTTPS endpoints.*

* * *

## 2\. The Integration Landscape in Oracle APEX

Oracle APEX provides two primary approaches for working with REST APIs. Choosing the right one is the first architectural decision you make.

### 2.1 REST Data Sources (Declarative)

REST Data Sources (Virtual Tables) treat APIs like standard database tables.

**Ideal for:**

*   Reporting (Interactive Reports/Grids).
    
*   Dashboards and Charts.
    
*   Read-heavy integrations.
    

### 2.2 Programmatic Integration (`APEX_EXEC` & `APEX_WEB_SERVICE`)

For transactional flows (e.g., "Create Order", "Process Payment"), you need PL/SQL control.

> **Professional Rule:** Start declarative. If custom logic is required, use PL/SQL but **keep configuration declarative** (see Section 4).

* * *

## 3\. Declarative Power: REST Data Sources & Synchronization

In **Shared Components → REST Data Sources**, you define the Endpoint, Authentication (via Web Credentials), and Data Profile.

### 3.1 The "Killer Feature": Synchronization

Querying a slow external API every time a user loads a page is bad practice. **Synchronization** solves this natively.

```mermaid
graph LR
    API[External API] -- Slow (2s) --> Sync[Local Table<br/>SYNC_DATA]
    Sync -- Fast (Milliseconds) --> APEX[APEX Reports]
    Scheduler[Automated Sync] -.-> API
```

1.  In your REST Data Source, go to **Manage Synchronization**.
    
2.  **Target Table:** APEX creates a local table (e.g., `SYNC_STRIPE_CHARGES`) to store the data.
    
3.  **Schedule:** Set it to run every hour, day, or on-demand.
    

Now, your APEX components query the **Local Table** (milliseconds) instead of the **External API** (seconds). The data is automatically kept fresh in the background.

* * *

## 4\. The Modern Pattern: `APEX_EXEC` over `APEX_WEB_SERVICE`

A common mistake is hardcoding URLs in PL/SQL using `APEX_WEB_SERVICE`.

**The Anti-Pattern (Hardcoded):**

```plsql
l_clob := apex_web_service.make_rest_request(
    p_url => 'https://api.stripe.com/v1/charges', ... -- ❌ Hardcoded URL
);
```

**The Professional Pattern (**`APEX_EXEC`**):** Define the module in "REST Data Sources" (declarative configuration) and call it from PL/SQL.

```plsql
DECLARE
    l_params apex_exec.t_parameters;
BEGIN
    apex_exec.add_parameter(l_params, 'customer_id', 101);

    apex_exec.execute_rest_source(
        p_static_id => 'STRIPE_CHARGES', -- ✅ Refers to Shared Component
        p_operation => 'POST',
        p_parameters => l_params
    );
END;
```

**Why?** If the API URL changes, you update *Shared Components*, not your package code. This separates "Configuration" from "Logic".

* * *

## 5\. Sending Data: Handling JSON and POST Requests

Integration isn't just about reading; it's about pushing data.

### 5.1 Generating the Payload & Robust Error Handling

Do not concatenate strings to build JSON. Use `APEX_JSON`. And critically, **parse error responses**.

```plsql
DECLARE
    l_response CLOB;
    l_error_msg VARCHAR2(4000);
BEGIN
    -- 1. Build JSON Payload
    apex_json.initialize_clob_output;
    apex_json.open_object;
        apex_json.write('customer', 'Acme Corp');
        apex_json.write('amount', 1250.50);
        apex_json.open_array('items');
            apex_json.open_object;
            apex_json.write('sku', 'PRO-100');
            apex_json.close_object;
        apex_json.close_array;
    apex_json.close_object;
    
    -- 2. Send Request
    apex_web_service.g_request_headers(1).name  := 'Content-Type';
    apex_web_service.g_request_headers(1).value := 'application/json';

    l_response := apex_web_service.make_rest_request(
        p_url                  => 'https://api.example.com/v1/orders',
        p_http_method          => 'POST',
        p_body                 => apex_json.get_clob_output,
        p_credential_static_id => 'MY_API_KEY'
    );
    
    apex_json.free_output;

    -- 3. Validate Success & Parse Error Details
    IF apex_web_service.g_status_code NOT IN (200, 201) THEN
        -- Try to extract human-readable error from API JSON response
        BEGIN
            apex_json.parse(l_response);
            -- Format varies by API
            l_error_msg := apex_json.get_varchar2('error.message');
        EXCEPTION WHEN OTHERS THEN
            l_error_msg := 'Unknown API Error';
        END;
        
        raise_application_error(-20001, 
            'Integration Failed (' || apex_web_service.g_status_code || '): ' ||
            l_error_msg
        );
    END IF;
END;
```

* * *

## 6\. Pro Tip: Debugging Integrations

When an integration fails, "Guessing" is not a strategy. Oracle APEX logs every outgoing request when Debug is enabled.

1.  Enable **Debug Level 9 (Trace)** for your page or session.
    
2.  Run the request.
    
3.  View the Debug Log (`View Debug`).
    
4.  Filter for `APEX_WEB_SERVICE`.
    

You will see the **exact** URL, Headers, and Request Body sent, as well as the raw Response. This is indispensable for troubleshooting Auth errors (401) or Bad Requests (400).

* * *

## 7\. Security: The "No Hardcoding" Rule

**Never** store API Keys in Package Constants.

1.  **Web Credentials:** Store secrets in *Workspace Utilities → Web Credentials*.
    
2.  **Environment Awareness:** APEX handles Dev/Prod URLs and Keys automatically via substitution strings in Shared Components.
    

* * *

## Conclusion

REST Integration in APEX is powerful when you follow the architecture: **Declarative configuration first, PL/SQL logic second.**

**Checklist:**

*   \[ \] Use **Synchronization** for read-heavy external data.
    
*   \[ \] Use **APEX\_EXEC** to decouple PL/SQL from URLs.
    
*   \[ \] Use **Web Credentials** for all security.
    
*   \[ \] Handle **4xx/5xx** errors by parsing the response payload.
    

* * *

## 📘 References

1.  **Oracle APEX Documentation: REST Data Sources**[https://docs.oracle.com/en/database/oracle/apex/24.2/htmdb/managing-REST-data-sources.html](https://docs.oracle.com/en/database/oracle/apex/24.2/htmdb/managing-REST-data-sources.html)
    
2.  **APEX\_WEB\_SERVICE API Reference**[docs.oracle.com/en/database/oracle/apex/24.2/aeapi/APEX\_WEB\_SERVICE.html](https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/APEX_WEB_SERVICE.html)
    
3.  **Parsing JSON in APEX**[docs.oracle.com/en/database/oracle/apex/24.2/aeapi/APEX\_JSON.html](https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/APEX_JSON.html)
    

* * *

## 🚀 Need an APEX Expert?

I help companies facilitate professional Oracle APEX development and DevOps. If you want to build better applications or automate your pipeline, **let's talk**.

[☕ Schedule a Call](https://calendly.com/vinnyum/intro-call)|[💼 Connect on LinkedIn](https://www.linkedin.com/in/vinny-jimenez/)

## 💖 Support My Work

If you found this article helpful, consider supporting me!

[**GitHub Sponsors**](https://github.com/sponsors/aguilavajz) | [**Buy Me a Coffee**](https://www.buymeacoffee.com/vinnyum)

Your support helps me keep creating open-source demos and content for the Oracle APEX community. 🚀

* * *