Building and Consuming REST APIs in Oracle APEX

 

In today’s software world, APIs (Application Programming Interfaces) are the foundation of integration. Whether your Oracle APEX application needs to connect with mobile apps, third-party platforms, or internal systems, APIs make it possible.

With Oracle APEX, you can do more than build web applications. You can also:

• Expose your database as REST APIs – share data and business logic securely.
• Consume REST APIs from other systems – bring external information directly into your app.

This approach is called API-First Development. It means designing your APIs first and then building applications around them. By following this practice, you enable scalability, reusability, and smooth integration across platforms.

What You’ll Learn in This Blog

1. What API-First means in APEX.
2. How to create REST APIs in Oracle APEX.
3. How to expose your data with GET, POST, PUT, and DELETE endpoints.
4. How to consume external APIs in APEX.
5. Best practices for beginners

1. What Does API-First Mean?

Normally, developers start by building the screens (UI) and only later think about APIs.

In the API-First approach, you start by designing the API (the contract) before you even build the UI.

Think of your database operations as reusable services.

• Your APEX app can use these services.
• A mobile app can also use the same services.
• No duplicate logic—just one API serving many clients.

Example:

Instead of putting PL/SQL code directly in an APEX page process, you can expose it as an API like /employees. Then:

• Your APEX app can call this API.
• Other systems can also call it directly.

This makes your application cleaner and more flexible.

2. Setting Up REST APIs in Oracle APEX

Oracle APEX uses Oracle REST Data Services (ORDS) to expose database objects as REST APIs. ORDS acts as the bridge between the database and the outside world.

Steps to Create a REST API

1. Log in to SQL Workshop in APEX.
2. Go to RESTful Services.
3. Create a Module → this is like a folder that groups related APIs.
• Example: hr_api for HR-related endpoints.


4. Add Templates → these define the URL paths
• Example: /employees/.


5. Add Handlers → these define what happens when someone calls your API.
• GET → fetch data.
• POST → insert new data.
• PUT → update existing data.
• DELETE → remove data.

3. Examples of REST API Methods in APEX

Suppose you want APIs for the EMPLOYEES table.

GET (Fetch All Data)

Endpoint: /employees/

Copy Code

SELECT employee_id, first_name, last_name, salary 
FROM employees;

Returns a JSON list of all employees.

GET by ID (Fetch One Employee)

Endpoint: /employees/:id

Copy Code

SELECT employee_id, first_name, last_name, salary 
FROM employees
WHERE employee_id = :id;

Returns just one employee based on the ID passed in the URL.

POST (Insert New Employee)

Endpoint: /employees/

Copy Code

BEGIN
  INSERT INTO employees (employee_id, first_name, last_name, salary)
  VALUES (:employee_id, :first_name, :last_name, :salary);
END;

Send a JSON body like this:

Copy Code

{
  "employee_id": 101,
  "first_name": "John",
  "last_name": "Doe",
  "salary": 5000
}

This inserts a new record into the database.

PUT (Update Employee)

Endpoint: /employees/:id

Copy Code

BEGIN
  UPDATE employees
  SET salary = :salary
  WHERE employee_id = :id;
END;

Updates the employee’s salary.

DELETE (Remove Employee)

Endpoint: /employees/:id




Copy Code

BEGIN
  DELETE FROM employees
  WHERE employee_id = :id;
END;

Deletes the employee with the given ID.

1. GET Method (Fetch Data from APEX API)

Suppose you expose an Employee REST API in APEX at:
https://oracleapex.com/ords/hr/employees/

Copy Code

DECLARE
  l_response CLOB;
BEGIN
  l_response := apex_web_service.make_rest_request(
    p_url         => 'https://oracleapex.com/ords/hr/employees/',
    p_http_method => 'GET'
  );

  dbms_output.put_line('Response: ' || l_response);
END;
/

Use case: Fetch employee details from your APEX REST API.

2. POST Method (Insert New Data into APEX API)

Suppose you want to insert a new employee record:

API: https://oracleapex.com/ords/hr/employees/

Copy Code

