Custom Report Tools

This section describes how to define and use predefined, parameterized SQL reports with the ORDS MCP server. It covers the report tools, definition format and supported properties, report resolution, and authorization.

Topics:

About Custom Report Tools

ORDS MCP SQL reports are predefined, parameter-driven SQL or PL/SQL statements that ORDS administrators or database developers publish as report definitions. Reports provide a consistent and governed way to run approved queries through an MCP client.

Unlike ad hoc SQL execution with sql_run, a report executes only its saved SQL or PL/SQL source. Caller-provided values are bound to the report’s declared variables if any. A report can also include descriptive metadata, input variable information, and expected output-column information to help an MCP client select and use it correctly.

ORDS makes reports available through three fixed MCP tools:

report_list: Lists predefined reports visible for the selected authorized MCP database.
report_sql: Returns the saved SQL or PL/SQL source for an authorized report without running it.
report_execute: Runs an authorized predefined report.

ORDS makes reports available through three fixed MCP tools:

report_list: Lists predefined reports visible for the selected authorized MCP database.
report_sql: Returns the saved SQL or PL/SQL source for an authorized report without running it.
report_execute: Executes an authorized predefined report.

Define a Report

A report is defined in a JSON document that can contain the following properties:

Property	Required	Type	Description
`id`	Yes	String	Specifies a stable report identifier. It must be nonblank, match `[A-Za-z0-9_.-]+`, and must be no longer than 128 characters.
`displayName`	Yes	String	Specifies the name displayed to the MCP client. Value must be nonblank.
`description`	No	String	Specifies a short description of the report result.
`purpose`	No	String	Specifies Specifies when to use the report and its intended purpose.
`instructions`	No	String	Specifies how to provide inputs and interpret the result.
`source`	Yes	String	Trusted SQL or PL/SQL source.
`variables`	No	Array of objects	Declared report inputs and SQL bind variables. See Declare Report Variables.
`columns`	No	Array of objects	Expected output-column metadata. It does not override the actual result.
`databases`	No	Array of strings	Specifies the database names that the global report can run against.
`access`	No	Object	Specifies separate authorization rules for listing, viewing source, and executing a report.

The optional access object controls authorization for each report operation. It can contain these sub-objects:

Property	Controls access to
`list`	Whether the report appears in `report_list`.
`sql`	Whether the saved SQL or PL/SQL source can be viewed through `report_sql`.
`execute`	Whether the report can be run through `report_execute`.

Each sub-object can contain either a scope or a role, depending on the global MCP authorization mode. For example, "list": { "scope": "mcp:reports:list" } requires the user to have that scope before the report appears in report_list.

These rules are independent. A user might be allowed to list a report without being allowed to view its source or run it. If a rule is not defined, the operation uses the authorization of the selected database pool. For every defined rule, the user must satisfy both the normal database-pool authorization and the report-specific rule.

The following example defines a report with one input variable:

{
  "id": "search_employees",
  "displayName": "Search Employees",
  "description": "Returns employees for a department.",
  "purpose": "Use this report to retrieve employees belonging to one department.",
  "instructions": "Provide the department name as the required input parameter.",
  "source": "SELECT employee_id, first_name, last_name FROM employees WHERE department = :department ORDER BY last_name, first_name",
  "variables": [
    {
      "name": "department",
      "type": "VARCHAR2",
      "description": "Department name."
    }
  ],
  "columns": [
    {
      "name": "employee_id",
      "type": "NUMBER",
      "description": "Employee identifier."
    },
    {
      "name": "first_name",
      "type": "VARCHAR2",
      "description": "Employee first name."
    }
  ],
  "databases": ["sales"],
  "access": {
    "list": {
      "scope": "mcp:reports:list"
    },
    "sql": {
      "scope": "mcp:reports:sql"
    },
    "execute": {
      "scope": "mcp:reports:execute"
    }
  }
}

Declare Report Variables

Each entry in variables must include:

name: The variable name
type: The Oracle data type
description: A description of the value

When variables are declared, the client supplies them in the variables array of report_execute. Each array entry contains the variable name and its value.

The variable names must match the bind variables used in the report source. Matching is case-insensitive. You can use the same bind variable more than once. Bind variables inside string literals, quoted identifiers, or SQL comments are ignored.

The following Oracle types are recognized:

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 all other supported types as JSON strings. A DATE value uses the JSON Schema date format and must be supplied as an ISO 8601 calendar date in YYYY-MM-DD format. A TIMESTAMP value uses the date-time format. Supply BLOB values as Base64-encoded strings using the JSON Schema byte format. ORDS decodes each value to binary data before creating the database bind.

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.

