Zero-Code Vector Search for Oracle Database 26ai

Oracle REST Data Services (ORDS) 26.1.0 is out and one of the new features is Vector Search which we’ll explore in this article. Before diving in, this post assumes you have already worked through:

👉 Getting Started with Vectors in 23ai

That article is the foundation and introduces the CCNEWS dataset (200,000 news headlines) along with embedding generation and SQL-based vector search. The step by step article is easy to follow and even involves using ORDS to define a REST service for performing that vector search. What is also nice about the article is that everything it covers is easy to implement and does not require any external services. In my case, I run all of it locally using ORDS and Oracle AI Database 26ai Free Container Image in docker.

Here, we build directly on that article and show how Oracle REST Data Services 26.1.0 simplifies exposing that Vector Search functionality over REST with no code in Oracle Database 26ai.

1. The CCNEWS Table (From the Prerequisite)

Assuming you have followed all the steps in the article you have a 26ai database with a VECTOR_USER schema that has a CCNEWS table.

So that table was created with this statement from the original blog post:

create table if not exists CCNEWS (
    id number(10) not null,
    info VARCHAR2(4000),
    vec VECTOR
);

Important: Primary Key Requirement

ORDS Vector Search on REST enabled objects requires a primary key. If not already defined, run this:

ALTER TABLE VECTOR_USER.CCNEWS
ADD CONSTRAINT CCNEWS_PK PRIMARY KEY (ID);

2. Vector Search in SQL (From the Article)

The Database & Cloud Technology article uses Oracle’s VECTOR_DISTANCE function combined with VECTOR_EMBEDDING for a similarity search:

select id, info
from CCNEWS
order by vector_distance(vec, TO_VECTOR(VECTOR_EMBEDDING(doc_model USING 'little red corvette' as data)), COSINE)
fetch approx first 5 rows only;

Key points:

VECTOR_EMBEDDING generates the query vector
VECTOR_DISTANCE computes similarity
COSINE is used as the distance metric
FETCH APPROX limits the number of rows returned using an approximate nearest neighbour search

3. The ORDS Handler Approach

To expose that vector_distance based query via ORDS previously, you needed a custom service:

BEGIN
  ORDS.define_service(
    p_module_name    => 'vectorsearch',
    p_base_path      => 'ai/',
    p_pattern        => 'ccnews/:mysentence',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'SELECT id,info from CCNEWS 
                         order by vector_distance(
                           vec, 
                           TO_VECTOR(VECTOR_EMBEDDING(doc_model USING :mysentence as data)), 
                           EUCLIDEAN
                         ) 
                         fetch approx first 5 rows only',
    p_items_per_page => 0);  COMMIT;
END;
/

Call it

curl 'http://localhost:8080/ords/vector_user/ai/ccnews/little%20red%20corvette'

That will get you a response like this:

{
   "count" : 5,
   "hasMore" : false,
   "items" : [
      {
         "id" : 142622,
         "info" : "CHICAGO -- Small cars have been getting great copy lately. They're lauded for their anti-gas guzzling efficiency and improved safety features."
      },
      {
         "id" : 79167,
         "info" : "A red car will start on pole as usual, but the probability that Ferrari will end the season with yet another win, their 16th in 18 races, is not quite as grim as it sounds."
      },
      {
         "id" : 182103,
         "info" : "With her red velvet hooded cape, Cybill Shepherd could have been Little Red Riding Hood."
      },
      {
         "id" : 182000,
         "info" : "When it comes to helping the environment or saving money at the pump, it's hard to beat one of the new mini-cars, like the Nissan Versa, Toyota Yaris and Mini Cooper."
      },
      {
         "id" : 57282,
         "info" : "Customized cars that use commercial logos as a creative palette."
      }
   ],
   "limit" : 0,
   "links" : [
      {
         "href" : "http://localhost:8080/ords/vector_user/ai/ccnews/little%20red%20corvette",
         "rel" : "self"
      },
      {
         "href" : "http://localhost:8080/ords/vector_user/metadata-catalog/ai/ccnews/item",
         "rel" : "describedby"
      }
   ],
   "offset" : 0
}

Observations

The query string (:mysentence) is passed directly into VECTOR_EMBEDDING
Distance metric is explicitly set to EUCLIDEAN
ORDS handles binding, but you still manage:
- Module definition
- URL structure
- SQL text

This works—but it involves encoding a query as a service.

4. ORDS 26.1.0: Built-in Vector Search

With ORDS 26.1.0, none of that is required for basic vector search.

Step 1: Enable AutoREST

As the VECTOR_USER run this to REST Enable the CCNEWS table.

BEGIN
  ORDS.enable_object(
    p_object       => 'CCNEWS'
  );
  COMMIT;
END;
/

That will give you a huge range of RESTful services for interacting with the table without any coding.

The OpenAPI V3 document describing the operations now available for the CCNEWS table.

Step 2: Use the Built-in Endpoint

Let’s look at the POST /vectorSearch operation. To perform the Vector Search there are certain values that must be provided in the request body. The ORDS Vector Search documentation describes them all so we will not go into details here.

Performing a Vector Search in this case requires a POST

The full path for the Vector Search operation on the CCNEWS table in the VECTOR_USER will look like this:

POST /ords/vector_user/ccnews/vectorSearch

