Introduction

Oracle REST Data Services (ORDS) allows you to expose database objects as RESTful web services. In this guide, we will enable RESTful services for the HR schema and create a fully functional REST API that supports GET, POST, PUT, PATCH, and DELETE operations for the Employees table.

This blog will take you through the end-to-end process, including:

• Enabling RESTful services for HR schema

• Creating a module, templates, and handlers

• Implementing all major HTTP methods

• Testing the APIs

---

1. Enabling RESTful Services for HR Schema

Before creating RESTful endpoints, we must enable ORDS for the HR schema.

Step 1: Enable ORDS for HR Schema

Execute the following PL/SQL block in SQLcl or SQL*Plus:

  BEGIN
        ORDS.ENABLE_SCHEMA(
        p_enabled => TRUE,
        p_schema => 'HR',
        p_url_mapping_type => 'BASE_PATH',
        p_url_mapping_pattern => 'hr_api', -- Base path for the schema
        p_auto_rest_auth => FALSE  -- Set to TRUE if authentication is required
    );
    COMMIT;
 END;
/

✅ Now, the HR schema is REST-enabled! All endpoints will be accessible under /ords/hr_api/.

---

2. Creating the RESTful API Module

A module acts as a logical container for multiple endpoints under a base path.

Step 2: Define a Module (hr_module)

 BEGIN
        ORDS.DEFINE_MODULE(
        p_module_name => 'hr_module',
        p_base_path => 'employees/',  -- Base path for all employee-related APIs
        p_items_per_page => 25
    );
    COMMIT;
 END;
/

✅ All employee-related endpoints will be accessible under /ords/hr_api/employees/.

---

3. Creating REST Endpoints for Employees Table

Step 3: Define Templates and Handlers

📌 GET - Fetch All Employees

    BEGIN
        ORDS.DEFINE_TEMPLATE(
        p_module_name => 'hr_module',
        p_pattern => '' -- Base pattern: `/ords/hr_api/employees/`
    );

    ORDS.DEFINE_HANDLER(
        p_module_name => 'hr_module',
        p_pattern => '',
        p_method => 'GET',
        p_source_type => ORDS.SOURCE_TYPE_QUERY,
        p_source => 'SELECT employee_id, first_name, last_name, job_id, salary FROM hr.employees'
    );

    COMMIT;
END;
/

✅ Access: GET /ords/hr_api/employees/
✅ Returns: List of all employees in JSON format.

---

📌 GET - Fetch a Single Employee by ID

BEGIN
    ORDS.DEFINE_TEMPLATE(
        p_module_name => 'hr_module',
        p_pattern => '{employee_id}' -- `/ords/hr_api/employees/:employee_id`
    );

    ORDS.DEFINE_HANDLER(
        p_module_name => 'hr_module',
        p_pattern => '{employee_id}',
        p_method => 'GET',
        p_source_type => ORDS.SOURCE_TYPE_QUERY,
        p_source => 'SELECT employee_id, first_name, last_name, job_id, salary 
                     FROM hr.employees 
                     WHERE employee_id = :employee_id'
    );

    COMMIT;
END;
/

✅ Access: GET /ords/hr_api/employees/101
✅ Returns: Employee details for employee_id = 101.

---

📌 POST - Insert a New Employee

BEGIN
    ORDS.DEFINE_TEMPLATE(
        p_module_name => 'hr_module',
        p_pattern => ''
    );

    ORDS.DEFINE_HANDLER(
        p_module_name => 'hr_module',
        p_pattern => '',
        p_method => 'POST',
        p_source_type => ORDS.SOURCE_TYPE_PLSQL,
        p_source => '
            INSERT INTO hr.employees (employee_id, first_name, last_name, job_id, salary) 
            VALUES (:employee_id, :first_name, :last_name, :job_id, :salary);
            COMMIT;'
    );

    COMMIT;
END;
/

✅ Access: POST /ords/hr_api/employees/
✅ Body (JSON Example):

jsonCopyEdit{
    "employee_id": 999,
    "first_name": "John",
    "last_name": "Doe",
    "job_id": "IT_PROG",
    "salary": 6000
}

✅ Effect: Inserts a new employee.

---

📌 PUT - Update an Entire Employee Record

BEGIN
    ORDS.DEFINE_TEMPLATE(
        p_module_name => 'hr_module',
        p_pattern => '{employee_id}'
    );

    ORDS.DEFINE_HANDLER(
        p_module_name => 'hr_module',
        p_pattern => '{employee_id}',
        p_method => 'PUT',
        p_source_type => ORDS.SOURCE_TYPE_PLSQL,
        p_source => '
            UPDATE hr.employees 
            SET first_name = :first_name, last_name = :last_name, 
                job_id = :job_id, salary = :salary 
            WHERE employee_id = :employee_id;
            COMMIT;'
    );

    COMMIT;
END;
/

✅ Access: PUT /ords/hr_api/employees/999
✅ Body (JSON Example):

jsonCopyEdit{
    "first_name": "Jane",
    "last_name": "Doe",
    "job_id": "SA_REP",
    "salary": 7000
}

