generate_sample_queries

Create sample SQL queries for database tables to facilitate data exploration and analysis in Oracle environments.

Instructions

Generate sample SQL queries for a given table to help with exploration

Input Schema

TableJSON Schema

Name	Required	Description	Default
`table_name`	Yes	Name of the table to generate queries for
`owner`	No	Schema owner (optional)

Implementation Reference

src/oracle_mcp_server/server.py:845-882 (handler)

The main handler function for the 'generate_sample_queries' tool within the call_tool method. It fetches table columns, generates sample SQL queries based on column types (select all, count, distinct values, stats, date ranges), and returns them as JSON.

elif name == "generate_sample_queries":
    table_name = arguments.get("table_name")
    owner = arguments.get("owner")

    columns = await self.inspector.get_table_columns(table_name, owner)

    # Generate sample queries
    table_ref = f"{owner}.{table_name}" if owner else table_name

    queries = [
        f"-- Basic select all\nSELECT * FROM {table_ref} WHERE ROWNUM <= 10;",
        f"-- Count total rows\nSELECT COUNT(*) FROM {table_ref};",
    ]

    # Add column-specific queries
    for col in columns[:5]:  # Limit to first 5 columns
        col_name = col["column_name"]

        if col["data_type"] in ["VARCHAR2", "CHAR", "CLOB"]:
            queries.append(
                f"-- Find distinct values for {col_name}\nSELECT DISTINCT {col_name} FROM {table_ref} WHERE {col_name} IS NOT NULL AND ROWNUM <= 20;"
            )
        elif col["data_type"] in ["NUMBER", "INTEGER"]:
            queries.append(
                f"-- Statistics for {col_name}\nSELECT MIN({col_name}), MAX({col_name}), AVG({col_name}) FROM {table_ref};"
            )
        elif col["data_type"] in ["DATE", "TIMESTAMP"]:
            queries.append(
                f"-- Date range for {col_name}\nSELECT MIN({col_name}), MAX({col_name}) FROM {table_ref};"
            )

    result = {"table_name": table_name, "sample_queries": queries}

    return [
        TextContent(
            type="text", text=json.dumps(result, indent=2, default=str)
        )
    ]

src/oracle_mcp_server/server.py:727-740 (schema)

Input schema definition for the 'generate_sample_queries' tool, specifying required 'table_name' and optional 'owner' parameters.

    "type": "object",
    "properties": {
        "table_name": {
            "type": "string",
            "description": "Name of the table to generate queries for",
        },
        "owner": {
            "type": "string",
            "description": "Schema owner (optional)",
            "default": None,
        },
    },
    "required": ["table_name"],
},

src/oracle_mcp_server/server.py:723-741 (registration)

Registration of the 'generate_sample_queries' tool in the list_tools handler, including name, description, and input schema.

Tool(
    name="generate_sample_queries",
    description="Generate sample SQL queries for a given table to help with exploration",
    inputSchema={
        "type": "object",
        "properties": {
            "table_name": {
                "type": "string",
                "description": "Name of the table to generate queries for",
            },
            "owner": {
                "type": "string",
                "description": "Schema owner (optional)",
                "default": None,
            },
        },
        "required": ["table_name"],
    },
),

Oracle MCP Server