5. Equivalent Vector Search via ORDS

Instead of embedding generation happening inside SQL, the client supplies the vector:

curl -X POST http://localhost:8080/ords/vector_user/ccnews/vectorSearch \
  -H "Content-Type: application/json" \
  -d '{
    "vector": [/* embedding for "little red corvette" */],
    "vectorColumn": "VEC",
    "columns": ["ID", "INFO"],
    "limit": 5,
    "distanceMetric": "EUCLIDEAN"
  }'

The client will have to know the AI model used for populating the VECTOR data but it has a wider range of options. That includes further filtering of rows in the same manner as what can be achieved through GET on the same table. See Filtering In Queries section of the ORDS documentation.

Key Difference vs Custom Handler Approach

Custom Hander	AutoREST Vector Search
Embedding generated in SQL (`VECTOR_EMBEDDING`)	Embedding supplied by client
Custom handler required	Built-in endpoint
SQL embedded in ORDS	No SQL required
URL parameter (`:mysentence`)	JSON payload
No refinement on search criteria	Client can specify order, number of rows to return, specific columns to include in the response and additional filter criteria.
Primary Key Hyperlinks require extra coding	Automatic Primary Key Hyperlinks for each row in the response.

Keep in mind that both approaches can be used at the same time. In some scenarios the client will know the model for the table or have the means to generate a vector array to search with.

Note that as with the initial article, the services provided are not secured and the ORDS instance is not using HTTPS for traffic. That’s fine for this exercise but not for production.

6. Let’s see it in action

Since the client now has to provide the vector to search for, to perform the exact same search one must get an embedding for little red corvette using the same model. Easily done by running:

select VECTOR_EMBEDDING(doc_model USING 'litle red corvette' as data)

The vector representation of ‘little red corvette’

Using that in a cURL command will look like this:

