REST APIs in Oracle APEX: Consuming and Publishing APIs Step-by-Step

Why REST APIs Matter in Oracle APEX

In today's interconnected enterprise landscape, applications rarely operate in isolation. Oracle APEX, Oracle's premier low-code development platform, provides robust capabilities for integrating with RESTful services, enabling seamless data exchange between APEX applications and external systems. Whether you're pulling data from a cloud CRM, pushing orders to an ERP, or exposing your own APEX data to mobile apps, REST APIs are the glue that holds modern architectures together.

At ROSTAN Technologies, we've helped numerous Indian and Middle Eastern enterprises leverage Oracle APEX for API integrations, from small departmental apps to mission-critical systems. In this step-by-step guide, we'll walk you through both consuming external REST APIs and publishing your own REST endpoints using Oracle APEX.

Part 1: Consuming REST APIs in Oracle APEX

Oracle APEX offers multiple ways to consume REST APIs. The most common approaches are using the APEX_WEB_SERVICE PL/SQL API or the declarative Web Source module. We'll cover both, starting with the programmatic approach.

Step 1: Configure a Web Credential

Before calling any external API, you need to store authentication details securely. APEX provides Web Credentials to manage this.

1. Navigate to App Builder > Workspace Utilities > Web Credentials.
2. Click Create.
3. Provide a name (e.g., MY_API_CRED), static ID, and choose the authentication type (Basic, OAuth2, etc.).
4. Enter the username/password or client ID/secret as required.
5. For OAuth2, specify the token URL and scopes.

Note: For APIs that don't require authentication, you can skip this step.

Step 2: Call the API Using APEX_WEB_SERVICE

The APEX_WEB_SERVICE package is your Swiss Army knife for REST calls. Here's a simple example to fetch data from a hypothetical customer API:

DECLARE
  l_response CLOB;
  l_url      VARCHAR2(4000) := 'https://api.example.com/customers';
BEGIN
  l_response := APEX_WEB_SERVICE.make_rest_request(
    p_url         => l_url,
    p_http_method => 'GET',
    p_credential_static_id => 'MY_API_CRED'
  );
  -- Process l_response (JSON)
  APEX_DEBUG.msg(l_response);
END;

For POST requests with a JSON payload, use:

l_response := APEX_WEB_SERVICE.make_rest_request(
  p_url         => l_url,
  p_http_method => 'POST',
  p_body        => '{"name":"John Doe","email":"john@example.com"}',
  p_credential_static_id => 'MY_API_CRED'
);

Step 3: Parse the JSON Response

Oracle Database 12c and later include native JSON functions. You can use JSON_TABLE to shred the response into rows and columns:

SELECT jt.customer_id, jt.name, jt.email
FROM JSON_TABLE(l_response, '$[*]'
  COLUMNS (
    customer_id NUMBER PATH '$.id',
    name        VARCHAR2(100) PATH '$.name',
    email       VARCHAR2(100) PATH '$.email'
  )
) jt;

Alternatively, use APEX_JSON package for more complex parsing.

Step 4: Declarative Approach with Web Source

For simpler integrations, APEX's Web Source module allows you to define a REST data source declaratively and use it like a table in reports and forms.

1. Go to Shared Components > Web Source Modules.
2. Click Create and choose REST Data Source.
3. Enter the endpoint URL, choose the HTTP method, and configure authentication (using the Web Credential from Step 1).
4. Define the response structure by providing a sample JSON response or using the wizard to detect columns.
5. Once created, you can build interactive reports or forms directly on this Web Source.

Step 5: Error Handling and Best Practices

• Always handle exceptions: Wrap API calls in BEGIN...EXCEPTION blocks and log errors using APEX_DEBUG or a custom error table.
• Use timeouts: Set p_request_timeout to avoid hanging processes.
• Secure credentials: Never hardcode credentials; use Web Credentials.
• Monitor performance: External calls can be slow; consider caching responses or using APEX's built-in caching mechanisms.

Part 2: Publishing REST APIs from Oracle APEX

Oracle APEX also allows you to expose your data as REST endpoints, enabling other applications to consume your APEX data. This is done using ORDS (Oracle REST Data Services) and APEX's RESTful Services.

Step 1: Enable RESTful Services

Ensure ORDS is installed and configured with your APEX instance. Then, in APEX, go to SQL Workshop > RESTful Services.

Step 2: Create a RESTful Service Module

1. Click Create Module.
2. Provide a name (e.g., hr) and base path (e.g., /hr/).
3. Set the module to Published.

Step 3: Define a Resource Template

Resource templates define the URI pattern for your API. For example, to expose employees, create a template with URI employees/.

1. Under your module, click Create Template.
2. Enter URI Template: employees/.
3. Set the priority and other options.

Step 4: Add a Resource Handler

Handlers define the HTTP method and the logic to execute. For a GET request that returns all employees:

1. Click Create Handler.
2. Method: GET.
3. Source Type: PL/SQL.
4. Enter the PL/SQL block:

DECLARE
  l_cursor SYS_REFCURSOR;
BEGIN
  OPEN l_cursor FOR
    SELECT employee_id, first_name, last_name, email
    FROM employees;
  APEX_JSON.open_object;
  APEX_JSON.write('employees', l_cursor);
  APEX_JSON.close_object;
END;

Alternatively, use the simple :body bind variable to return JSON directly.

Step 5: Secure Your API

Security is paramount. APEX RESTful Services support several authentication mechanisms:

• OAuth2: Ideal for third-party access; configure in Shared Components > OAuth2 Clients.
• First Party Authentication: Use APEX session authentication.
• Basic Authentication: Simple but less secure; use with HTTPS.

You can also define privileges and roles to control access to specific modules.

Step 6: Test Your API

Once published, test your API using tools like Postman or curl. For example:

curl -X GET https://yourdomain.com/ords/yourworkspace/hr/employees/

Ensure the response is as expected and that authentication works.

Real-World Use Case: Integrating APEX with a Payment Gateway

Consider an Indian e-commerce company using APEX for order management. They need to integrate with a payment gateway like Razorpay. Using the consuming techniques above, they call Razorpay's API to initiate payments and handle callbacks. Simultaneously, they publish an API for their mobile app to fetch order status, using the publishing techniques. This bi-directional integration showcases APEX's versatility.

Best Practices for REST API Integration in APEX

• Version your APIs: Include version in the URL (e.g., /v1/) to manage changes.
• Use meaningful HTTP status codes: 200 for success, 400 for bad request, 401 for unauthorized, etc.
• Document your APIs: Use OpenAPI (Swagger) specifications for clarity.
• Implement rate limiting: Protect your APIs from abuse.
• Log requests and responses: For debugging and auditing.

Conclusion

Oracle APEX provides a comprehensive toolkit for REST API integration, whether you're consuming external services or publishing your own. By following the steps outlined in this guide, you can build robust, secure, and scalable integrations that connect your APEX applications with the broader enterprise ecosystem.

At ROSTAN Technologies, we specialize in Oracle APEX implementations and integrations. If you need assistance with your API strategy or APEX development, contact us today.

Written & reviewed by

Tarun Dutta

Senior Manager — APEX Solutions, ROSTAN Technologies

Tarun Dutta heads Oracle APEX solution delivery at ROSTAN Technologies, building low-code enterprise applications on Oracle APEX and the Oracle Database and modernising legacy Oracle Forms systems. More from Tarun →