ORDS rejects missing, unknown, duplicate, or unconvertible variable values before executing the report. A report with no declared variables can be called without the variables property.

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

Store Report Definitions

After defining a report, store it in one of the supported sources listed in the following sections.

Pool Report Definitions

Store a report for one pool under:

/databases/<pool>/mcp_reports/<report-file>.json

The report belongs to the pool whose directory contains the file. Its databases property does not change that association.

Global Report Definitions

Store reports that can be shared by multiple MCP-enabled pools under:

/global/mcp_reports/<report-file>.json

For a global report, omit databases or leave it empty to make the report available on any MCP-enabled database the caller is authorized to access. A nonempty list limits the report to the specified database names.

Note: The file name is not the report ID. The id property in the document is authoritative.

Schema Report Definitions

ORDS developers can store reports in the DBTOOLS$MCP_REPORTS table. The table must contain a report_json JSON column containing the complete report document. ORDS reads these reports through the connection pool associated with the selected MCP database.

Database-backed reports are disabled by default. ORDS administrators can enable them through the feature.mcp.schemaReports setting:

ords --config /path/to/conf config --db-pool sales set feature.mcp.schemaReports true

For example, create the table as the database user that owns the report definitions:

CREATE TABLE DBTOOLS$MCP_REPORTS (
    report_json JSON NOT NULL,
    id          VARCHAR2(255)
                    GENERATED ALWAYS AS (
                        JSON_VALUE(
                            report_json,
                            '$.id' RETURNING VARCHAR2(255) ERROR ON ERROR
                        )
                    ) VIRTUAL,
    CONSTRAINT dbtools_mcp_reports_pk PRIMARY KEY (id)
);

The following example inserts a database-backed report definition:

INSERT INTO DBTOOLS$MCP_REPORTS (report_json)
VALUES (
    JSON_OBJECT(
        'id'           VALUE 'executive-headcount-by-department-db',
        'displayName'  VALUE 'Executive Headcount by Department',
        'description'  VALUE 'Executive summary of employee headcount by department.',
        'purpose'      VALUE 'Show workforce distribution across departments.',
        'instructions' VALUE 'Use this report for a high-level organizational overview.',
        'source' VALUE q'[
            SELECT department,
                   COUNT(*) AS employee_count
            FROM EMPLOYEES
            GROUP BY department
            ORDER BY employee_count DESC, department
        ]',
        'columns' VALUE JSON_ARRAY(
            JSON_OBJECT(
                'name'        VALUE 'department',
                'type'        VALUE 'string',
                'description' VALUE 'Department name.'
            ),
            JSON_OBJECT(
                'name'        VALUE 'employee_count',
                'type'        VALUE 'number',
                'description' VALUE 'Number of employees in the department.'
            )
        )
        RETURNING JSON
    )
);
COMMIT;

Understand Report Resolution

When a client requests a report for a database, ORDS checks the supported sources in the following order:

pool configuration > global configuration > schema table

When the same id appears in multiple sources, pool definitions take precedence over global definitions, which take precedence over schema-table definitions. If the ID exists in a higher-priority source, ORDS ignores lower-priority definitions with that ID—even if the higher-priority definition is invalid, duplicated, inapplicable, or unauthorized for the requested operation.

Reports with different IDs from applicable sources are combined. Invalid definitions and duplicate definitions are rejected independently so that unrelated valid reports remain available.

Use Reports Through MCP

The normal report workflow is:

Call report_list with the target database to discover visible reports.
Use the returned reportId with report_sql when you need to inspect the saved source and have permission to view it.
Use report_execute with the reportId and any required variable values.

To retrieve the reports available for the sales database, provide:

{
  "database": "sales"
}

The list includes the report ID, display name, description, purpose, instructions, declared variables, and output-column metadata when those properties are defined. It does not include SQL source or access rules.

To view a saved source of the report, provide:

{
  "database": "sales",
  "reportId": "search_employees"
}

To run the report, provide its declared variables as an array of objects with name and value properties:

{
  "database": "sales",
  "reportId": "search_employees",
  "variables": [
    {
      "name": "department",
      "value": "Sales"
    }
  ]
}

The report result contains report metadata and a CSV result set. The first row contains column headers.

Parent topic: Using ORDS Model Context Protocol (MCP)