curl --location 'http://localhost:8080/ords/vector_user/ccnews/vectorSearch' \
--header 'Content-Type: application/json' \
--data '{
  "limit": 5,
  "vector": [-3.07053532E-002,5.85529916E-002,-6.74578473E-002,1.2257684E-002,6.316524E-002,-2.40801703E-002,7.64445635E-003,3.26403342E-002,-5.40543646E-002,6.23201914E-002,8.92788619E-002,6.09119842E-003,8.08357634E-003,-2.87142694E-002,-3.36633772E-002,-1.59669053E-002,1.10877827E-001,5.03755994E-002,-3.0524137E-003,2.27314071E-003,-8.80712047E-002,4.54846956E-002,-1.76324453E-002,5.4300325E-003,-7.49083012E-002,-7.84977898E-003,-1.03094578E-001,3.23520638E-002,-5.20449802E-002,-8.81914124E-002,5.09979203E-003,-2.28075747E-004,8.27756822E-002,1.80074794E-003,-2.35566851E-002,-5.18809892E-002,2.15939945E-003,-2.15763152E-002,-4.92988527E-002,4.24234103E-003,-3.89126204E-002,-1.08256757E-001,1.64869614E-002,1.02524489E-001,9.25767645E-002,-2.06774045E-002,-1.8368477E-002,1.33422706E-002,8.78902823E-002,-6.02279529E-002,3.43158655E-002,-9.60535407E-002,-2.74095945E-002,-2.8675979E-002,4.55864891E-002,2.63328925E-002,1.72216333E-002,-1.97178405E-002,6.42947182E-002,-2.79306769E-002,-2.80832332E-002,3.09923105E-002,-2.81780143E-003,-2.21623778E-002,1.51646696E-002,-5.2848449E-003,3.95465866E-002,-3.46338265E-002,-5.43230511E-002,3.11623141E-002,3.56283262E-002,-3.72483805E-002,-3.45825478E-002,-5.79019776E-003,-5.63643761E-002,-3.12294867E-002,8.83353204E-002,-3.03997919E-002,-1.23773552E-002,-5.15865511E-004,-3.59914801E-003,-1.69582665E-002,-7.76734948E-002,-4.63231243E-002,6.63533583E-002,-3.5777919E-002,4.40812893E-002,5.63600361E-002,-5.28740976E-003,5.69755659E-002,-5.50081208E-002,6.87551275E-002,3.28732915E-002,1.58981874E-003,-5.56428321E-002,-7.57648051E-002,5.8034081E-002,-6.68441579E-002,-3.73762697E-002,1.87148571E-001,1.12300172E-001,4.12532054E-002,1.00773335E-001,5.72219677E-003,-3.81047241E-002,3.35666686E-002,7.77937025E-002,4.77051288E-002,-2.15624534E-002,-1.18911706E-001,-2.00342126E-002,7.05829309E-003,-8.89590383E-002,-2.68359855E-002,-6.41334206E-002,-4.22521196E-002,-7.08332583E-002,-5.96234351E-002,-3.76171395E-002,3.2652244E-002,-4.96881641E-002,-7.7103138E-002,-2.48034019E-002,5.12335682E-003,7.085336E-002,-1.47623777E-001,5.37105873E-002,3.64335366E-002,-2.60975622E-002,5.82129369E-003,-8.14416409E-002,6.67800754E-002,2.08732914E-002,1.19046867E-001,5.18846847E-002,9.57678165E-003,-3.64857614E-002,7.76776895E-002,-3.93842794E-002,-9.8264575E-002,-4.7032699E-002,-5.16803861E-002,3.27760428E-002,3.26377489E-002,-6.59162626E-002,-1.05144009E-001,-3.22745182E-002,-5.92551716E-002,-1.13562755E-001,1.25622064E-001,1.14990778E-001,8.03940892E-002,7.66430572E-002,5.89383543E-002,-9.20753181E-003,-3.60060111E-002,-2.22714879E-002,6.28050696E-003,3.42132933E-002,6.13801964E-002,1.75386257E-002,-1.80134606E-002,-3.39894332E-002,-2.67520901E-002,-3.81485149E-002,-1.25639021E-001,2.98075918E-002,-4.81692702E-003,-5.38430316E-003,-2.68271938E-002,-8.80813152E-002,7.9674378E-002,8.1708869E-003,4.49210731E-003,4.93363179E-002,9.08218604E-003,2.98463684E-002,1.67458635E-002,4.30809148E-002,-6.30635628E-003,-3.66868116E-002,2.24808641E-002,-1.97107941E-002,4.19183187E-002,-2.07382943E-002,7.16343299E-002,-1.17987087E-002,2.83470232E-004,5.55337667E-002,3.85905765E-002,2.60068625E-002,2.47582365E-002,5.64134903E-002,7.59846391E-003,-6.95330277E-002,-5.1294744E-002,-2.89460607E-002,3.93225159E-003,2.4446765E-002,-5.46594113E-002,4.43796702E-002,-7.10089058E-002,3.78895737E-002,-7.63406715E-005,3.07969283E-002,-1.99558139E-002,1.54345371E-002,-8.88657868E-002,8.84383637E-003,-2.23286971E-002,-2.45919079E-002,-3.978635E-002,5.16027845E-002,-2.32025366E-002,-2.45015081E-002,7.69026503E-002,-2.96384431E-002,1.53524885E-002,-2.90377047E-002,-2.80109104E-002,4.53478694E-002,-3.58900763E-002,2.99728755E-002,-1.45389412E-033,1.88392568E-002,-6.47298545E-002,1.01908617E-001,-1.31587433E-002,-1.85926091E-002,-2.86066998E-002,-1.33587588E-002,-1.80519139E-003,-1.71739608E-002,-4.6595633E-002,-4.6931956E-002,8.30989629E-002,1.53468493E-002,-5.12199767E-004,1.04117207E-001,-1.2125371E-002,-1.8739434E-002,4.73202318E-002,2.74012014E-002,8.04362372E-002,-2.32837833E-002,-7.19153807E-002,2.11932212E-002,2.4031328E-002,-3.77282277E-002,5.17850779E-002,-6.48160726E-002,3.25367749E-002,-2.89279353E-002,-6.88138753E-002,-2.91798194E-003,-4.56949286E-002,2.95383874E-002,1.14016305E-003,-3.78589332E-002,-6.03643758E-003,2.98500806E-002,-4.85490002E-002,4.10790853E-002,-2.5354486E-002,-9.72109195E-003,-2.7718816E-002,-4.33942713E-002,6.56506717E-002,9.87958163E-004,-6.58345083E-003,5.29988557E-002,-1.82564501E-002,6.05667047E-002,-2.87184888E-003,1.63399044E-003,-8.17960426E-002,2.47597024E-002,3.14391218E-002,-2.03312412E-002,4.33304794E-002,4.25058976E-002,6.37011081E-002,3.73960882E-002,-1.86836459E-002,1.70387905E-002,2.79710498E-002,-3.8476523E-002,-2.18143836E-002,-2.01852974E-002,-2.65123788E-002,-1.21113239E-002,1.73476618E-002,-2.11952161E-002,-2.85385773E-002,-8.24076403E-003,2.16407981E-002,9.9034654E-003,-4.52033617E-002,-1.22422148E-002,1.14305643E-002,9.72609892E-002,4.52578627E-002,4.41269763E-002,-2.55465172E-002,-3.49305272E-002,-5.51500618E-002,5.64764142E-002,1.05597645E-001,-1.18475445E-002,-6.03516959E-002,4.99546118E-002,4.38831076E-002,-4.56738211E-002,4.00517136E-002,1.65134482E-002,8.81506428E-002,-2.3371242E-002,9.05854441E-003,-1.08199259E-002,1.63877298E-032,-1.96534507E-002,6.13839971E-003,2.32135262E-002,-3.43828648E-002,5.41330278E-002,1.27508137E-002,5.94790131E-002,-6.85228733E-003,-9.60054323E-002,-6.71492144E-002,-5.39250374E-002,-1.74073465E-002,1.59162041E-002,-7.3701553E-002,-2.2581771E-002,3.65978405E-002,1.47042209E-002,-7.16396943E-002,7.73388445E-002,-1.16045386E-001,2.4834685E-002,7.23040178E-002,3.32084112E-002,3.31360511E-002,1.10893575E-002,-9.28622484E-002,-4.19785641E-002,2.29144283E-002,9.44261849E-002,-8.33413657E-003,2.91968007E-002,5.88697419E-002,-1.39651537E-001,-6.11314899E-004,2.57693212E-002,-4.38742265E-002,1.74329523E-002,8.57599378E-002,5.69615103E-002,-9.78489816E-002,1.14794098E-001,-1.86840519E-002,-7.20299184E-002,3.69497389E-002,1.7280845E-002,2.3233227E-003,2.18159296E-002,-7.62700588E-002,4.16742787E-002,-3.8183827E-002,2.58755721E-002,4.36592326E-002,-3.85195836E-002,-1.25658726E-002,-6.86033443E-002,-2.30002753E-003,-4.88031134E-002,5.49012981E-002,-8.51705298E-002,2.89389715E-002,-4.43107588E-003,-2.64256331E-003,-2.68490277E-002,2.34105438E-002]
}'