✅ Effect: Updates all fields of the employee.

---

📌 PATCH - Partially Update an Employee

BEGIN
    ORDS.DEFINE_TEMPLATE(
        p_module_name => 'hr_module',
        p_pattern => '{employee_id}'
    );

    ORDS.DEFINE_HANDLER(
        p_module_name => 'hr_module',
        p_pattern => '{employee_id}',
        p_method => 'PATCH',
        p_source_type => ORDS.SOURCE_TYPE_PLSQL,
        p_source => '
            UPDATE hr.employees 
            SET salary = :salary 
            WHERE employee_id = :employee_id;
            COMMIT;'
    );

    COMMIT;
END;
/

✅ Access: PATCH /ords/hr_api/employees/999
✅ Body (JSON Example):

jsonCopyEdit{
    "salary": 8000
}

✅ Effect: Updates only the salary of the employee.

---

📌 DELETE - Remove an Employee

BEGIN
    ORDS.DEFINE_TEMPLATE(
        p_module_name => 'hr_module',
        p_pattern => '{employee_id}'
    );

    ORDS.DEFINE_HANDLER(
        p_module_name => 'hr_module',
        p_pattern => '{employee_id}',
        p_method => 'DELETE',
        p_source_type => ORDS.SOURCE_TYPE_PLSQL,
        p_source => '
            DELETE FROM hr.employees WHERE employee_id = :employee_id;
            COMMIT;'
    );

    COMMIT;
END;
/

✅ Access: DELETE /ords/hr_api/employees/999
✅ Effect: Removes employee with employee_id = 999.

---

Testing the REST API

Once everything is set up, test the APIs using:

• Postman

• cURL

• Web browser (for GET requests)

---

Conclusion

In this guide, we:
✔ Enabled RESTful services for HR schema
✔ Created an ORDS module (hr_module)
✔ Implemented GET, POST, PUT, PATCH, and DELETE requests
✔ Provided real examples with HR schema

Now, you have a fully functional REST API for managing employees! 🚀

Securing ORDS REST APIs with Basic Authentication in APEX

By default, ORDS allows anonymous access if p_auto_rest_auth is set to FALSE. However, for security, we should enable authentication using APEX roles and privileges.

---

1. Enable Authentication for HR Schema

Modify the ORDS.ENABLE_SCHEMA command to force authentication:

sqlCopyEditBEGIN
    ORDS.ENABLE_SCHEMA(
        p_enabled => TRUE,
        p_schema => 'HR',
        p_url_mapping_type => 'BASE_PATH',
        p_url_mapping_pattern => 'hr_api',
        p_auto_rest_auth => TRUE  -- Enforce authentication
    );
    COMMIT;
END;
/

✅ Now, authentication is required for all endpoints.

---

2. Create an APEX User for API Access

1. Log in to APEX Administration.

2. Navigate to Manage Users and Groups → Create User.

3. Provide a Username, Password, and assign RESTful Service role.

4. Save the user.

✅ The user can now authenticate with Basic Authentication (username & password).

---

3. Create an ORDS Role and Privilege

To enforce API access restrictions, define a role and privilege in ORDS.

Step 1: Create a Role (hr_api_role)

sqlCopyEditBEGIN
    ORDS.CREATE_ROLE(
        p_role_name => 'hr_api_role'
    );
    COMMIT;
END;
/

✅ hr_api_role is now available in ORDS.

---

Step 2: Assign the Role to APEX Users

sqlCopyEditBEGIN
    ORDS.GRANT_ROLE(
        p_role_name => 'hr_api_role',
        p_grantee => 'APEX_REST_PUBLIC_USER'
    );
    COMMIT;
END;
/

✅ Now, the APEX user must authenticate to access the API.

---

Step 3: Create a Privilege and Assign the Role

sqlCopyEditBEGIN
    ORDS.DEFINE_PRIVILEGE(
        p_privilege_name => 'hr_api_access',
        p_role => 'hr_api_role',
        p_description => 'Access to HR API endpoints'
    );
    COMMIT;
END;
/

✅ The privilege hr_api_access is linked to hr_api_role.

---

Step 4: Secure the API Endpoints

Modify the existing ORDS module to enforce authentication.

sqlCopyEditBEGIN
    ORDS.DEFINE_MODULE(
        p_module_name => 'hr_module',
        p_base_path => 'employees/',
        p_items_per_page => 25,
        p_status => 'PUBLISHED',
        p_comments => 'HR Employees API'
    );

    -- Secure the module with the privilege
    ORDS.DEFINE_MODULE_PRIVILEGE(
        p_module_name => 'hr_module',
        p_privilege_name => 'hr_api_access'
    );

    COMMIT;
END;
/

✅ Now, all requests to /ords/hr_api/employees/ require authentication.

---

4. Testing Secure API with Authentication

Now, if you try to access the API without authentication, you will get:

jsonCopyEdit{
    "error": "401 Unauthorized"
}

Use Basic Authentication in Postman or cURL:

Postman:

1. Go to Authorization tab → Select Basic Auth.

