How do I Create a REST Service from a Table in Oracle APEX

 

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

1. 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.

2. 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.

3. 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.

4. 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.

5. 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.

6. 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.

7. 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

1. Open SQL Developer or SQLcl and connect to your Oracle Database.

2. 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;

3. This enables RESTful services for your schema, allowing you to expose database objects.

Step 2: Create a REST Module in APEX

1. Log in to Oracle APEX and navigate to SQL Workshop.

2. Click on RESTful Services.

3. Click Create and select Module.

4. Enter a module name, such as employees_service, and set the Base Path (e.g., /employees).

5. Click Next, then Create.

Step 3: Define a RESTful GET Handler for a Table

1. Inside the created module, click Create Template.

2. Set the URI Pattern as /:id?, allowing retrieval of all or a single record.

3. Click Next, then Create.

4. Click Create Handler and select Method: GET.

5. In the Source Type, select SQL Query and enter: 

SELECT * FROM employees WHERE employee_id = :id OR :id IS NULL;

6. Click Next, then Create.

7. 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.

8. 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

1. Inside the REST module, create another Template with the same URI Pattern (/).

2. Click Create Handler, select Method: POST, and set the Source Type to PL/SQL.

3. 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;

4. 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/

5. This inserts a new employee record into the employees table.

Step 5: Create a RESTful PUT Handler to Update Data

1. Create a new Template with URI Pattern /:id.

2. 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;

3. 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

4. This updates the employee record for ID 101.

Step 6: Create a RESTful DELETE Handler

1. Create a new Template with URI Pattern /:id.

2. Click Create Handler, select Method: DELETE, and enter: 

DELETE FROM employees WHERE employee_id = :id;

COMMIT;

3. Test by running: 

curl -X DELETE https://your-apex-url/ords/my_schema/employees/101

1. 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.