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.

CCNEWS table with content

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 HanderAutoREST Vector Search
Embedding generated in SQL (VECTOR_EMBEDDING)Embedding supplied by client
Custom handler requiredBuilt-in endpoint
SQL embedded in ORDSNo SQL required
URL parameter (:mysentence)JSON payload
No refinement on search criteriaClient can specify order, number of rows to return, specific columns to include in the response and additional filter criteria.
Primary Key Hyperlinks require extra codingAutomatic 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"
}

or

{
  "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

Understanding ORDS db.connectionType: A Practical Comparison of All Four Connection Models

Oracle REST Data Services (ORDS) supports four different ways to create database connection pools, each designed for a specific deployment model or infrastructure environment. While most people are familiar with the common Basic connection settings, the other options—TNS, Custom URL, and Database Tools Connection—offer powerful alternatives when you need more flexibility or cloud-native authentication.

This article provides a concise comparison of all four db.connectionType values, explains when each option makes sense, and lists the configuration settings required for each type.

Why db.connectionType Matters

Every ORDS deployment includes one or more database pools. The connection type determines:

  • how ORDS constructs the JDBC URL
  • where host/service metadata comes from
  • whether credentials are stored locally or remotely
  • what additional ORDS settings are required

Choosing the right connection type simplifies configuration, avoids hard-coding credentials, and ensures ORDS connects using the most appropriate mechanism for the environment.

The Four ORDS Connection Types

1. Basic — Direct Host/Port/Service Connections

This is the simplest and most commonly used configuration. ORDS constructs the JDBC URL automatically based on host, port, and service name.

Use this when:

  • the database is customer-managed (on-prem or IaaS)
  • you want simple, explicit connection parameters
  • you’re not using TNS descriptors or OCI integrations

Required Settings:

db.connectionType=basic
db.hostname=<host>
db.port=<port>
db.servicename=<service>
db.username=<db user>
db.password=<secret>

Note that as a secret, the db.password value is expected to be persisted in a wallet file locally and set using the ORDS command line interface.

2. TNS — Use tnsnames.ora

When you choose the tns connection type, ORDS reads the connect descriptor from a tnsnames.ora file. This allows you to use advanced Oracle Net features such as load balancing, failover, and connect-time routing.

Use this when:

  • your organisation standardises on Oracle Net
  • you need RAC/Service failover descriptors
  • TNS files already exist and are centrally managed

Required Settings:

db.connectionType=tns
db.tnsDirectory=<directory containing tnsnames.ora>
db.tnsAlias=<alias in tnsnames.ora>
db.username=<db user>
db.password=<secret>

Note that as a secret, the db.password value is expected to be persisted in a wallet file locally and set using the ORDS command line interface. Moreover, the directory containing the tnsnames.ora must be accessible to the ORDS runtime instance. This can be over a networked filesystem but must be readable by the ORDS process.

3. Custom URL — Full JDBC URL Control

The customurl connection type gives you complete control of the JDBC URL. ORDS simply uses whatever you provide. This is the most flexible option and the only one suitable for non-Oracle databases.

Use this when:

  • you are connecting to MySQL or another JDBC-compatible database
  • you need URL parameters not supported by Basic or TNS
  • you prefer to manage the full connection string manually

Required Settings:

db.connectionType=customurl
db.customURL=jdbc:oracle:thin:@//host:port/service
# or MySQL, PostgreSQL etc.
db.username=<db user>
db.password=<secret>

Useful for cases like:

db.customURL=jdbc:mysql://10.0.1.23/?sslMode=REQUIRED

Note that as, a secret the db.password value is expected to be persisted in a wallet file locally and set using the ORDS command line interface. ORDS only ships with the Oracle JDBC driver so if using a different JDBC driver the jar files that must be in the lib/ext directory.

4. Database Tools Connection — Cloud-Native Authentication via OCI

This option integrates ORDS with Oracle Cloud Infrastructure Database Tools, allowing ORDS to use an OCI-managed “Database Tools Connection” object. Credentials are stored in OCI Vault, not in ORDS configuration files. See https://docs.oracle.com/en-us/iaas/database-tools/home.htm for more information on this managed service.

This is the most secure and most cloud-native way to connect ORDS to an Autonomous Database or Oracle Base Database running in OCI.

Use this when:

  • ORDS is running inside OCI (Compute, Functions, OKE)
  • you want to avoid storing DB passwords in file systems
  • you want automatic credential rotation via Vault
  • you manage DB access through OCI IAM policies

Required Settings:

When your OCI compute instance is configured in a dynamic group which contains a policy granting it access to the dbtools service…

db.connectionType=databaseToolsConnection
db.databaseToolsConnection=<OCID of Database Tools Connection>

When your not running ORDS on an OCI compute instance but you have the OCI Command Line Interface installed…

db.connectionType=databaseToolsConnection
db.databaseToolsConnection=<OCID of Database Tools Connection>
db.authProvider=oci-profile
db.ociProfile=<only if using oci-profile>

Remember: No hostname, port, service name, or password required — OCI provides everything.

Quick Comparison Table

TypeURL SourceCredentials Stored Locally?Suitable ForNotes
basicHost/port/service nameYesOn-prem & simple setupsMost common
tnstnsnames.ora descriptorYesRAC, complex descriptorsCentralised Net configs
customurlFull JDBC URL provided by youYesNon-Oracle DBs, exotic configsMax flexibility
databaseToolsConnectionOCI Database Tools metadataNoCloud-native deploymentsBest security

Choosing the Right Connection Type

If you’re unsure which to pick, here’s a simple decision flow:

  • Running on OCI and want the best security?
    → Use databaseToolsConnection
  • Need RAC/SCAN/FAN/Load Balancing descriptors?
    → Use tns
  • Connecting to MySQL or need a very specific JDBC URL?
    → Use customurl
  • Standard on-prem or IaaS database with straightforward networking?
    → Use basic

Final Thoughts

ORDS has grown far beyond its early single-connection configuration model. Understanding the four db.connectionType settings helps you choose the best option for your environment—whether that’s a classic on-premises Oracle instance or a fully cloud-native, password-free deployment in OCI.

If you’re standardising ORDS across multiple environments, I recommend experimenting with each type in a test pool. You’ll quickly see which one aligns with your organisation’s approach to connectivity, configuration management, and security.

Find those unused Oracle Database Accounts (Easily!)

Managing Oracle database accounts is an essential part of database security and operational hygiene. One key task is identifying accounts that have not been used for extended periods, such as over 30 days. While queries against DBA_USERS or ALL_USERS provide basic metadata about database accounts, they do not reliably indicate actual usage, particularly for REST-enabled schemas. This is because Oracle REST Data Services (ORDS) and other middleware may connect to the database via a “connect through” mechanism, creating sessions on behalf of the schema without a traditional direct login.

For precise auditing of user activity, Oracle 26ai’s Unified Audit framework offers a reliable solution.

What is a Unified Audit Policy?

Unified Auditing consolidates Oracle’s auditing mechanisms into a single, consistent framework. It allows administrators to define audit policies that track database activity across sessions, users, and even system-level events.

A Unified Audit Policy is a set of rules that specifies which actions are audited. When enabled, it records events in the UNIFIED_AUDIT_TRAIL view, including logins, logouts, DML operations, and privilege usage. This provides a reliable, timestamped record of actual database activity.

Define The Policy

Oracle 26ai ships with a predefined Unified Audit Policy called ORA_LOGIN_LOGOUT. This policy is designed to track user logins and logouts. However, by default it only tracks failures unless your System Administrator has altered the policy definition. So lets verify your audit policy state.

To confirm that Unified Audit is enabled in your database run this query. It should return TRUE.

SELECT VALUE
FROM V$OPTION
WHERE PARAMETER = 'Unified Auditing';

Now check the status of the predefined ORA_LOGIN_LOGOUT policy…

SELECT * FROM audit_unified_enabled_policies where POLICY_NAME = 'ORA_LOGIN_LOGOUT';
The results could look like this indicating that only failed attempts are recorded.

If the predefined ORA_LOGIN_LOGOUT policy is not recording on SUCCESS then define a new policy to achieve that and activate it…

CREATE AUDIT POLICY ALL_LOGONS ACTIONS LOGON;

AUDIT POLICY ALL_LOGONS;

You can verify that policy definition and that it is enabled by running the following queries:

SELECT * FROM audit_unified_enabled_policies where POLICY_NAME = 'ALL_LOGONS';

select * from AUDIT_UNIFIED_POLICIES where policy_name = 'ALL_LOGONS';

You can verify that LOGON activity is now being recorded based on this policy by checking the UNIFIED_AUDIT_TRAIL:

SELECT * from UNIFIED_AUDIT_TRAIL where UNIFIED_AUDIT_POLICIES = 'ALL_LOGONS' order by EVENT_TIMESTAMP_UTC desc;

Refer to Oracle documentation for more details: Unified Audit Policies.

Identifying Inactive Users

Now that you are recording the LOGON activity you can review the audit trail view. To find database accounts that have not logged in for over 30 days, you can query the UNIFIED_AUDIT_TRAIL using the following SQL:

SELECT
    UAT.DBUSERNAME,
    MAX(UAT.EVENT_TIMESTAMP_UTC) AS LAST_LOGIN_UTC
FROM
    UNIFIED_AUDIT_TRAIL UAT,
    ALL_USERS           AU
WHERE
    UAT.ACTION_NAME = 'LOGON'
    AND UAT.DBUSERNAME = AU.USERNAME
GROUP BY
    UAT.DBUSERNAME
HAVING
    MAX(UAT.EVENT_TIMESTAMP_UTC) < SYSTIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '30' DAY
ORDER BY
    LAST_LOGIN_UTC DESC;

Explanation of the query:

  • DBUSERNAME identifies the database account.
  • EVENT_TIMESTAMP_UTC captures the precise UTC timestamp of the last login.
  • MAX(EVENT_TIMESTAMP_UTC) gives the most recent login per user.
  • The join on ALL_USERS ensures you only see database users that still exist.
  • HAVING MAX(EVENT_TIMESTAMP_UTC) < SYSTIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '30' DAY filters accounts that have been inactive for over 30 days.
  • ORDER BY LAST_LOGIN_UTC DESC lists the accounts from least to most recently active.

This approach is far superior to just using DBA_USERS.LAST_LOGIN or ALL_USERS because it captures all session activity, including sessions initiated via REST services like ORDS. Accounts that appear “inactive” in DBA_USERS may, in fact, be used regularly through REST-enabled access.

Why DBA_USERS or ALL_USERS Isn’t Enough

Traditional queries like:

SELECT username, last_login
FROM dba_users;

or

SELECT username
FROM all_users;

have limitations:

  1. No REST context – They only reflect direct database logins, missing sessions created via middleware using “connect through” mechanisms.
  2. Potentially outdated – DBA_USERS.LAST_LOGIN is only updated on direct logins.

Unified Auditing solves all these limitations by taking advantage of UNIFIED_AUDIT_TRAIL, recording every login event centrally, giving administrators a reliable view of database account activity.

Conclusion

By taking advantage of UNIFIED_AUDIT_TRAIL, administrators can:

  • Accurately determine which accounts are truly inactive.
  • Include REST-enabled schemas in their audits.
  • Improve security by identifying accounts that may no longer require access.

If it is enabled for SUCCESS then the ORA_LOGIN_LOGOUT Unified Audit Policy is the recommended approach for identifying inactive database accounts in Oracle 26ai. Otherwise, you will have to define a new policy as detailed in this article.

Enabling and managing the audit policies is a critical best practice for modern Oracle databases, particularly those supporting REST services through ORDS. It is important to also have procedures in place to archive old audit trail records so they do not fill disk space.

What audit policies for ORDS do you have in place for monitoring activity?

Faster Database Authentication with 23ai

Oracle REST Data Services (ORDS) 25.3.0 has just been released, and one of the highlights for developers is a significant performance improvement for Basic Authentication when paired with the latest Oracle 23ai database patch. In this article, we’ll show real-world “before and after” results from requests to an ORDS handler running in the Autonomous Database (ADB) Frankfurt region, demonstrating how ORDS 25.3.0 drastically reduces authentication response times.

Dramatic improvement on all metrics!
All values are milliseconds.

Note: It is recommended to use OAuth for authentication and authorisation rather than Basic Authentication where possible.

Background

Basic Authentication is widely used in ORDS for accessing secured REST endpoints. Note that, one is recommended to use OAuth2 over Basic Authentication for both performance and security reasons. See the ORDS Best Practices document. However, that is not always possible because for some clients their only authentication mechanism available is Basic Authentication. While simple to implement, verifying usernames and passwords can introduce latency, especially under load or in high-frequency request scenarios. Of course the biggest security issue is that one is sending username and password credentials for a database user over the network with every request. Even with HTTPS, it is best to avoid this practice.

For our tests, we used the events query handler from the ORDS sample application. This handler is part of the ORDS Sample Application ( also known as ORDS Concert App ), which you can explore here: ORDS Remix JWT Sample.

All tests were executed against an Autonomous Database instance in the Frankfurt region to ensure realistic production-like conditions.

Test Setup

We compared two configurations:

  • ORDS 25.2.3 (previous version)
  • ORDS 25.3.0 (latest release)
  • Oracle 23ai database with the most recent RU, which contains enhancements specifically for faster ORDS basic authentication. More specifically, that’s RDBMS 23.10 which has been available in Autonomous Database for about a week now.

The test scenario:

  • A k6 script making 100 sequential requests to the ORDS handler
  • 200ms pause between requests to avoid ADB rate limiting (all requests came from the same IP)
  • k6 executed from Dublin, with ORDS and the database hosted in Frankfurt, introducing a consistent network latency
  • The protected service is the ORDS Concert App events handler.

Note: The details of the k6 script and execution environment will be provided in a dedicated section below.

Before: ORDS 25.2.3 Performance

The statistics for ORDS 25.2.3, recorded a few days ago at the same time of day as the latest test, are as follows (all durations in milliseconds):

MetricDuration (ms)
Avg160.448
Min108.257
Med129.354
Max1118.475
P90183.042
P95204.627

Analysis:

  • Average response time was ~160ms
  • Maximum response exceeded 1 second, highlighting potential bottlenecks
  • P95 and P90 values show noticeable tail latency, which can affect user experience

After: ORDS 25.3.0 Performance

After upgrading to ORDS 25.3.0 and applying the latest Oracle 23ai patch, the same test yielded dramatically improved results:

MetricDuration (ms)
Avg59.195
Min51.607
Med54.811
Max273.553
P9065.565
P9568.708

Analysis:

  • Average response time reduced by ~63%
  • Minimum response time shows roughly a 52% improvement, but compared with the 63% average improvement, it suggests that the fastest possible request was already bounded mostly by network latency (Dublin → Frankfurt).
  • Maximum response reduced by ~76%
  • P90 and P95 values significantly lower, resulting in more consistent and predictable performance
  • The improvements are immediately noticeable for real-world API requests

Why the Improvement Happens

The performance gains are made possible by enhancements in Oracle 23ai that were introduced specifically for ORDS:

  • Faster verification of username and password
  • Tight integration as a trusted Oracle database tools application
  • Optimized authentication path within ORDS 25.3.0
The faster authentication for ORDS basic auth was introduced in RDBMS 23.10

In practice, this means that if basic authentication is your only option because of the client or integration tool used for consuming your ORDS REST services you will see a noticeable improvement in response times, even in high-volume scenarios.

One such example is the Oracle Database API for MongoDB where basic authentication is the only option with the MongoDB Wire protocol interface. Improving the connection time for customers using Oracle RDBMS to persist data for their MongoDB applications was the primary driver for this initiative with ORDS and 23ai.

OAuth Bearer Token Performance

The use of OAuth is still recommended over Basic Authentication because it is more secure. It was dramatically faster than Basic Authentication too but as you can see from these OAuth bearer token test runs for the same service there is almost parity on response times now.

MetricDuration (ms)
Avg54.966722 ( 5ms better )
Min38.9794 ( 12ms better )
Med45.7988 ( 9ms better )
Max234.4554 ( 39ms better )
P9074.56998 ( 9ms worse )
P9587.19764 ( 18ms worse )

Observations:

  • Apart from the P90 and P95 metrics the OAuth bearer token is still more performant
  • These tests just involve 1 single client making sequential requests and the performance profile with multiple concurrent requests should be significantly beter.
  • Consider using OAuth JWT Profile with an external identify provider for even faster response times.

k6 Script Details and Execution

Here is the sequential_100.k6 script that was used to record the response times and avoid the Autonomous Database request rate limiting on the OCI Free Tier.

import http from 'k6/http';
import { check, sleep } from 'k6';
import { Trend, Rate } from 'k6/metrics';
import encoding from 'k6/encoding';

// Test configuration
export let options = {
  vus: 1,              // single VU so requests are sequential
  iterations: 1,      // run default() once (we loop 100 times inside)
  thresholds: {
    // Require 100% of requests to be successful (status 200)
    'successful_requests': ['rate==1'],
    // Example response-time threshold (optional) - adjust to your needs
    'response_time_ms': ['p(95) < 500']
  },
};

// Request details. Change these for your environment.
const URL = 'https://mytenancy-my23ai.adb.eu-frankfurt-1.oraclecloudapps.com/ords/ords_concert_app/authuser/v1/events/';
const USER = 'ORDS_CONCERT_APP';
const PASS = 'MyConcertAppDatabaseUserPassword';

// Basic auth header
const AUTH_HEADER = 'Basic ' + encoding.b64encode(`${USER}:${PASS}`);

// Metrics
let responseTimeTrend = new Trend('response_time_ms');
let successRate = new Rate('successful_requests');

export default function () {
  for (let i = 0; i < 100; i++) {
    // Perform request with Authorization header
    let res = http.get(URL, {
      headers: {
        Authorization: AUTH_HEADER,
        'Accept': 'application/json'
      }
    });

    // Record timing (milliseconds)
    responseTimeTrend.add(res.timings.duration);

    // Assert HTTP 200 for each request and update success rate
    const ok = check(res, {
      'status is 200': (r) => r.status === 200
    });
    successRate.add(ok);

    // Log any failures to console for quick debugging
    if (!ok) {
      console.error(`Request #${i + 1} failed — status: ${res.status}, body: ${res.body ? res.body.slice(0,200) : 'empty'}`);
    }

    // Wait 200 ms between requests to respect rate limits
    sleep(0.2);
  }
}

Some details to call out:

At line 7 we define the test options which specify how many concurrent users ( 1 in this case ) and how many requests to send ( 100 in this case ). Also defined here are some minimum success criteria, also known as thresholds, such as all responses must have HTTP 200 status code and no response should take longer than 500 milliseconds.

At line 19 the details of URL as well as username & password are specified. You will change these to match your environment.

At line 27 custom metrics for response time and success rate are defined. These will be the key metrics that we’re interested in.

At line 33, inside a for loop which is executed 100 times the request is sent, thresholds checked, metrics recorded and then a wait for 200ms. The 200ms wait is to avoid rate limiting because all requests are coming from the same IP address. This 200ms wait is not included in the response metrics.

When executed ( before the ORDS 25.3.0 upgrade ) the response for test run looks like this.

When gathering the statistics I ran the sequential_100.k6 5 times and recorded the average of the various metrics. This was to smooth out any intermittent spikes in network or database activity.

A test run of the same k6 script after ORDS 25.3.0 was rolled out shows improvement with all metrics.

Conclusion

ORDS 25.3.0, when paired with Oracle 23ai and its latest patch, provides a remarkable improvement in Basic Authentication performance.

Key takeaways:

  • Average authentication response time dropped from ~160ms to ~59ms
  • Max response time and tail latency drastically reduced
  • Real-world API workloads benefit from more consistent and faster performance
  • If Basic Authentication is your only option you will see improvements but you really should be using OAuth2 where you can.

Developers should consider upgrading to ORDS 25.3.0 and applying the latest Oracle 23ai patch to take full advantage of these optimizations.

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…

  1. 🎯 The Objective
  2. 📍 Introduction
  3. 🔍 What Is MCP?
  4. 🚀 Why ORDS + MLE JavaScript?
  5. 🛠️ MCP Handler Implementation in ORDS
    1. 🧩 1. Setup – REST enable the database schema and grant MLE privileges
    2. 🧱 2. Defining the MCP Module and Template
    3. ✍️ 3. JavaScript MLE Handler Source
  6. 🔐 Securing the MCP Handler
  7. 🧪 Deploying and Testing the MCP Server
    1. 🛰️ Deploy to Oracle Autonomous Database
    2. 🧰 Test with Tools
      1. ✅ MCP Inspector
      2. 🤖 Claude Desktop (with mcp-remote)
  8. ⚠️ Limitations and Next Steps
    1. 🔐 OAuth2 Support Limitations
    2. 🚫 Lack of Streamable HTTP and Notifications
    3. 🔧 Future Enhancements
  9. 🧠 Summary

🎯 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