Since it is not easy to manage long cURL commands like this I imported the OpenAPI V3 document from http://localhost:8080/ords/vector_user/open-api-catalog/ccnews/ into Postman..

The CCNEWS operations available to be called from Postman

Making that little red corvette search now looks like this:

The request payload is just two attributes: limit and vector

Everything else, such as what columns to return or what distance metric to use, will default. The response will look like this:

Response includes the matching rows and also a link to the individual row on the table

Runtime error?

If you are getting a ORA-25716: The identifier contains a double quotation mark (") character exception when calling the /vectorSearch service then check that you have defined a primary key for the CCNEWS table.

7. Distance Metric Comparison

Notice an important shift:

Old handler used: EUCLIDEAN
SQL example used: COSINE
ORDS allows either via distanceMetric

Example

{
  "distanceMetric": "EUCLIDEAN"
}

{
  "distanceMetric": "COSINE"
}

Impact at Scale (200K Headlines)

COSINE → best semantic similarity (recommended for text)
EUCLIDEAN → slightly different ranking due to magnitude sensitivity

This is now a runtime choice, not hardcoded in SQL.

8. What Changed Architecturally?

Then

SQL handled:
- embedding generation
- similarity calculation
ORDS:
- exposed SQL via REST

Now (ORDS 26.1.0)

Database:
- stores vectors
- performs similarity search
Client:
- generates embeddings
ORDS:
- provides a native vector search endpoint

Final Thoughts

Using the same CCNEWS dataset from the prerequisite article, we’ve moved from:

Custom REST services wrapping SQL

to:

Native vector search exposed automatically via ORDS

With Oracle Database 26ai + ORDS 26.1.0, the requirements are minimal:

A VECTOR column
A primary key
One ORDS.enable_object call

And your dataset becomes:

A fully functional semantic search API

Implement an MCP Server with ORDS and MLE JavaScript

Oracle provides MCP Server options for your database

If you are serious about building out a secure, scalable and fully integrated MCP experience in your organisation then focus on dedicated Oracle products rather than rolling your own. Start with:

If you want to explore the specification and learn some concepts then read on…

🎯 The Objective

An example of what we want to achieve with the ORDS Concert Application data and an ORDS MCP Server Handler

📍 Introduction

The Model Context Protocol (MCP) is a growing standard for enabling AI tools to interact seamlessly via structured requests. MCP is an open standard that enables AI assistants like Claude, ChatGPT, or Cline to securely connect to external data sources and tools. Think of it as a universal adapter that lets AI models interact with the world beyond their training data.

In this article, we’ll demonstrate how to implement an MCP server directly in Oracle using ORDS (Oracle REST Data Services) and JavaScript MLE (Multilingual Engine). With just a few lines of JavaScript and SQL, you can build an HTTP-based MCP server embedded in your Oracle database. This runs securely, for free, on the hosted Autonomous Database at cloud.oracle.com. Of course, you can choose to run your own setup on-premise too.

This article continues the series on MLE-based handlers—check out earlier examples at peterobrien.blog/tag/multilingual-engine. In particular, we build on concepts introduced in JSON-RPC in ORDS Using MLE JavaScript (Oracle 23ai) and the ORDS Sample Application that is mentioned in 🎉 Data Science in Concert: Inspect Data from the ORDS Sample Application with R!

Here be dragons! Keep in mind that you are using AI tools where access to your database is being permitted. Even if you are running your LLM inside your network, you are still asking an agent to decide on actions based on what it discovers. Be careful with your data and database resources. Only make accessible what is required for the workflow you want to automate. Use the database security and auditing facilities available.
Security, Security, Security

🔍 What Is MCP?

The Model Context Protocol is a JSON-RPC–based interface for exchanging AI capabilities, models, prompts, and resource metadata. Without MCP, AI assistants are like incredibly smart people locked in a room with no internet, phone, or way to access current information or perform actions. They can only work with what they learned during training. MCP breaks down these walls by providing a standardised way for AI models to:

Stay current with live information
Access real-time data from APIs, databases, and services
Control applications and systems
Retrieve files and documents
Execute actions in the real world

MCP operates on a client-server architecture:

MCP Client: The AI assistant (like Cline or Claude)
MCP Server: A program that provides specific capabilities (tools, resources, or prompts)

The protocol defines how these communicate through three main primitives:

Resources: Static or dynamic content that the AI can reference.
Tools: Functions the AI can execute.
Prompts: Templates that help the AI understand how to use resources and tools effectively in specific contexts.

The MCP specification states that a server should supports methods such as:

initialize: describes the server and its capabilities
tools/list: enumerates available tools
tools/call: executes a named tool
prompts/list: lists available prompts
resources/list: lists downloadable resources

That’s not an exhaustive list and they are not all mandatory to implement. In fact, the only one truly required is initialize but you might find an MCP client that expects others to be implemented.

An MCP server can be local, on the same machine running your MCP client (using standard i/o) or remote (using streamable HTTP). The Model Context Protocol server for the Oracle Database is an example of a local MCP server which is accessed using standard i/o. In this article we’ll implement a HTTP based remote server which will not involve any streamable functionality but will support the synchronous request/response aspects of the specification. Note that the most recent version of the MCP specification(Protocol Revision: 2025-06-18) has removed server side events as a supported transport.

MCP clients (like Claude) expect a standard JSON response structure and often use OAuth2 for authentication. Not every authorisation scenario outlined in the specification will be covered in this article.

🚀 Why ORDS + MLE JavaScript?

ORDS can serve as the HTTP interface for all of this functionality — directly from your Oracle database. By combining ORDS with the JavaScript Multilingual Engine (MLE) in Oracle 23ai, you can:

Implement MCP server methods entirely in server-side JavaScript.
Query live database content when responding to MCP tool calls.
Package and publish database-backed tools to AI agents without building a separate backend service.
Secure everything with OAuth2 client credentials.

This makes it possible to turn your database into an MCP-compatible API that works with modern AI tooling—without leaving the Oracle environment and without sharing more than you need to.

In this example we’ll provide a get_events tool which lists the results of querying the SEARCH_VIEW in the ORDS_CONCERT_APP schema. That information can be gleaned from the REST Enabled endpoint for that view but your AI Assistant does not have access to that. When coding your own MCP Server through ORDS handlers you can provide access to data as you see fit.

Using the Model Context Protocol server for the Oracle Database will give full access to your database schema and therefore is the most powerful option but you’re unlikely to be sharing those DB credentials with anyone else. Implementing your own MCP Server allows you to greatly restrict that access.

🛠️ MCP Handler Implementation in ORDS

There’s some PL/SQL in this article but it is just the invocation of ORDS APIs to create the user defined resources. The real logic is in JavaScript which executes in the database. You can expand on this and implement your own logic for tools, resources or prompts. Refer to the ORDS mle/handler documentation for more information on that.

Note that although we’re using OAuth2 client credentials flow there are other authorisation options available. This is something that one must choose wisely because there are some aspects of the MCP specification with regard to authorisation discovery which is not currently supported by ORDS. Moreover, there are MCP clients that do not implement or support all the options available. In some cases, such as with Claude desktop, that support may only be available for certain paid subscriptions or premium level plans.

In this article we will protect the ORDS module and have a client credentials OAuth2 client which means we have to request an access token manually and then use it in our MCP server reference. That is not ideal but is a limitation of some of the tools used. More about limitations later.

Here’s the actual ORDS module and handler setup used in our implementation…

🧩 1. Setup – REST enable the database schema and grant MLE privileges

This article is based on the ORDS Sample Application so the database account used is ORDS_CONCERT_APP. It will already be REST Enabled but will require the privileges to execute JavaScript in the database. You can run this as an administrator user, such as ADMIN in the Autonomous Database.

GRANT EXECUTE ON JAVASCRIPT TO ORDS_CONCERT_APP;
GRANT EXECUTE DYNAMIC MLE TO ORDS_CONCERT_APP;

🧱 2. Defining the MCP Module and Template

Now, while logged in as the ORDS_CONCERT_APP user run the following to create the structure for your implementation. This will define a path /mcp/server for the service but it will require a handler for the POST method.

BEGIN
ORDS.DEFINE_MODULE(
    p_module_name    => 'mcp',
    p_base_path      => '/mcp/');

ORDS.DEFINE_TEMPLATE(
    p_module_name    => 'mcp',
    p_pattern        => 'server');

END;
/

✍️ 3. JavaScript MLE Handler Source

The handler responds to MCP methods such as initialize, tools/list, and tools/call. Here I detail the JavaScript portion so that section can be described and explained:

      (req, res) => {
        let response = {
          jsonrpc: "2.0",
          id: null
        };

        try {
          const rpc = req.body;
          response.id = rpc.id ?? null;

          if (rpc.jsonrpc !== "2.0" || !rpc.method) {
            response.error = {
              code: -32600,
              message: "Invalid Request"
            };
          } else if (rpc.method === "initialize") {
            // Respond with server capabilities
            response.result = {
              protocolVersion:"2025-06-18",
              capabilities: {
                tools: {listChanged: false}
              },
              serverInfo: {
                name: "ORDSHandlerExampleServer",
                title: "Example MCP Server implemented in ORDS mle/javascript",
                version: "0.0.1"
              }
            };
          } else if (rpc.method === "tools/list") {
            // Respond with tools list
            response.result = {
                tools: [
                    {
                        name: "get_events",
                        title: "Events Information Provider",
                        description: "Get information on ORDS (Oracle REST Data Services) Sample Application events. The events view brings together artist, venue and schedule concert data. The attributes include EVENT_NAME,ARTIST_NAME,ARTIST_ID,EVENT_ID,EVENT_DATE,EVENT_DETAILS,EVENT_STATUS_NAME,EVENT_STATUS_ID,VENUE_ID,VENUE_NAME,CITY_NAME,MUSIC_GENRES",
                        inputSchema: {
                            type: "object"
                        }
                    }
                ]
            };
          } else if (rpc.method === "tools/call") {
            // Call the get_events tool if that was the tool requested
            if (rpc.params.name === "get_events") {
                const query = ''select * from search_view'';
                const res = session.execute(query);
                textContent = [];
                for (let row of res.rows) {
                  textContent.push({type:"text", text: JSON.stringify(row)});
                };

                response.result = {
                    content: textContent,
                    "isError": false
                };
            } else {
               response.error =  {
                    code: -32602,
                    message: "Unknown tool: invalid_tool_name"
                }
            }
          } else {
            // Unsupported method
            response.error = {
              code: -32601,
              message: "Method not found"
            };
          }

        } catch (e) {
          response.error = {
            code: -32700,
            message: "Parse error",
            data: e.message
          };
        }

        res.status(200);
        res.content_type("application/json");
        res.send(JSON.stringify(response));
      }

At line 2, similar to the /rpc/handler example in the previous article, we define the result structure for a successful response. We are expecting that all requests that we should act on will have an id, so the response as an id field. We copy that across at line 9.

Lines 16 – 28 deal with initialisation which tells the MCP client about the MCP server and its capabilities. In this case we’re saying that the MCP server implementation only provides tools and it does not send notifications if the tool definitions change.

...
} else if (rpc.method === "initialize") {
  response.result = {
    protocolVersion: "2025-06-18",
    capabilities: { tools: { listChanged: false } },
    serverInfo: {
      name: "ORDSHandlerExampleServer",
      title: "Example MCP Server implemented in ORDS mle/javascript",
      version: "0.0.1"
    }
  };
} else ...

