Custom SQL Tools

This section describes how to define and use custom SQL tools with the ORDS MCP server. It covers tool definitions, database selection, input variables, storage, authorization, and how MCP clients discover and run tools.

Topics:

About Custom SQL Tools

Custom SQL tools are MCP tools that ORDS administrators define to make approved SQL or PL/SQL operations available to MCP clients.

An MCP client discovers the tools that the caller is allowed to use with the standard tools/list method. The client then calls a selected tool with its input values. The client cannot create, change, or save custom SQL tool definitions through MCP. MCP clients use the standard tools/list method to discover tools the caller is authorized to use. The client then calls a selected tool with its input values. They cannot create, modify, or save custom SQL tool definitions through MCP.

Create a Custom SQL Tool Definition

A custom SQL tool is defined by a JSON document. The document can contain the following properties:

Property Required Type Description
id Yes String Specifies a stable definition identifier. It must be nonblank, match [A-Za-z0-9_.-]+, and be no longer than 128 characters.
displayName Yes String Specifies the name exposed to the MCP client and used when the tool is called. It must be a nonblank value and follow the same identifier rules as id.
description No String Specifies the description returned to the MCP client.
source Yes String Specifies the trusted SQL or PL/SQL source controlled by an ORDS administrator.
variables No Array of objects specifies the input values that the tool binds to the named SQL bind variables.
databases No Array of strings Specifies the ORDS MCP database names where the tool can run.
access No Object Specifies the additional scope or role-based authorization rule.
annotations No Object Specifies the Metadata that tells an MCP client whether the operation is read-only or destructive.

The following example defines a tool that looks up one customer:

{
  "id": "customer_lookup",
  "displayName": "customer_lookup",
  "description": "Looks up a customer by customer identifier.",
  "source": "SELECT customer_id, customer_name, status FROM customers WHERE customer_id = :customer_id",
  "variables": [
    {
      "name": "customer_id",
      "type": "NUMBER",
      "description": "Customer identifier."
    }
  ],
  "databases": ["sales"],
  "access": {
    "scope": "mcp:customer:read"
  },
  "annotations": {
    "readOnly": true,
    "destructive": false
  }
}

The id identifies the definition. The MCP tool name is displayName.

Note: Unknown JSON properties are ignored for forward compatibility. Duplicate id or displayName values are rejected, and invalid definitions do not prevent unrelated valid definitions from loading.

Choose the Database for Execution

The optional databases property controls which databases the tool can run against:

The user must already be authorized to access the selected database. Listing a database in databases does not grant access to it.

Declare Input Variables

Each entry in variables must include:

When variables are declared, ORDS includes these inputs in a nested variables object in the generated MCP schema.

Variable names must match the bind variables in the source, matching is case-insensitive. You can reference the same bind variable more than once. ORDS ignores bind-variable references inside string literals, quoted identifiers, and SQL comments.

ORDS recognizes the following Oracle data types:

BINARY_DOUBLE, BINARY_FLOAT, BLOB, CHAR, CLOB, DATE, FLOAT,
INTEGER, JSON, NCHAR, NCLOB, NUMBER, NVARCHAR2, RAW,
SMALLINT, TIMESTAMP, TIMESTAMP_WITH_LOCAL_TZ, TIMESTAMP_WITH_TZ,
VARCHAR2, XMLTYPE

ORDS represents numeric types as JSON numbers and other supported types as JSON strings. Supply DATE values as ISO 8601 calendar dates in YYYY-MM-DD format, which matches the JSON Schema date format. Use the date-time format for TIMESTAMP values. Supply BLOB values as Base64-encoded strings using the JSON Schema byte format. ORDS decodes them to binary data before assigning them to the database bind variable.

ORDS treats any non-empty type name that is not in the supported list as a custom Oracle type. It represents the value as a JSON string and uses the configured type name to resolve the type in the database.

For example, the customer_id value is supplied as follows:

{
  "variables": {
    "customer_id": 42
  }
}

Note: The names database and current_user are reserved. ORDS supplies current_user, and it cannot be declared or overridden.

Store the Definition

Store each custom SQL tool as a separate JSON file under the global ORDS configuration directory:

/global/mcp_tools/<tool-file>.json

Note: The file name is not the tool identity. The id and displayName properties in the document determine the identity.

Definitions are loaded into the MCP tool catalog. If a file is malformed or contains an invalid definition, ORDS skips that file and continues loading other definitions.

Control Authorization and Tool Metadata

The optional access object adds an authorization requirement for the tool. Configure one of these properties based on the global MCP authorization mode:

The caller must satisfy both the normal MCP database-pool authorization and the custom tool’s access rule. A caller who does not satisfy the rule does not see the tool in tools/list.

If access object is omitted, the custom SQL tool inherits pool-level MCP authorization.

The optional annotations object can contain the following Boolean properties:

If either property is omitted, ORDS uses readOnly: false and destructive: true. ORDS returns the effective values to the MCP client as readOnlyHint and destructiveHint. These values guide client and model tool selection.

Discover and Run a Custom SQL Tool

ORDS includes a custom SQL tool in tools/list only if the caller is authorized to use it and can access at least one database where it can run. The generated input schema contains the tool’s declared variables and, when applicable, the available database choices.

For each tool call, ORDS revalidates the tool definition, database selection, database-pool authorization, and tool-specific authorization. It then runs only the saved SQL source with the selected MCP database pool’s privileges.

Custom SQL tool calls use the existing ORDS MCP audit path and database session context. Variable values are not logged by default.

Parent topic: Using ORDS Model Context Protocol (MCP)