DECLARE
  l_response CLOB;
BEGIN
  l_response := apex_web_service.make_rest_request(
    p_url         => 'https://oracleapex.com/ords/hr/employees/',
    p_http_method => 'POST',
    p_body        => '{"employee_id": 207, "first_name": "Vikas", "last_name": "Kumar", "email": "vikas.kumar@example.com"}',
    p_wallet_path => null,
    p_content_type => 'application/json'
  );

  dbms_output.put_line('Response: ' || l_response);
END;
/

Use case: Add new employee details to APEX REST table.

3. PUT Method (Update Existing Data)

Suppose you want to update employee 207’s email:

API: https://oracleapex.com/ords/hr/employees/207




Copy Code

DECLARE
  l_response CLOB;
BEGIN
  l_response := apex_web_service.make_rest_request(
    p_url         => 'https://oracleapex.com/ords/hr/employees/207',
    p_http_method => 'PUT',
    p_body        => '{"email": "vikas.new@example.com"}',
    p_wallet_path => null,
    p_content_type => 'application/json'
  );

  dbms_output.put_line('Response: ' || l_response);
END;
/

Use case: Update existing employee details.

4. DELETE Method (Remove a Record)

Suppose you want to delete employee 207:

API: https://oracleapex.com/ords/hr/employees/207

Copy Code

DECLARE
  l_response CLOB;
BEGIN
  l_response := apex_web_service.make_rest_request(
    p_url         => 'https://oracleapex.com/ords/hr/employees/207',
    p_http_method => 'DELETE'
  );

  dbms_output.put_line('Response: ' || l_response);
END;
/

Use case: Delete a record from your APEX REST API.

Key Notes:

1. API URL Format in APEX:
   • https://oracleapex.com/ords/schema/module/resource/
     Example: https://oracleapex.com/ords/hr/employees/
2. Methods:
   • GET → Fetch data
   • POST → Insert new record
   • PUT → Update existing record
   • DELETE → Remove record
3. Testing Tools:
   Before calling in PL/SQL, test API in Postman.
4. Authentication:
• Public APIs work without auth.
• Secure APIs need Basic Auth / OAuth2.

4. Consuming External APIs in Oracle APEX

You don’t just create APIs—you can also use APIs from other systems inside APEX.

Ways to consume external APIs:

• Web Source Modules → treat an API as a data source in APEX (like a table).
• PL/SQL APIs → APEX_WEB_SERVICE or APEX_EXEC.

Example: Call a Weather API

Copy Code

DECLARE
  l_response CLOB;
BEGIN
  l_response := apex_web_service.make_rest_request(
    p_url => 'https://api.weatherapi.com/v1/current.json?key=YOUR_KEY&q=Delhi',
    p_http_method => 'GET'
  );
  dbms_output.put_line(l_response);
END;

This fetches weather data in JSON format, which you can then show in a report, chart, or page item.

5. Best Practices for Beginners

Design First – Write down your endpoints before coding. Tools like Postman.
Organize APIs – Group related APIs in modules (HR, Finance, etc.).
Secure Everything – Use Authentication (OAuth2, Basic Auth, or APEX sessions). Never leave APIs open.
Keep It Reusable – Write APIs so they can be used by APEX, mobile apps, or other systems.
Test with Postman – Always test APIs separately before using them in your app.

Why Building and Consuming REST APIs in APEX Matterses

• Future-ready → Connect your APEX apps to mobile, cloud, or third-party platforms effortlessly.
• Faster integration → Share data securely without direct database access—just expose REST APIs.
• Cleaner architecture → Keep business logic inside APIs instead of scattered across page processes.

Conclusion

Oracle APEX with ORDS makes building and consuming REST APIs straightforward and powerful. You can:

• Expose your tables and PL/SQL as secure, reusable REST APIs.
• Consume APIs from external systems directly in your APEX apps.
• Create modern, scalable, and integrated applications—all within the APEX environment.

Whether you’re working with GET, POST, PUT, or DELETE, mastering REST APIs in APEX is a core skill for developers building enterprise-ready solutions today.