Creating a Simple REST Endpoint With ORDS and Consuming It in APEX

Why REST in an APEX World

Oracle REST Data Services (ORDS) lets you expose database objects as RESTful web services with minimal configuration. Even if your primary application is built in APEX, creating REST endpoints opens your data to mobile apps, external integrations, and other systems. And APEX itself can consume REST endpoints, which is useful for pulling data from external services or for decoupling parts of a large application.

Creating a REST Endpoint in SQL Workshop

The fastest way to create a REST endpoint is through APEX’s SQL Workshop. Navigate to RESTful Services and create a new module. A module is a logical grouping of related endpoints:

Module Base Path: /api/. Template: employees/:id. Method: GET. Source Type: Collection Query.

-- Source for GET /api/employees/
SELECT employee_id,
       first_name,
       last_name,
       email,
       hire_date,
       department_id
  FROM employees
 ORDER BY last_name, first_name;

-- Source for GET /api/employees/:id
SELECT employee_id,
       first_name,
       last_name,
       email,
       phone_number,
       hire_date,
       salary,
       department_id
  FROM employees
 WHERE employee_id = :id;

Once you publish the module, ORDS immediately makes these endpoints available. The collection endpoint returns JSON with pagination support, and the single resource endpoint returns one employee record.

Consuming the REST Endpoint in APEX

To consume a REST endpoint in an APEX page, create a REST Data Source under Shared Components. Provide the endpoint URL, configure any parameters, and APEX will discover the response structure. You can then use this REST Data Source as the source for a report, chart, or Interactive Grid just like a local table.

For programmatic consumption in PL/SQL, use APEX_WEB_SERVICE:

DECLARE
    l_response CLOB;
BEGIN
    l_response := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
        p_url         => 'https://myserver/ords/myschema/api/employees/101',
        p_http_method => 'GET'
    );

    -- Parse the JSON response
    APEX_JSON.PARSE(l_response);
    DBMS_OUTPUT.PUT_LINE('Name: ' ||
        APEX_JSON.GET_VARCHAR2(p_path => 'first_name') || ' ' ||
        APEX_JSON.GET_VARCHAR2(p_path => 'last_name'));
END;
/

Security Considerations

By default, ORDS endpoints are unprotected. For production use, enable OAuth2 client credentials flow or first party authentication. ORDS supports multiple authentication schemes that you configure at the module or template level. At minimum, restrict write operations (POST, PUT, DELETE) to authenticated clients, and consider rate limiting to prevent abuse.