Lines 29 – 42 deal with returning the list of tools that the MCP server supports. The description of the tool is important because that is what helps your AI Assistant determine if the tool will be useful for the given workflow.

...
} else if (rpc.method === "tools/list") {
  response.result = {
    tools: [
      {
        name: "get_events",
        title: "Events Information Provider",
        description: "Get information on ORDS (Oracle REST Data Services) Sample Application events. The events view brings together artist, venue and schedule concert data. The attributes include EVENT_NAME,ARTIST_NAME,ARTIST_ID,EVENT_ID,EVENT_DATE,EVENT_DETAILS,EVENT_STATUS_NAME,EVENT_STATUS_ID,VENUE_ID,VENUE_NAME,CITY_NAME,MUSIC_GENRES",
        inputSchema: {
           type: "object"
        }
    ]
  };
} else ...

Lines 43 – 62 are where the real action is. These lines deal with the invocation of the requested tool. In this case the only tool supported is called get_events which is for a specific query on the database. When you are implementing your own MCP server using ORDS handlers you can code this for your own AI integration needs. Note that the specification allows for various content types. For simplicity, in this case, we are constructing an array of text content entries. Although structuredContent could also be used here that content type is not supported by many MCP clients.

...
} else if (rpc.method === "tools/call") {
  if (rpc.params.name === "get_events") {
    const query = ''select * from search_view'';
    const res = session.execute(query);
    textContent = [];
    for (let row of res.rows) {
     textContent.push({type:"text", text: JSON.stringify(row)});
    };
 
    response.result = {
      content: textContent,
      "isError": false
    };
  } else {
    response.error = { code: -32602, message: "Unknown tool: invalid_tool_name" };
  }
} else ...

