REST API Integration in Oracle APEX

🇪🇸 Leer en Español

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.

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.

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.

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):

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.

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.

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 docs.oracle.com/en/database/oracle/apex/24.2/htmdb/rest-data-sources
2. APEX_WEB_SERVICE API Reference 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

---

🚀 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|💼 Connect on LinkedIn

💖 Support My Work

If you found this article helpful, consider supporting me!

GitHub Sponsors | Buy Me a Coffee

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

---