ORDS MCP REPORTS AND CUSTOM SQL TOOLS

ORDS MCP can give an AI agent access to saved SQL or PL/SQL. You can publish that SQL as a report or as a custom SQL tool.

You can review the SQL before you publish it. When a user asks a question, the agent supplies values for the query inputs. ORDS runs the saved query, so the agent does not need to generate a new query for each request.

The complete demo configuration and setup script are available in the ORDS MCP reports and custom SQL tools GitHub repository.

The main difference between these two MCP options is how the agent finds and calls the saved SQL:

	Report	Custom SQL tool
What the agent sees	The built-in report tools: report_list, report_sql, and report_execute	One MCP tool for each definition
Who can define it	An ORDS admin, or a schema developer when schema reports are enabled	An ORDS admin
Useful when	You have many saved queries, or developers need to contribute queries	You want one named tool for one clear task

How the agent sees them

The user asks a question. The agent chooses the report tools or the named custom tool, then sends the input values to ORDS.

User asks a question

↓

AI agent

Report
report_list → report_execute

Custom SQL tool
find_high_earning_employees

↓

ORDS MCP database pool

With reports, the agent uses the same report tools for many saved queries. With custom SQL tools, each definition appears as a separate tool.

Who can add them?

An ORDS administrator manages the ORDS instance. They can access the ORDS configuration directory, manage ORDS pools, and define reports and custom SQL tools.

An ORDS developer usually cannot access the ORDS configuration directory. They work with the database schema and the MCP pools provided by the ORDS administrator. They can add a report in the database schema if the administrator enables schema reports for that pool.

In the examples below, the pool is called sales-admin, and the table is PDBDBA.EMPLOYEES. The GitHub repository contains the example configuration files and SQL setup script.

Example 1: a report

This report finds employees in a department. The agent supplies the department name:

SELECT employee_id, first_name, last_name, email,
       department, job_title, salary, hire_date
FROM PDBDBA.EMPLOYEES
WHERE UPPER(TRIM(department)) = UPPER(TRIM(:department_name))
ORDER BY last_name, first_name

Here is the complete report definition. It is stored at:

<ords-config>/databases/sales-admin/mcp_reports/employee-directory-by-department.json

{
  "id": "demo-employee-directory-by-department-pool",
  "displayName": "Employee Directory by Department",
  "description": "Lists employees in a selected department for business users.",
  "purpose": "Answer employee-directory questions with a controlled parameterized query.",
  "instructions": "Provide the department name as text. Matching is case-insensitive and ignores leading or trailing spaces.",
  "source": "SELECT employee_id, first_name, last_name, email, department, job_title, salary, hire_date FROM PDBDBA.EMPLOYEES WHERE UPPER(TRIM(department)) = UPPER(TRIM(:department_name)) ORDER BY last_name, first_name",
  "variables": [
    {
      "name": "department_name",
      "type": "VARCHAR2",
      "description": "Department name to search for, such as Sales or Finance."
    }
  ],
  "columns": [
    {"name": "employee_id", "type": "INTEGER", "description": "Employee identifier."},
    {"name": "first_name", "type": "VARCHAR2", "description": "Employee first name."},
    {"name": "last_name", "type": "VARCHAR2", "description": "Employee last name."},
    {"name": "email", "type": "VARCHAR2", "description": "Employee email address."},
    {"name": "department", "type": "VARCHAR2", "description": "Department name."},
    {"name": "job_title", "type": "VARCHAR2", "description": "Employee job title."},
    {"name": "salary", "type": "NUMBER", "description": "Employee salary."},
    {"name": "hire_date", "type": "TIMESTAMP", "description": "Employee hire date."}
  ],
  "access": {
    "list": {"scope": "pools:read"},
    "sql": {"scope": "pools:read"},
    "execute": {"scope": "pools:read"}
  }
}

You can also view the employee report JSON on GitHub.

A few fields to notice:

• source is the SQL that ORDS runs.
• variables describes the input bind value used in the SQL. Here, :department_name matches the variable named department_name.
• columns describes the columns returned by the query.
• access sets who can list, view, and run this report.

A user can ask, “Show me employees in Sales.” The agent finds this report and supplies Sales for department_name. ORDS runs the saved query with that value.

Where reports are stored

An ORDS administrator can store a report in either of these locations:

• For one pool: <ords-config>/databases/<pool-name>/mcp_reports/<report-file>.json
• To share across pools: <ords-config>/global/mcp_reports/<report-file>.json. The report definition controls which pools can use it. See Oracle’s report definition reference.

An ORDS developer can store a report in the schema’s DBTOOLS$MCP_REPORTS table. The table must exist, and the ORDS administrator must enable schema reports for the pool. The setup script on GitHub creates the table and inserts a sample report.

Example 2: a custom SQL tool

This tool finds employees whose salary is above a value supplied by the agent. The complete definition is:

{
  "id": "demo-find-high-earning-employees",
  "displayName": "find_high_earning_employees",
  "databases": ["sales-admin"],
  "description": "Finds employees whose salary is greater than or equal to a supplied minimum salary. Read-only custom SQL tool.",
  "source": "SELECT employee_id, first_name, last_name, email, department, job_title, salary, hire_date FROM PDBDBA.EMPLOYEES WHERE salary >= :minimum_salary ORDER BY salary DESC, last_name, first_name",
  "variables": [
    {
      "name": "minimum_salary",
      "type": "NUMBER",
      "description": "Minimum salary threshold."
    }
  ],
  "columns": [
    {"name": "employee_id", "type": "INTEGER", "description": "Employee identifier."},
    {"name": "first_name", "type": "VARCHAR2", "description": "Employee first name."},
    {"name": "last_name", "type": "VARCHAR2", "description": "Employee last name."},
    {"name": "email", "type": "VARCHAR2", "description": "Employee email address."},
    {"name": "department", "type": "VARCHAR2", "description": "Department name."},
    {"name": "job_title", "type": "VARCHAR2", "description": "Employee job title."},
    {"name": "salary", "type": "NUMBER", "description": "Employee salary."},
    {"name": "hire_date", "type": "TIMESTAMP", "description": "Employee hire date."}
  ],
  "annotations": {"readOnly": true, "destructive": false},
  "access": {"scope": "pools:read"}
}

This file goes in the ORDS configuration’s global/mcp_tools directory. ORDS publishes it to the agent under the name find_high_earning_employees.

You can view the custom SQL tool JSON on GitHub.

A user can ask, “Find employees earning at least 10000.” The agent calls that named tool and supplies 10000 as minimum_salary.

The key fields work much like the report fields: source is the saved SQL, variables describes its input, and columns describes its output. The annotations field tells the MCP client that the tool is read-only and non-destructive.

Which one should I choose?

Choose a report when you have many approved queries, want to keep the number of tools the agent sees small, or want schema developers to add reports.

Choose a custom SQL tool when you want a named MCP action for a specific task, such as finding high-earning employees. Custom SQL tool definitions are managed by an ORDS administrator.

Both still use the authorization for the MCP database pool. Reports can also set separate access rules for listing, viewing SQL, and execution.

More fields and options

This tutorial explains the fields used in its two examples. It does not list every supported field. See Oracle’s reference pages for the full set of options:

• Define a Report
• Create a Custom SQL Tool Definition

Browse the complete ORDS MCP demo repository on GitHub.