2. Enter APEX username & password.

3. Send the request.

cURL:

bashCopyEditcurl -u apex_user:your_password -X GET https://yourserver/ords/hr_api/employees/

✅ Now, only authenticated users can access the HR API.

---

Conclusion

✔ Enforced authentication using ORDS roles and privileges
✔ Restricted API access to APEX users
✔ Secured all RESTful endpoints

OAuth2 and JWT Authentication in ORDS (Oracle REST Data Services)

When exposing Oracle database objects as RESTful services using ORDS, securing them with OAuth2 and JWT (JSON Web Token) is recommended for better security and control. Below, I’ll explain both authentication methods and provide detailed implementation steps.

---

1. OAuth2 Authentication in ORDS

Step 1: Enable OAuth2 in ORDS

To use OAuth2, ensure ORDS is configured for OAuth2.
Run the following command in SQL Developer (as ORDS_METADATA user):

sqlCopyEditBEGIN
    ORDS.ENABLE_SCHEMA(
        p_enabled => TRUE,
        p_schema => 'HR',
        p_url_mapping_type => 'BASE_PATH',
        p_url_mapping_pattern => 'hr_api',
        p_auto_rest_auth => TRUE
    );
    COMMIT;
END;
/

✅ This enforces authentication for all endpoints under hr_api/.

---

Step 2: Create an OAuth2 Client

Run the following command to create an OAuth2 client in ORDS:

sqlCopyEditBEGIN
    ORDS.CREATE_OAUTH_CLIENT(
        p_name            => 'hr_client',
        p_grant_type      => 'client_credentials',
        p_owner           => 'HR',
        p_description     => 'HR API OAuth2 Client'
    );
    COMMIT;
END;
/

✅ This creates an OAuth2 client (hr_client) with client credentials authentication.

---

Step 3: Get the Client Secret

Run the following query to retrieve the client secret:

sqlCopyEditSELECT name, client_id, client_secret FROM user_ords_clients;

✅ Note the client_id and client_secret, as they will be used for authentication.

---

Step 4: Obtain an Access Token

Use curl or Postman to request an OAuth2 access token:

bashCopyEditcurl -X POST https://yourserver/ords/hr/oauth/token \
    -H "Content-Type: application/x-www-form-urlencoded" \
    -d "grant_type=client_credentials" \
    -d "client_id=hr_client" \
    -d "client_secret=your_client_secret"

✅ This returns a response like:

jsonCopyEdit{
    "access_token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...",
    "token_type": "bearer",
    "expires_in": 3600
}

🔹 Use this access_token in API requests.

---

Step 5: Use Access Token for API Calls

Once you have the access token, send API requests using Bearer Token Authentication:

bashCopyEditcurl -X GET https://yourserver/ords/hr_api/employees/ \
    -H "Authorization: Bearer eyJhbGciOiJIUzI1Ni..."

✅ Now, access is granted only if a valid token is provided.

---

2. JWT Authentication in ORDS

Instead of OAuth2, you can use JWT (JSON Web Token) authentication for a stateless and secure API.

Step 1: Enable JWT Authentication

Run the following command to enable JWT authentication in ORDS:

sqlCopyEditBEGIN
    ORDS.ENABLE_SCHEMA(
        p_enabled => TRUE,
        p_schema => 'HR',
        p_url_mapping_type => 'BASE_PATH',
        p_url_mapping_pattern => 'hr_api',
        p_auto_rest_auth => TRUE
    );
    COMMIT;
END;
/

✅ This ensures that JWT is required for API access.

---

Step 2: Create a JWT-Based Role and Privilege

sqlCopyEditBEGIN
    ORDS.CREATE_ROLE('hr_jwt_role');
    COMMIT;
END;
/

Then, create a privilege that requires a JWT:

sqlCopyEditBEGIN
    ORDS.DEFINE_PRIVILEGE(
        p_privilege_name => 'hr_jwt_access',
        p_role => 'hr_jwt_role',
        p_description => 'Access to HR API via JWT'
    );
    COMMIT;
END;
/

✅ Now, only users with valid JWT tokens can access the API.

---

Step 3: Obtain a JWT Token

1. Create a JWT provider (e.g., Keycloak, Auth0, Oracle IDCS).

2. Use the client_id and client_secret to request a JWT.

3. ORDS will validate the JWT token before granting access.

Example JWT token request:

bashCopyEditcurl -X POST https://your-auth-server/oauth/token \
    -H "Content-Type: application/json" \
    -d '{
        "grant_type": "password",
        "client_id": "your_client_id",
        "client_secret": "your_client_secret",
        "username": "your_username",
        "password": "your_password"
    }'

✅ This returns a JWT token similar to OAuth2.

---

Step 4: Use JWT for API Calls

bashCopyEditcurl -X GET https://yourserver/ords/hr_api/employees/ \
    -H "Authorization: Bearer eyJhbGciOiJIUzI1Ni..."

✅ ORDS verifies the JWT and grants or denies access.

---

Conclusion

✅ OAuth2 is ideal for external applications.
✅ JWT is best for microservices & secure APIs.