Introduction
Creating a REST service from a table in Oracle APEX enables you to expose your database data as RESTful web services, allowing external applications to interact with your data easily. This capability is essential for integrating Oracle APEX applications with other systems, supporting mobile apps, or enabling modern web development practices. By converting your database tables into REST services, you unlock seamless communication between your database and external clients over HTTP.
How to Create a REST Service from a Table in Oracle APEX
-
Enable RESTful Services in Oracle Database
Ensure that Oracle REST Data Services (ORDS) is installed and configured for your database. ORDS acts as the bridge that allows RESTful endpoints to access your database objects securely. -
Access Oracle APEX RESTful Services Module
In Oracle APEX, navigate to SQL Workshop and then to RESTful Services. Here, you can create and manage RESTful modules, templates, and handlers. -
Create a RESTful Module
Define a new module that represents a logical grouping for your REST services. Provide a name and optional comments for easy identification. -
Define a RESTful Template
Within the module, create a template that maps to the URL pattern for your service. For example, you can define/employees/to represent access to the Employees table. -
Add Handlers for CRUD Operations
Create handlers for HTTP methods to define how the service behaves:
-
GET: Retrieves rows from the table. Use SQL queries to fetch data.
-
POST: Inserts new rows. Use SQL INSERT statements with JSON input.
-
PUT/PATCH: Updates existing rows. Use SQL UPDATE with parameters.
-
DELETE: Removes rows based on provided criteria.
-
Test the REST Service
Use REST clients like Postman or built-in APEX testing tools to verify that your service works as expected, returning data and allowing modifications as configured. -
Secure Your REST Service
Apply authentication methods such as OAuth2 or Basic Authentication. Use roles and privileges to restrict access and protect sensitive data.
Best Practices
-
Validate input data to prevent SQL injection or malformed requests.
-
Use pagination for GET requests on large tables to improve performance.
-
Employ meaningful URL naming conventions for clarity and ease of use.
-
Document your REST API endpoints for future maintenance and developer onboarding.
-
Regularly review and update security policies to safeguard your data.
Oracle APEX Documentation
For detailed instructions and examples, consult the official Oracle APEX RESTful Services guide:
https://docs.oracle.com/en/database/oracle/application-express/
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
Creating REST services from database tables in Oracle APEX provides a powerful way to expose your data to external applications securely and efficiently. By following the outlined steps and best practices, developers can build flexible RESTful APIs that enhance integration capabilities and modernize their Oracle APEX solutions. This approach supports scalable, maintainable, and secure data access for diverse client applications.
