BUILDING a RESTful Web Services in Oracle APEX

❓What is a Web Service?

A web service is a mechanism for sending and receiving data between two different systems over a network, commonly using HTTP.

There are two main kinds of web services:

• RESTful: A lightweight, fast architectural style using HTTP verbs such as GET, POST, PUT, DELETE, typically exchanging JSON or XML

• SOAP: An older and more formal standard-based web-service architecture, built on XML and often more complex in setup.

*Tip: In the context of Oracle APEX we typically work with RESTful web services.

❓API or REST Web Service?

In everyday conversation, people sometimes use the terms API and REST web service interchangeably, but technically, every REST Web Service is an API,but not every API is REST Web Service. For example, a SOAP API or gRPC service uses a different architecture and may not revolve around HTTP or resources.

they are not the same:

• API (Application Programming Interface): A broad concept referring to a set of rules, tools, and protocols that allow two software systems to communicate. An API can be implemented in many ways (SOAP, GraphQL, REST) and is not limited to web usage.

• REST Web Service: A specific kind of API that uses the REST architecture style. REST services are usually accessed via HTTP (GET, POST, PUT, DELETE) and exchange data in JSON or XML.

📙Building a Web Service in Oracle APEX

✅ Step 1: 

Go to your workspace → SQL Workshop→  RESTFUL Services

* TIP: If no REST services are enabled yet, click the button Register Schema with ORDS

✅ Step 2: Create a Module

 

    fill Module Definition, for example, like below

• Module Name: employees_api
• Base Path: /api/employees/
• Pagination Size: leave default or set 0 for no paging
• Is Published: On the API is accessible
• Origins Allowed: Controlling Which Websites Can Access Your REST API

This setting defines which websites are permitted to send requests to your REST API
If this field is left empty, no restrictions are applied, meaning any website can access your REST endpoints. For better security, it is recommended to explicitly specify which origins are allowed.

for example: https://myfrontend.com,http://localhost:8080

• Comments: optional description
  Click Create

✅ Step 3: Create a Template

After creating the module, you will see the Resource Template click Create Template.

Select the module you created → click Create Template.

• URI Template: list/ (depending on if you're returning one or many)
• Comments (optional)
  Click Create.
• Priority

Priority determines the order in which ORDS evaluates URI templates.
Lower values mean higher priority.
If multiple templates match an incoming request, the one with the lowest priority value is executed.

• HTTP Entity Tag Type

ORDS supports three ETag generation methods:

1. Secure Hash

Generates a version identifier using a secure hash of the response content.
This ensures that any modification in the resource results in a different ETag.
Recommended for production environments due to its accuracy and security.

2. Query

Uses a user-defined SQL query to determine the version identifier.
This option gives full control over what is considered a “change” in the resource.
For example:

SELECT MAX(last_update_date) FROM employees;

This ETag changes only when the underlying table data is modified.

3. None

No ETag is generated.
Caching is disabled, and the client always receives the full response.
Useful in scenarios where data changes frequently or caching is undesirable.

The final URL for the resource will be something like:

https://<server>/ords/<schema_alias>/api/employees/{id}

✅ Step 4: Create a Handler

Under the Template, click Create Handler. 

Configure:

• Method: GET/POST/PUT/DELETE:

GET – Retrieve Data

SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL

 FROM employees

        WHERE EMPNO= :id   if you want to use parameter in where like where EMPNO= :id change your template url as list/{id}

*TIP: Do not include a trailing semicolon in the SQL. This is a common cause of errors.

POST– Create a New Record

Used to insert new data into the database.

INSERT INTO employees (
    first_name,
    last_name,
    department_id,
    salary
) VALUES (
    :first_name,
    :last_name,
    :department_id,
    :salary
);

COMMIT;

PUT – Update an Existing Record

Updates one specific record.
Usually used with an ID in the URI Template.

URI Template

employees/{id}

UPDATE employees
SET salary = :salary,
    department_id = :department_id
WHERE employee_id = :id;
COMMIT;

DELETE – Remove a Record

Deletes a record by ID.
DELETE requests normally do not include a body.

Handler Source

DELETE FROM employees
WHERE employee_id = :id;
COMMIT;

• Source Type: Query (or PL/SQL, Media depending on use)
• Format: JSON
• Source: SQL statement:

now we can test our webservice in a browser