Introduction
Creating a REST service from a table in Oracle APEX allows developers to expose database data through web services, enabling seamless data exchange with external applications or systems. This approach is essential for building modern, service-oriented applications that rely on lightweight and secure communication. With Oracle APEX and Oracle REST Data Services (ORDS), you can quickly and securely publish your data using a no-code or low-code interface. Whether you are integrating with other systems, feeding a dashboard, or enabling mobile apps, creating a RESTful service is a vital step.
To create a REST service from a table in Oracle APEX, you use Oracle REST Data Services (ORDS), which provides a way to expose your database objects as RESTful web services. This allows you to access and manipulate table data over HTTP. Here is a detailed step-by-step process to create a REST-enabled table service:
-
Login to Oracle APEX
Open your Oracle APEX workspace and navigate to the SQL Workshop. Select RESTful Services under the RESTful Services section. -
Enable REST for the Schema (if not already enabled)
Before creating services, you need to ensure that REST is enabled for your schema.-
Go to SQL Workshop > RESTful Services > RESTful Services Dashboard.
-
Click on Enable RESTful Services for your schema if it shows as disabled.
-
Optionally, define a Schema Alias and a Module Prefix.
-
-
Create a New REST Module
-
Go to SQL Workshop > RESTful Services > Modules.
-
Click Create Module.
-
Enter a Name (e.g.,
employee_api). -
Provide a Base Path (e.g.,
/hr/employees/). -
Optionally, enter a description.
-
Click Next to continue.
-
-
Define a Template
Templates define the URL pattern.-
Click Create Template.
-
Enter a URI Template like
/for all rows or/:idfor a specific row. -
Select the appropriate Method (GET, POST, PUT, DELETE) depending on what you want the endpoint to do.
-
-
Create a Handler
For each method (GET, POST, etc.), define a SQL or PL/SQL block to execute.
For example, to fetch all rows:-
Choose Method:
GET -
Source Type:
Query -
Enter a SQL statement such as:
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID FROM EMPLOYEES -
Click Create Handler
For a specific row:
-
URI Template:
/:id -
Method:
GET -
Source Type:
Query -
SQL:
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEE_ID = :id
-
-
Add POST, PUT, DELETE Handlers (optional)
You can add other HTTP methods for inserting, updating, or deleting records:-
POST for inserts
-
PUT for updates
-
DELETE for deletions
Use PL/SQL blocks like:
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID) VALUES (:employee_id, :first_name, :last_name, :department_id); -
-
Test the REST Service
After defining your templates and handlers:-
Go to the Module Overview
-
Copy the full endpoint URL.
-
Use a tool like Postman, or simply paste the URL in your browser if it’s a
GETmethod, to verify the response.
-
-
Use Authentication (Optional)
You can control access using:-
OAuth2
-
Basic Authentication
-
Oracle APEX session authentication
-
-
Consume in APEX Application
Once your REST service is working, you can use it as a Web Source in APEX.-
Go to Shared Components > Web Source Modules
-
Create a new Web Source using the REST endpoint.
-
Use it in forms, reports, and interactive grids as a data source.
-
By following these steps, you enable a table in your Oracle database to be accessed through RESTful services, providing flexible and modern data integration for internal and external applications.
Oracle APEX provides built-in support for creating RESTful web services, allowing applications to expose database tables as RESTful endpoints. This is useful for integrating with external applications, enabling mobile development, and sharing data securely over HTTP.
Why Use REST Services in APEX?
Provides an easy way to expose database tables as RESTful APIs.
Allows seamless integration with external systems and applications.
Supports JSON and XML data formats for modern web applications.
Enables mobile and web applications to interact with database data remotely.
Steps to Create a REST Service from a Table in APEX
Step 1: Enable ORDS (Oracle REST Data Services) in APEX
Open SQL Developer or SQLcl and connect to your Oracle Database.
Run the following command to enable ORDS for your schema:
BEGIN
ORDS.ENABLE_SCHEMA(p_enabled => TRUE, p_schema => 'MY_SCHEMA', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'my_schema', p_auto_rest_auth => FALSE);
COMMIT;
END;
This enables RESTful services for your schema, allowing you to expose database objects.
Step 2: Create a REST Module in APEX
Log in to Oracle APEX and navigate to SQL Workshop.
Click on RESTful Services.
Click Create and select Module.
Enter a module name, such as employees_service, and set the Base Path (e.g., /employees).
Click Next, then Create.
Step 3: Define a RESTful GET Handler for a Table
Inside the created module, click Create Template.
Set the URI Pattern as /:id?, allowing retrieval of all or a single record.
Click Next, then Create.
Click Create Handler and select Method: GET.
In the Source Type, select SQL Query and enter:
SELECT * FROM employees WHERE employee_id = :id OR :id IS NULL;
Click Next, then Create.
Test the REST endpoint by opening a browser and entering:
https://your-apex-url/ords/my_schema/employees/
This should return a JSON response with all employees.
To fetch a specific employee, use:
https://your-apex-url/ords/my_schema/employees/100
This returns details for employee ID 100.
Step 4: Create a RESTful POST Handler to Insert Data
Inside the REST module, create another Template with the same URI Pattern (/).
Click Create Handler, select Method: POST, and set the Source Type to PL/SQL.
Enter the following PL/SQL block to handle inserts:
BEGIN
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id)
VALUES (:employee_id, :first_name, :last_name, :email, SYSDATE, :job_id);
COMMIT;
END;
Click Create, then test the API using a REST client like Postman or cURL:
curl -X POST -H "Content-Type: application/json" -d '{"employee_id":101, "first_name":"John", "last_name":"Doe", "email":"jdoe@example.com", "job_id":"IT_PROG"}' https://your-apex-url/ords/my_schema/employees/
This inserts a new employee record into the employees table.
Step 5: Create a RESTful PUT Handler to Update Data
Create a new Template with URI Pattern /:id.
Click Create Handler, select Method: PUT, and enter:
UPDATE employees
SET first_name = :first_name, last_name = :last_name, email = :email, job_id = :job_id
WHERE employee_id = :id;
COMMIT;
Test with:
curl -X PUT -H "Content-Type: application/json" -d '{"first_name":"Jane", "last_name":"Smith", "email":"jsmith@example.com", "job_id":"HR_REP"}' https://your-apex-url/ords/my_schema/employees/101
This updates the employee record for ID 101.
Step 6: Create a RESTful DELETE Handler
Create a new Template with URI Pattern /:id.
Click Create Handler, select Method: DELETE, and enter:
DELETE FROM employees WHERE employee_id = :id;
COMMIT;
Test by running:
curl -X DELETE https://your-apex-url/ords/my_schema/employees/101
This removes the employee with ID 101 from the table.
Conclusion
Publishing a REST service from a table in Oracle APEX is straightforward and powerful. With just a few configuration steps, you can expose your table's data to any authorized consumer, all while maintaining control over access and structure. Oracle APEX and ORDS provide a secure and scalable framework for RESTful development, making it easy to build API-driven applications that work seamlessly across platforms.
