Building Metis Part 2: Talking to Autonomous Database 26ai with MCP

07 May Building Metis Part 2: Talking to Autonomous Database 26ai with MCP

In the first post of this series, I introduced Metis, the AI CloudOps Co-pilot I have been building on OCI, and walked through the high-level architecture. In this post I want to go deep on the piece that I think is the most interesting: how I am using Autonomous Database 26ai and its MCP (Model Context Protocol) endpoint to let the AI co-pilot query my Strava data in natural language.

If you have not heard of MCP yet, it is an open protocol for connecting AI assistants to external data sources and tools. Anthropic released it back in late 2024, and Oracle baked an MCP server right into ADB 26ai. That means any ADB 26ai instance can act as an MCP server with very little setup. This was probably the feature that excited me the most in the 26ai release.

What Is MCP, In One Paragraph

Think of MCP as a standard way to expose “tools” (functions, queries, data sources) to an AI model. The AI model receives a list of available tools when it starts a conversation, decides which ones to call based on the user’s question, and gets structured responses back. No more writing custom REST endpoints for every piece of data, you describe a tool once, the AI figures out when to use it. JSON-RPC under the hood, over HTTP or Server-Sent Events.

Setting Up the MCP Endpoint on ADB

The MCP endpoint on ADB 26ai is just a URL. The shape is:

https://{adb-host}-{adb-name}.adb.{region}.oraclecloudapps.com/ords/{schema}/_/mcp

For my ADB the endpoint looks a little bit  like this:

https://axxxxxxxxxx-reneace.adb.ca-toronto-1.oraclecloudapps.com/ords/strava/_/mcp

To authenticate, I needed a bearer token. ADB 26ai has a dedicated token endpoint:

POST /adb/auth/v1/databases/{ADB-OCID}/token

You send your DB username and password (the same one you use to log in via SQL Developer Web), and you get back a JWT that you can include as Authorization: Bearer <token> in MCP requests. The token expires after about an hour, so I cache it in memory and refresh when needed.

Here is the relevant snippet from my Node.js client:

async function getBearerToken() {
  if (_tokenCache.token && Date.now() < _tokenCache.expiresAt) {
    return _tokenCache.token;
  }
  const username = process.env.ADB_USERNAME || "ADMIN";
  const password = process.env.ADB_PASSWORD;
  if (!password) throw new Error("ADB_PASSWORD not set");

  const url = `https://adb.${ADB_REGION}.oraclecloud.com/adb/auth/v1/databases/${ADB_OCID}/token`;
  const resp = await axios.post(url, {}, {
    auth: { username, password },
    timeout: 15000,
  });
  _tokenCache = {
    token: resp.data.token,
    expiresAt: Date.now() + (50 * 60 * 1000), // refresh after 50 min
  };
  return _tokenCache.token;
}

In my case the credentials come from OCI Vault at startup (more on that in part 4), so the process.env values are populated from a vault read, not from .env.

Defining MCP Tools as PL/SQL Functions

This is where it gets cool. On the ADB side, MCP tools are just PL/SQL functions or procedures that I register against an ORDS module. Here is a real example, a function I created to return the user’s recent activities:

CREATE OR REPLACE FUNCTION RECENT_ACTIVITIES (
  p_limit IN NUMBER DEFAULT 10
) RETURN CLOB AS
  v_result CLOB;
BEGIN
  SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
      'name'     VALUE name,
      'type'     VALUE activity_type,
      'date'     VALUE TO_CHAR(start_date, 'Mon DD, YYYY'),
      'distance_km' VALUE ROUND(distance/1000, 2),
      'time_min' VALUE ROUND(elapsed_time/60, 1),
      'calories' VALUE calories,
      'avg_hr'   VALUE average_heart_rate
    ) ORDER BY start_date DESC RETURNING CLOB
  )
  INTO v_result
  FROM (
    SELECT * FROM STRAVA_ACTIVITIES
    ORDER BY start_date DESC
    FETCH FIRST p_limit ROWS ONLY
  );
  RETURN v_result;
END;

I have similar functions for ACTIVITY_SUMMARY, PERSONAL_BESTS, MONTHLY_TRAINING, PERFORMANCE_TRENDS, and a generic RUN_QUERY that takes a SQL string. Each one is registered as an MCP tool in the ORDS configuration with a title and description. The AI sees the descriptions and picks the right tool for the user’s question.

Calling MCP from Node.js

MCP uses JSON-RPC over HTTP, with Server-Sent Events for streaming responses. Each call has a sequence:

1. POST /mcp with method: "initialize" — handshake, returns server capabilities
2. POST /mcp with method: "tools/list" — returns the list of available tools
3. POST /mcp with method: "tools/call" and the tool name + args — returns the result

The response is wrapped in MCP’s content format, so I parse it once more to get the actual JSON array of activities.

Natural Language to SQL

The other piece that MCP unlocked is letting the user ask freeform questions against the database. Here is how it works:

1. User types: “How many runs did I do in 2024?”
2. Metis sends the question to Gemini 2.5 Flash along with the schema of STRAVA_ACTIVITIES and a system prompt that says “You generate Oracle SQL. Return ONLY the SQL, no markdown.”
3. Gemini returns: SELECT COUNT(*) FROM STRAVA_ACTIVITIES WHERE activity_type = 'Run' AND EXTRACT(YEAR FROM start_date) = 2024
4. Metis calls mcpCall("tools/call", { name: "RUN_QUERY", arguments: { p_sql: "..." } })
5. The PL/SQL RUN_QUERY function uses DBMS_SQL to execute and return the result as JSON
6. The result comes back and gets rendered as a table or sentence in the chat

The whole round trip takes about 2-3 seconds. The AI does the SQL, ADB executes it, MCP brings it back. Nothing custom.

Closing Thoughts

MCP changed how I think about exposing data to AI applications. Before, I would have written half a dozen REST endpoints, validation, pagination, the works. Now I write a PL/SQL function, register it as a tool, and the AI figures out the rest. Combine that with ADB 26ai’s built-in MCP server and you have a really clean architecture for AI-driven data apps.

In the next post I will go through the OCI Functions side of the build, how I am pulling Strava activities into ADB on a schedule, deploying with Docker, and handling the network plumbing.