Build Oracle REST Data Services (ORDS) in KeepTool 16

Updated October 2025

---

Introduction

Welcome back to the KeepTool blog!

In this article, we’ll demonstrate how KeepTool 16 makes it easy to work with Oracle REST Data Services (ORDS) — a powerful framework that exposes your Oracle database as RESTful web services.

With ORDS, you can access database objects using standard HTTP(S) requests (GET, POST, PUT, DELETE) and return results as JSON. This enables lightweight integration between Oracle databases and modern applications written in JavaScript, Python, or any other language that can consume REST APIs.

ORDS is the foundation of many Oracle technologies, including:

• Oracle APEX, where it provides REST access for applications and REST-enabled SQL queries

• Custom APIs, allowing developers to expose data and logic from their own schemas

By combining KeepTool Hora with ORDS, you can design, test, and manage REST services directly from a graphical interface — no manual PL/SQL coding required.

---

Understanding the ORDS Architecture and URL Structure

Every ORDS endpoint corresponds to a specific resource in your database and follows a predictable URL structure:

Let’s break down the components:

• hostname:port — The host and port where ORDS runs (e.g. localhost:8080).

• ords — The web application context path.

• <pldb> — The pluggable database (PDB) name.

• <schema> — The database schema being exposed.

• module — A logical grouping of related templates and handlers (like an API module).

• template — Defines the URI pattern for a resource (e.g. /employees/).

• :id — A URI parameter that identifies a specific record.

Each template can have one or more handlers, which define what happens when a client performs a GET, POST, PUT, or DELETE request on that endpoint.

---

Installing and Configuring ORDS

Before we use KeepTool to manage ORDS objects, ORDS itself must be installed and configured on your Oracle instance.

1. Download ORDS from https://download.oracle.com/otn_software/java/ords/ords-latest.zip .

2. Inside the installation folder, locate the params directory — it contains ords_params.properties, which controls configuration values such as the database connection and context path.

3. ORDS can be deployed in several modes:

   • Standalone mode (great for testing and development)

   • Apache Tomcat

   • Oracle WebLogic Server

4. This tutorial uses the standalone mode, which runs directly from the command line.

5. During the first run, ORDS will ask for:

   • A database connection (usually your pluggable database or container database)

   • A privileged administrative user, often PDBADMIN

6. ORDS automatically creates two internal schemas:

   • ORDS_METADATA – stores definitions and configuration metadata

   • ORDS_PUBLIC_USER – the schema used for web connections

These schemas are the foundation on which KeepTool builds its ORDS management interface.

---

Starting the ORDS Server (Standalone Mode on Windows)

After installation, make sure the ORDS\bin folder is included in your system PATH.

You can then start the server by running the following command:

ORDS will open a lightweight web server that listens for HTTP and HTTPS requests.
You can now access it in a browser or through KeepTool.

---

Enabling ORDS for a Schema in KeepTool

In KeepTool’s Hora, ORDS functionality can be found under the “Additional” menu.
The ORDS Schema grid displays which schemas are currently REST-enabled.

If you have ORDS administrator privileges, you can right-click in this grid and select:
👉 “Enable Schema”

A dialog appears that allows you to:

• Choose the schema (e.g. HR)

• Define a URI mapping pattern (default: lowercase schema name, e.g. /hr/)

Once confirmed, Hora executes the necessary ORDS PL/SQL calls to enable REST for that schema.
If you are connected as a normal user, you’ll only see your own schema listed.

---

Creating a REST Module

An ORDS module is a logical container for templates and handlers. Think of it as a small API project — for example, you might create a module named /hr_demo/.

To create one in KeepTool:

1. Go to the Modules tab.

2. Right-click → “Create Module”.

3. Enter the module name and base URI.

4. Confirm your changes.

The new module will appear in the list, along with its context path and schema.

---

Creating a ORDS Template

A template defines a resource path within a module. For example, you might create a template /emp to represent employee data.

Steps:

1. Go to the Templates tab.

2. Right-click → “Create Template”.

3. Assign the new template to an existing module (e.g. hr_demo).

4. Enter a URI pattern such as emp.

5. Add an optional comment to describe its purpose.

The new template will be listed, and Hora automatically shows sample HTTP and HTTPS URIs you can use later for testing.

---

Defining a Handler (the REST Logic)

Each template needs one or more handlers to define how incoming requests are processed.

To create one in Hora:

1. Open the Handlers tab.

2. Right-click → “Create Handler”.

3. Select the template (e.g. /emp).

4. Choose the HTTP method (GET, POST, PUT, or DELETE).

5. Choose the input/output format (typically JSON).

6. In the SQL editor, write your query or PL/SQL logic.

Then define parameters for :id and :sal via the parameters grid below the SQL editor:

• Right-click → “Define Parameter”

• Add names, data types, and default values if needed

Hora automatically generates sample URIs for testing, for example:

---

Testing the REST service

Now it’s time to verify that your new service works.
With the ORDS server running:

1. Copy one of the generated URIs from Hora.

2. Paste it into your browser or a REST client (such as Postman).

3. The response should be JSON data from the EMPLOYEES table.

You can easily pass URL parameters such as ?id=123 or ?sal=10000 to filter the data dynamically.
This allows your REST service to deliver only the relevant records, making it straightforward to integrate database queries into web or mobile applications.

As you can see, the JSON response contains a single EMPLOYEE object along with several supporting HTTP reference fields.
Alternatively, you can filter results by salary by adding a sal parameter to the request.
For example, querying with ?sal=10000 returns all employees whose salary exceeds 10,000.

---

Expanding Your ORDS Services

Once you’ve mastered the basics, you can extend your ORDS service by:

• Adding POST, PUT, and DELETE handlers for data modification

• Returning hierarchical data or JSON arrays

• Using authorization schemes to control access

• Integrating your endpoints into APEX or other web apps

ORDS is a full-featured web layer for Oracle — and KeepTool provides an intuitive GUI for exploring and managing it without writing complex PL/SQL packages manually.

---

Summary

With KeepTool 16 and Oracle REST Data Services, you can:

1. Enable ORDS on a schema

2. Create REST modules, templates, and handlers

3. Define SQL-based REST endpoints in minutes

4. Test and preview your REST APIs directly in Hora

This streamlined workflow allows developers, DBAs, and analysts to build modern, standards-compliant REST services from existing Oracle data — quickly and visually.

---