Performing REST API Calls in Oracle APEX with PL/SQL Code

In Oracle APEX, REST API calls can be performed using either Shared Components (Web Source Modules) or through PL/SQL processes. While Shared Components provide simplicity and built-in tools, sometimes you need greater control over the request, response, and processing logic.

In this article, I’ll show you how to perform REST API calls using pure PL/SQL code in a process. We’ll use a REST API call to send a JSON payload, parse the response, and store the data — all while maintaining full control. To keep things practical, I’ll use an example of calling the OpenAI API, but this method applies to any REST API, such as those for external integrations, internal microservices, or custom applications.

1. Why use PL/SQL for REST API Calls?

While Oracle APEX provides easy ways to integrate REST APIs through Shared Components, there are cases where more control is needed:

1. Dynamic Requests: Customizing the request body, headers, or query parameters based on page conditions.
2. Complex Responses: Parsing nested JSON data or handling unexpected API responses.
3. Custom Error Handling: Adding PL/SQL exception logic for retries, error logging, or fallback mechanisms.
4. Performance: Reducing dependency on pre-defined Shared Components for lighter, faster API calls.

In short, using PL/SQL gives you the flexibility to manage every step of the REST call: building the payload, sending the request, parsing the response, and acting on it.

2. Example Use Case: Calling a REST API

Let’s take a practical example of calling a REST API. Here, we call the OpenAI API to send a POST request and process its response. This example serves as a template and can be applied to any REST API.

2.1. The PL/SQL code

Here’s the full PL/SQL code for the REST API call:

DECLARE
    l_url         VARCHAR2(4000) := 'https://api.openai.com/v1/chat/completions';
    l_response    CLOB;
    l_body        VARCHAR2(4000);
    l_message     VARCHAR2(4000);
BEGIN
    -- 1. Set HTTP Headers
    apex_web_service.g_request_headers(1).name  := 'Content-Type';
    apex_web_service.g_request_headers(1).value := 'application/json';

    -- 2. Build the JSON Payload
    l_body := '{
        "model": "gpt-3.5-turbo",
        "messages": [
            {"role": "system", "content": "You are a helpful assistant."},
            {"role": "user", "content": "Hello, API!"}
        ]
    }';

    -- 3. Make the REST API Call
    l_response := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
        p_url                 => l_url,
        p_http_method         => 'POST',
        p_body                => l_body,
        p_credential_static_id => 'OpenAI_Credentials'  -- Credential ID
    );

    -- 4. Parse the JSON Response
    apex_json.parse(l_response);
    l_message := JSON_VALUE(l_response, '$.choices[0].message.content');

    -- 5. Log or Store the Response
    :P6_RESPONSE := l_message;

EXCEPTION
    WHEN OTHERS THEN
        :P6_RESPONSE := 'Error: ' || SQLERRM;
END;

The Step-by-Step Breakdown

1. Set HTTP Headers

We define the HTTP headers required by the API. Here, Content-Type is set to application/json.

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

2. Build the JSON Payload

The JSON body is constructed dynamically as a string. This can be modified based on page items or application logic.

l_body := '{
    "model": "gpt-3.5-turbo",
    "messages": [
        {"role": "system", "content": "You are a helpful assistant."},
        {"role": "user", "content": "Hello, API!"}
    ]
}';

You can replace the messages content or any JSON payload based on your integration requirements.

3. Make the REST API call

We use the APEX_WEB_SERVICE.MAKE_REST_REQUEST procedure to send the POST request:

• p_url: The API endpoint URL.
• p_http_method: HTTP method (POST, GET, PUT, etc.).
• p_body: JSON payload.
• p_credential_static_id: Refers to the stored credentials for secure API authentication.

l_response := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
    p_url                 => l_url,
    p_http_method         => 'POST',
    p_body                => l_body,
    p_credential_static_id => 'OpenAI_Credentials'
);

4. Parse the Response

apex_json.parse(l_response);
l_message := JSON_VALUE(l_response, '$.choices[0].message.content');

JSON_VALUE extracts a specific path in the JSON response. Replace the path based on your API’s response structure.

5. Handle the Response

You can store the response in an APEX page item, table, or collection. In this case, we assign it to a page item for display.

:P6_RESPONSE := l_message;

6. Error Handling

We wrap the entire process in an exception block to handle errors gracefully.

EXCEPTION
    WHEN OTHERS THEN
        :P6_RESPONSE := 'Error: ' || SQLERRM;

3. Benefits of using this approach

• Custom Payloads: Build JSON dynamically based on your APEX page conditions.
• Advanced Control: Parse and extract only the needed data from complex JSON responses.
• Error Handling: Handle API failures and provide fallback mechanisms.
• API Security: Integrate securely with stored credentials using APEX Static ID Credentials.

4. How to test this

• Add page items like P6_RESPONSE to display the response.
• Add this PL/SQL code as a Page Process triggered on a button click.
• Replace the example API endpoint and payload with your own REST API details.

Conclusion

Performing REST API calls in Oracle APEX using PL/SQL processes gives you full control over every step: request building, response handling, and error management. While this article used OpenAI’s API as an example, the approach works seamlessly for any external or internal API integration.

By using PL/SQL, you gain the flexibility to integrate complex APIs into your APEX applications efficiently and securely.

Liked this article? Great! There’s more where this came from — follow me on Medium for more APEX tips and tricks, and connect with me on LinkedIn so we can geek out over APIs, PL/SQL, and all things APEX 😉