Mastering REST API Integrations in Oracle APEX.

For a long time, Oracle databases were treated as impenetrable fortresses. They were excellent at storing and processing data internally, but getting them to talk to the outside world—to trigger a Slack message, fetch live exchange rates, or validate a customer's shipping address via a third-party service—was a painful exercise in writing thousands of lines of low-level UTL_HTTP code.

If you are architecting modern applications for your users at siloed databases are no longer an option. Your Oracle APEX application must exist within a larger, interconnected ecosystem.

Fortunately, APEX provides a massive superpower right out of the box: APEX_WEB_SERVICE and APEX_JSON.

In this advanced guide, we are going to abandon legacy methods and learn how to seamlessly consume external RESTful APIs, handle complex JSON payloads, and manage HTTP authentication, all entirely natively within PL/SQL.

The Prerequisites: ACLs and Wallets

Before you write a single line of code, you must understand database security. By default, Oracle prevents the database from making outbound network calls.

1. Access Control Lists (ACL): Your DBA must grant your database schema permission to connect to external hosts using the DBMS_NETWORK_ACL_ADMIN package.

2. Oracle Wallet: If the API you are calling uses HTTPS (and in today's world, 100% of them do), the root SSL certificate of the target server must be loaded into the Oracle Database Wallet, and APEX must be configured to use that wallet.

Assume for this tutorial that your DBA has configured the ACLs and Wallet for your workspace.

Use Case 1: The Simple GET Request (Fetching Live Data)

Scenario: We are building a financial dashboard. When a user opens a contract record, we need to fetch the live currency exchange rate from EUR to USD using a public REST API (e.g., an endpoint like api.exchangerate.host).

Step 1: Making the Call

We use apex_web_service.make_rest_request. This function handles the network handshake and returns the raw response as a CLOB.

DECLARE
    l_response_clob CLOB;
    l_http_status   NUMBER;
BEGIN
    -- 1. Execute the REST GET request
    l_response_clob := apex_web_service.make_rest_request(
        p_url         => 'https://api.exchangerate.host/latest?base=EUR&symbols=USD',
        p_http_method => 'GET'
    );

    -- 2. ALWAYS check the HTTP status code!
    l_http_status := apex_web_service.g_status_code;

    IF l_http_status = 200 THEN
        -- The request was successful
        dbms_output.put_line('Success! Response received.');
    ELSE
        -- The server returned an error (400, 401, 404, 500, etc.)
        raise_application_error(-20001, 'API Call Failed with HTTP Status: ' || l_http_status);
    END IF;
END;

Step 2: Parsing the JSON

The API returns a JSON payload that looks something like this:

{
  "success": true,
  "base": "EUR",
  "date": "2026-04-10",
  "rates": {
    "USD": 1.085
  }
}

We cannot easily query a raw CLOB. We must parse it into memory using APEX_JSON.

DECLARE
    l_response_clob CLOB;
    l_exchange_rate NUMBER;
BEGIN
    -- [Execute the GET request as shown above...]

    IF apex_web_service.g_status_code = 200 THEN
        
        -- 1. Parse the CLOB into the APEX_JSON memory tree
        apex_json.parse(p_values => l_response_clob);
        
        -- 2. Extract specific values using path navigation
        -- Notice how we navigate the nested JSON object using dot notation: 'rates.USD'
        l_exchange_rate := apex_json.get_number(p_path => 'rates.USD');
        
        -- Use the data in your APEX application
        :P10_CURRENT_RATE := l_exchange_rate;
        
    END IF;
END;

Use Case 2: The Complex POST Request (Sending Data with Authentication)

Scenario: We need to send an automated SMS alert to a customer using the Twilio API whenever a high-priority asset changes to "NEEDS REPAIR". This requires sending an HTTP POST request, passing a JSON payload in the body, and authenticating via a Bearer Token or Basic Auth.

Step 1: Setting HTTP Headers

External APIs require metadata. You must tell the API what type of data you are sending (e.g., application/json) and who you are (Authorization).

BEGIN
    -- Clear any existing headers from previous calls in the same session
    apex_web_service.g_request_headers.delete;

    -- Set the Content-Type
    apex_web_service.g_request_headers(1).name  := 'Content-Type';
    apex_web_service.g_request_headers(1).value := 'application/json';

    -- Set the Authorization Header (Bearer Token)
    apex_web_service.g_request_headers(2).name  := 'Authorization';
    apex_web_service.g_request_headers(2).value := 'Bearer sk_live_YOUR_SECURE_API_KEY_HERE';
END;

Step 2: Generating the JSON Payload

Never concatenate strings to build JSON (e.g., '{ "phone": "' || v_phone || '" }'). This leads to invalid JSON if a user inputs a quote character, and exposes you to injection attacks. Use APEX_JSON to safely build the payload.

DECLARE
    l_payload CLOB;
BEGIN
    -- Initialize an in-memory CLOB
    dbms_lob.createtemporary(l_payload, true);
    
    -- Open the JSON builder and point it to our CLOB
    apex_json.initialize_clob_output;
    
    apex_json.open_object;
    apex_json.write('to_number', '+1234567890');
    apex_json.write('message', 'Alert: Asset #A-992 requires immediate repair.');
    apex_json.write('priority', 'HIGH');
    apex_json.close_object;
    
    -- Extract the generated JSON into our variable
    l_payload := apex_json.get_clob_output;
    
    -- Clean up memory
    apex_json.free_output;
END;

Step 3: Executing the POST Request

Now we combine the headers, the generated payload, and the REST call.

DECLARE
    l_response_clob CLOB;
    l_payload       CLOB;
BEGIN
    -- 1. [Set Headers as shown above]
    -- 2. [Generate l_payload as shown above]
    
    -- 3. Make the POST request
    l_response_clob := apex_web_service.make_rest_request(
        p_url         => 'https://api.external-sms-gateway.com/v1/messages',
        p_http_method => 'POST',
        p_body        => l_payload -- Attach the JSON body here
    );

    -- 4. Check status and handle response
    IF apex_web_service.g_status_code IN (200, 201) THEN
        -- Parse the response to get the newly created Message ID
        apex_json.parse(l_response_clob);
        
        UPDATE roya_asset_assigned_history
           SET sms_notification_id = apex_json.get_varchar2('message_id'),
               sms_sent_date = SYSDATE
         WHERE asset_id = :P10_ASSET_ID;
         
    ELSE
        -- Log the error securely for debugging
        INSERT INTO api_error_logs (error_date, http_status, response_body)
        VALUES (SYSDATE, apex_web_service.g_status_code, l_response_clob);
    END IF;
END;

Crucial Architectural Best Practices

To make this production-ready, you must adhere to the following rules:

1. Never Hardcode API Keys: Notice how I hardcoded the Bearer token in the example above? Never do that in production. Store your API keys in a secure, encrypted table, or better yet, use APEX Web Credentials. APEX Web Credentials securely store client IDs and secrets and automatically inject them into your make_rest_request calls using the p_credential_static_id parameter.

2. Beware of Blocking the UI: If you put a slow REST API call inside an APEX Page Processing step, the user's browser will freeze with a spinning wheel until the external server responds. If the external server times out (which can take 60 seconds), your user will think the application crashed.

   • Solution: For non-critical actions (like sending an email or SMS), use DBMS_SCHEDULER or APEX Automations to push the API call into a background job, allowing the UI to return a "Success" message to the user instantly.

3. Always Expect the API to Fail: Third-party APIs go down. Rate limits get exceeded. Your code must gracefully handle HTTP 429 (Too Many Requests) or HTTP 500 (Internal Server Error) without throwing ugly unhandled Oracle exceptions to the end-user. Always parse the error payload and show a friendly APEX message.

Summary

By mastering APEX_WEB_SERVICE and APEX_JSON, you transform Oracle APEX from a standalone database tool into a highly connected middleware platform. You can orchestrate microservices, sync with cloud CRMs, and trigger serverless functions, all while maintaining the security, transactional integrity, and performance of PL/SQL.