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

 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:

1. Login to Oracle APEX
   Open your Oracle APEX workspace and navigate to the SQL Workshop. Select RESTful Services under the RESTful Services section.

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

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

4. Define a Template
   Templates define the URL pattern.

   • Click Create Template.

   • Enter a URI Template like / for all rows or /:id for a specific row.

   • Select the appropriate Method (GET, POST, PUT, DELETE) depending on what you want the endpoint to do.

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

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

7. 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 GET method, to verify the response.

8. Use Authentication (Optional)
   You can control access using:

   • OAuth2

   • Basic Authentication

   • Oracle APEX session authentication

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

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