# Oracle DB MCP Server — Quick Start & Usage

1. Start server manually (recommended)

- Activate venv (PowerShell):
  venv\Scripts\Activate
- Run server (keep terminal open):
  python -u server/main.py
- See server entry point: [`server/main.py`](server/main.py:1)

2. Letting Roo start the server (optional)

- Roo can start the process, but it must use the correct Python interpreter from your venv.
- Recommended: point Roo's command to the venv python, e.g.:
  "command": "D:\\...\\Oracle_mcp\\venv\\Scripts\\python.exe",
  "args": ["-u", "server\\main.py"]
- Your Roo config is in: [`.roo/mcp.json`](.roo/mcp.json:1)

2a. Registering this MCP server with Roo (main usage)

- Roo reads the MCP registry in `.roo/mcp.json`, so add the Oracle MCP declaration there before calling any tools.
- Example snippet matching the context7 style:
  {
  "mcpServers": {
  "oracle-db": {
  "type": "streamable-http",
  "url": "http://localhost:7087/mcp"
  }
  }
  }
- Point the `url` at whichever transport the server exposes (`streamable-http` is the default; use `stdio` if Roo should launch the process itself).
- The MCP server implements the standard `list_tools()` endpoint which advertises all available tools and their JSON schemas. Roo and other MCP clients call this automatically when the registry loads; you can also call it manually to inspect the tool registry.
  - Manual check (example):
    - curl: `curl http://localhost:7087/mcp/list_tools`
    - Example (trimmed) response:
      ```json
      [
      	{
      		"name": "ping",
      		"description": "Test database connectivity",
      		"inputSchema": { "type": "object", "properties": {}, "required": [] }
      	},
      	{
      		"name": "db.query",
      		"description": "Execute SQL query with parameter binding and safety limits",
      		"inputSchema": {
      			"type": "object",
      			"properties": {
      				"query": { "type": "string" },
      				"params": { "type": "object", "additionalProperties": true },
      				"max_rows": { "type": "integer" },
      				"timeout_s": { "type": "integer" }
      			},
      			"required": ["query"]
      		}
      	}
      ]
      ```
  - Implementation reference: [`server/mcp_server.py`](server/mcp_server.py:46)
  - Notes: The returned tool list is used by Roo to populate the client-side tool picker and to validate request payloads before calling tools.

3. Verify the server is running

- Run the simple integration test:
  python test_mcp_simple.py
- Run the PL/SQL test suite (requires DB access):
  python test_plsql_operations.py
- Test files: [`test_mcp_simple.py`](test_mcp_simple.py:1), [`test_plsql_operations.py`](test_plsql_operations.py:1)

4. How tools are exposed and how to call them

- The MCP server advertises available tools and their JSON schemas via the standard `list_tools()` call. Roo (or any MCP client) will call that automatically and show available tools.
- Tool names you can call (examples):
  - db.query
  - db.exec_plsql
  - db.create_or_replace_proc
  - db.call_proc
  - db.run_test
- Example request payloads (JSON):

  db.query
  {
  "query": "SELECT \* FROM DUAL",
  "params": null,
  "max_rows": 100,
  "timeout_s": 10
  }

  db.exec_plsql
  {
  "plsql_block": "BEGIN NULL; END;",
  "params": null,
  "timeout_s": 30
  }

  db.create_or_replace_proc
  {
  "procedure_name": "MY_PROC",
  "procedure_text": "CREATE OR REPLACE PROCEDURE MY_PROC AS BEGIN NULL; END MY_PROC;",
  "timeout_s": 60
  }

  db.call_proc
  {
  "procedure_name": "MY_PROC",
  "params": {"p_in": 1, "p_out": null},
  "timeout_s": 30
  }

5. Example: run the query you requested

- SQL: select \* from wuss where wuss_name like "Hotelid"
- Use a LIKE pattern and a named bind (recommended):
  {
  "query": "SELECT \* FROM wuss WHERE wuss_name LIKE :wname",
  "params": {"wname": "%Hotelid%"},
  "max_rows": 500,
  "timeout_s": 30
  }
- You can run this from Roo (it will call the registered `db.query` tool) or from a local script.
- Local quick-run example (runs using the project's Oracle code):
  python -c "import asyncio, json; from server.oracle.pool import OraclePool; from server.oracle.exec import OracleExecutor; print(asyncio.run(OracleExecutor(OraclePool()).query('SELECT \* FROM wuss WHERE wuss_name LIKE :wname', params={'wname':'%Hotelid%'})))"

6. Expected responses

- Each tool returns a JSON object with at least:
  - "status": "success" or "error"
  - other fields depend on the tool (e.g. columns, rows, output_values)
- See implementations for exact fields:
  - Query: [`server/oracle/exec.py`](server/oracle/exec.py:310)
  - PL/SQL: [`server/oracle/plsql.py`](server/oracle/plsql.py:1)

7. Common troubleshooting

- "MCP error -32000: Connection closed" — server process exited. Keep the server terminal open or ensure Roo launches the correct venv interpreter.
- If Roo should launch server automatically, set `"command"` to venv python or use a wrapper script (`run_server.bat`) that activates venv and starts the server.
- Inspect logs in the terminal where the server was started. The logging config is in [`util/log.py`](util/log.py:1).

8. Calling tools from a client

- Roo will call `list_tools()` and present the tools + schemas to you automatically.
- If you use a programmatic mcp client, call tools using the client API (example pseudo-code):
  from mcp.client import Client
  client = Client.open_stdio() # depends on client API
  res = client.call_tool("db.query", {"query":"SELECT 1 FROM DUAL"})
  print(res)

9. Helpful references

- MCP registration and tool handlers: [`server/mcp_server.py`](server/mcp_server.py:1)
- PL/SQL implementation and examples: [`server/oracle/plsql.py`](server/oracle/plsql.py:1)
- Connection pool: [`server/oracle/pool.py`](server/oracle/pool.py:1)

Next steps

- Tell me if you want:
  - a wrapper script `run_server.bat` that activates venv and starts the server (I can add it),
  - or a sample Roo config that explicitly points to venv Python so Roo will start the right interpreter automatically.