The remainder of the JavaScript code includes placeholders for listing prompts, resources and a little error handling. You can expand on these as you see fit.

Here’s the full handler definition statement to run in your database:

BEGIN
ORDS.DEFINE_HANDLER(
      p_module_name    => 'mcp',
      p_pattern        => 'server',
      p_method         => 'POST',
      p_source_type    => 'mle/javascript',
      p_items_per_page => 0,
      p_comments       => 'MCP server handler example',
      p_source         => 
'
      (req, res) => {
        let response = {
          jsonrpc: "2.0",
          id: null
        };

        try {
          const rpc = req.body;
          response.id = rpc.id ?? null;

          if (rpc.jsonrpc !== "2.0" || !rpc.method) {
            response.error = {
              code: -32600,
              message: "Invalid Request"
            };
          } else if (rpc.method === "initialize") {
            // Respond with server capabilities
            response.result = {
              protocolVersion:"2025-06-18",
              capabilities: {
                tools: {listChanged: false}
              },
              serverInfo: {
                name: "ORDSHandlerExampleServer",
                title: "Example MCP Server implemented in ORDS mle/javascript",
                version: "0.0.1"
              }
            };
          } else if (rpc.method === "tools/list") {
            // Respond with tools list
            response.result = {
                tools: [
                    {
                        name: "get_events",
                        title: "Events Information Provider",
                        description: "Get information on ORDS (Oracle REST Data Services) Sample Application events. The events view brings together artist, venue and schedule concert data. The attributes include EVENT_NAME,ARTIST_NAME,ARTIST_ID,EVENT_ID,EVENT_DATE,EVENT_DETAILS,EVENT_STATUS_NAME,EVENT_STATUS_ID,VENUE_ID,VENUE_NAME,CITY_NAME,MUSIC_GENRES",
                        inputSchema: {
                            type: "object"
                        }
                    }
                ]
            };
          } else if (rpc.method === "tools/call") {
            // Call the get_events tool if that was the tool requested
            if (rpc.params.name === "get_events") {
                const query = ''select * from search_view'';
                const res = session.execute(query);
                textContent = [];
                for (let row of res.rows) {
                  textContent.push({type:"text", text: JSON.stringify(row)});
                };

                response.result = {
                    content: textContent,
                    "isError": false
                };
            } else {
               response.error =  {
                    code: -32602,
                    message: "Unknown tool: invalid_tool_name"
                }
            }
          } else {
            // Unsupported method
            response.error = {
              code: -32601,
              message: "Method not found"
            };
          }

        } catch (e) {
          response.error = {
            code: -32700,
            message: "Parse error",
            data: e.message
          };
        }

        res.status(200);
        res.content_type("application/json");
        res.send(JSON.stringify(response));
      }
    ');
END;
/

🔐 Securing the MCP Handler

ORDS provides built-in OAuth2 support for securing handlers. In this example, we use the Client Credentials flow, where:

Using a client ID and secret request that ORDS issues a bearer token.
In your MCP Client configure the MCP Server entry to use that bearer token.
Requests to /mcp/server must include an Authorization: Bearer <token> header.

To secure the handler:

DECLARE
L_PRIV_ROLES owa.vc_arr;
L_PRIV_PATTERNS owa.vc_arr;
L_PRIV_MODULES owa.vc_arr;
BEGIN
ORDS.CREATE_ROLE(p_role_name => 'MCPAgentRole');

L_PRIV_ROLES( 1 ) := 'MCPAgentRole';
L_PRIV_MODULES( 1 ) := 'mcp';
ORDS.DEFINE_PRIVILEGE(
    P_PRIVILEGE_NAME => 'blog.peterobrien.MCPServerExamplePriv',
    P_ROLES => L_PRIV_ROLES,
    P_PATTERNS =>  L_PRIV_PATTERNS,
    P_MODULES => L_PRIV_MODULES,
    P_LABEL => 'MCP Server Example Privilege',
    P_DESCRIPTION => 'Restricts access to the example MCP Server module'
);
END;
/

Make sure the MCP client you use supports this flow (not all do—see Limitations below).

Speaking of clients, we must now define the OAuth2 client which will get access to this protected service. Note that in this case, in order to be able to use the access token for longer, we’re setting the token duration to 8 hours. This example specifies ChangeMe as the secret to use so now is a good time to choose another value or examine the ORDS_SECURITY documentation for other options for generating secrets.

DECLARE
  l_client_cred ords_types.t_client_credentials;
BEGIN
  l_client_cred := ORDS_SECURITY.REGISTER_CLIENT(
      p_name            => 'MCPClient',
      p_grant_type      => 'client_credentials',
      p_description     => 'MCP Client to the example MCP Server.',
      p_client_secret   => ords_types.oauth_client_secret(p_secret=>'ChangeMe'),
      p_support_email   => 'test@example.org',
      p_token_duration   => 28800);

  ORDS_SECURITY.GRANT_CLIENT_ROLE(
      p_client_name     => 'MCPClient',
      p_role_name => 'MCPAgentRole');
  COMMIT;
  sys.dbms_output.put_line('CLIENT_ID:'     || l_client_cred.client_key.client_id);
  sys.dbms_output.put_line('CLIENT_SECRET:' || l_client_cred.client_secret.secret);
END;
/

That will create a client and output the generated CLIENT_ID and CLIENT_SECRET. You will have to remember these values because they are needed to get an access token. This can be achieved through curl:

curl -i -k --user <client_id>:<client_secret> --data "grant_type=client_credentials" https://<my ORDS server>/ords/ords_concert_app/oauth/token

Replace the variables as appropriate. For example:

curl -i -k --user IV2YsD5Z0sr8_Wvgd0U1jQ..:ChangeMe --data "grant_type=client_credentials" https://c9abzixuw5nq9si-kop3yqis71qcmbx2.adb.eu-frankfurt-1.oraclecloudapps.com/ords/ords_concert_app/oauth/token

That will give you an access token which you will use later.

{
 "access_token":"8kbzUqtgsDrCTNKB6Xb32w",
 "token_type":"bearer",
 "expires_in":28800
}

🧪 Deploying and Testing the MCP Server

🛰️ Deploy to Oracle Autonomous Database

This handler works with Autonomous Database using their hosted ORDS with 23ai. That’s the simplest and most convenient to get started with this example although you can run this on your own infrastructure.

🧰 Test with Tools

You can test your MCP server using the following tools but there are other MCP clients available:

✅ MCP Inspector: Enter the /mcp/server URL and token to explore responses interactively.
🤖 Claude Desktop (with mcp-remote): Add your server as a remote tool and use get_events inside Claude chat.

✅ MCP Inspector

The MCP Inspector is a browser based tool for testing and debugging MCP servers. It provides an interactive interface and proxy mechanism that simplifies the development, inspection, and validation of MCP servers before integrating them with AI assistants or production systems. It is the best place to start verifying your handler.

It requires Node.js and is as easy to start as npx @modelcontextprotocol/inspector

Start the inspector – it hosts a mini web server and opens a browser to show the UI

*In the UI enter the MCP server address* – This is the full path for mine hosted in Frankfurt region

Set the Authorization header with the access token as Bearer Token value

The inspector tool invoking your hosted MCP Server implementation

With the Authentication details provided just press the Connect button. As you can see from the above recording you get live data from your MCP Server mle/handler implementation. The invocation of the relevant methods initialize, tools/list and tools/call can be seen in the history.

Now that you know the MCP Server implementation works and complies with the specification it is time to plug this into an AI Assistant which can operate as an MCP Client. One example is Claude Desktop.

🤖 Claude Desktop (with `mcp-remote`)

Claude is an AI assistant created by Anthropic. It is built to assist with a wide variety of tasks – from answering questions and helping with analysis to writing, coding, math, creative projects, and having conversations on a wide range of topics. Claude.ai refers to the online version of the AI chatbot, while Claude Desktop is a dedicated application for Windows and macOS that allows users to access Claude’s features directly on their computer, offering a more streamlined and efficient experience. Although still in beta mode…Claude Desktop can connect to remote MCP servers, allowing you to access tools and data hosted on the internet. This integration enhances Claude’s capabilities by enabling it to perform tasks using resources from various external services. However, it does have its limitations. For example, although the free version works with local MCP servers to use remote MCP servers directly requires an upgrade to a paid plan.

To work around that we’ll use mcp-remote which will act as a local MCP server proxying to your remote server.

Manage your list of MCP Servers in the Developer section of Claude Desktop Settings

The Edit Config button will open up a file explorer window with your claude_desktop_config.json file selected. Edit that file and add an entry for your MCP Server.

{
  "mcpServers": {
    "ORDS-handler-example": {
      "command": "npx",
      "args": [
	"mcp-remote",
	"https://c4tozyxuw8nq2ja-kgl2qyis29qcbmv2.adb.eu-frankfurt-1.oraclecloudapps.com/ords/ords_concert_app/mcp/server",
	"--header",
	"Authorization:${AUTH_HEADER}"
      ],
      "env": {
        "AUTH_HEADER": "Bearer 8kbzUqtgsDrCTNKB6Xb32w"
      }
    }
  }
}

Note that that you will have to specify your access token and restart Claude Desktop to pick up the change. In the above example the ORDS-handler-example is defined to execute the npx command to run the mcp-remote Node.js package locally. Additional command parameters include the URL for the remote MCP Server and an instruction to include an Authorization header. Note that the access token from earlier is used here.

Once Claude Desktop is restarted, let’s start asking questions. The AI Agent knows that it has an MCP Server available to it that has a tool for retrieving concert events.

At startup Claude Desktop got the list of tools available from the MCP Server

Note that it is best practice to not give an agent unsupervised access to your tools and resources so in the majority of cases, select “Allow once” so that each action is reviewed before executing.

So let’s ask Claude a question:
From the ORDS Sample Application concert data who are the most popular artists with sold out concerts?

Claude gets the live data and performs its analysis on it

And that’s it! You now can now chat with an AI Assistant to gain insight on your ORDS Sample Application concert events, venues and artists.

⚠️ Limitations and Next Steps

🔐 OAuth2 Support Limitations

Some MCP clients—such as Claude—do not fully support OAuth2 client credentials at the time of writing. This may require:

Temporarily disabling OAuth2 for local testing.
Creating a public ORDS endpoint for development use.

Neither of which are really viable options. Remember the security, security, security statement at the top!

ORDS does not currently provide support for the OAuth2 flow to list identity providers and that reduces the integration options. The approach mentioned in this article to have an access token that lasts longer than the default 1 hour is not practical in the long term. However, you may find an MCP client that works with one of the other OAuth2 flows.

🚫 Lack of Streamable HTTP and Notifications

One limitation of this ORDS mle/javascript MCP server implementation is the absence of streamable HTTP responses and real-time notifications.

Streamable HTTP: The MCP specification allows servers to return results progressively, which is useful for long-running operations or large responses. ORDS handlers currently return only complete responses, meaning agents must wait until all processing finishes before receiving data.
Notifications: MCP also supports server-initiated notifications to inform clients of changes (e.g., new events becoming available). At present, ORDS does not provide a mechanism for pushing such asynchronous messages, so clients must re-poll endpoints to detect updates.

While these limitations do not prevent basic MCP functionality, they reduce efficiency for agents expecting real-time updates or streamed results.

🔧 Future Enhancements

Here are some possible next steps to build on this example mle/javascript handler:

Add a tool for managing events
Implement dynamic prompt generation in prompts/list.
Add support for downloading resources.
Log incoming MCP requests in a table for auditing.
Move the logic into an MLE Module so that the JavaScript code is better structured.
Explore the use of different OAuth2 security flows which will work with you chosen MCP client.

🧠 Summary

With just a few lines of MLE JavaScript and an ORDS handler definition, you can turn your Oracle database into a lightweight MCP HTTP server.

This approach allows Oracle developers to publish tools, prompts, and data to modern AI tools like Claude—without deploying external services. Moreover, the data and database resources that are shared is limited to what is specifically coded for in your handler.

📚 Continue exploring: More MLE Articles, https://github.com/oracle/mcp and Model Context Protocol server for the Oracle Database