PatOrg Case Management MCP - Oracle Natural Language to SQL

A Model Context Protocol (MCP) server that converts natural language queries into Oracle SQL for the PatOrg case management system. This repository is Oracle-only and uses real schema documentation.

Overview

This MCP server enables Claude (or other AI assistants) to:

• Read detailed Oracle schema documentation
• Review query patterns and business glossary
• Generate safe Oracle SQL from natural language
• Validate queries before execution
• Execute read-only SELECT queries safely

Project Structure

patorg-case-mcp-oracle/
├── src/
│   ├── server.py          # MCP server definition (resources & tools)
│   ├── db.py              # Oracle connection and query execution
│   └── validators.py      # Query validation and safety checks
├── docs/
│   ├── schema.md          # Oracle schema documentation
│   ├── patterns.md        # Natural language → SQL examples
│   ├── glossary.md        # Business term definitions
│   ├── end-to-end-testing.md        # Testing guide
│   └── fastmcp-tools-explained.md   # MCP tools explanation
├── tests/
│   └── test_queries.py    # Validation & execution tests (pytest)
├── start_mcp_server.py    # MCP server wrapper (loads .env credentials)
├── toggle_mcp.ps1         # PowerShell script to enable/disable MCP
├── toggle_mcp.bat         # Windows batch wrapper for toggle script
├── .env.example           # Template for Oracle credentials
├── QUICKSTART.md          # Quick start guide
├── CLAUDE_DESKTOP_INTEGRATION.md   # Claude Desktop setup guide
├── COMPLETION_SUMMARY.md  # Project completion summary
├── claude_desktop_config.template.json
├── pyproject.toml
└── README.md

MCP Resources & Tools

Resources (read):

• case_schema → docs/schema.md
• query_patterns → docs/patterns.md
• business_glossary → docs/glossary.md
• db_summary → live Oracle summary

Tools (execute):

• validate_query
• execute_query

Safety Features

• SELECT-only enforcement
• Table whitelist for Oracle schema (AKTE, CPIDE/CPIDD/CPIDF, P7LIST*, HIST*, LOHI, ADRS*, DOVE*, DOKU)
• WHERE clause requirement on large tables
• Blocked keywords (DELETE/INSERT/UPDATE/etc.)
• SQL comment blocking
• Automatic row limiting with FETCH FIRST n ROWS ONLY

Quick Start

1. Install dependencies:

   pip install -e .
   

2. Configure Oracle credentials:

   cp .env.example .env
   # Edit .env with your Oracle credentials
   

3. Run tests:

   pytest tests/test_queries.py -v
   

4. Integrate with Claude Desktop: See CLAUDE_DESKTOP_INTEGRATION.md for complete setup instructions.

For detailed setup, see QUICKSTART.md.

Example Queries for Claude

Once integrated with Claude Desktop, you can ask questions in natural language:

Simple Queries:

• "Show me all case files in the AKTE table"
• "What case files did user RUH create?"
• "Show me the structure of the AKTE table"
• "How many rows are in the CPIDE table?"

Recent Activity:

• "Show me files modified in the last 30 days"
• "What files were created this week?"
• "Show me recent login activity from LOHI table"

Status and Priority:

• "Show me files with STATUSAKT not null"
• "Find main files where HAKTE = 1"
• "Show high priority files (PRIOANZ > 0)"

History and Changes:

• "Show history for file MK 118"
• "What are the most recent changes in HISTST01?"
• "Show document history from DOVE table"

Relationships and JOINs:

• "Show files with their party information from CPIDE"
• "Find cases and their addresses from ADRS"
• "Get files with document references"

Analysis:

• "Count files by user who created them"
• "Show status distribution in AKTE"
• "Which users are most active?"

Claude will:

1. Read the schema documentation (docs/schema.md)
2. Review query patterns (docs/patterns.md)
3. Generate appropriate Oracle SQL
4. Validate it using the validation tool
5. Execute it safely and return results

Troubleshooting

• Oracle connection parameters required → Check .env file has correct credentials.
• Query validation failed → Review docs/patterns.md for safe query patterns.
• Claude doesn't see the MCP → Restart Claude Desktop and verify config path.
• Import errors → Ensure package is installed: pip install -e .
• MCP server won't start → Test with: python start_mcp_server.py

For more help, see:

• docs/end-to-end-testing.md - Complete testing guide
• CLAUDE_DESKTOP_INTEGRATION.md - Integration troubleshooting

Utility Scripts

• start_mcp_server.py - Starts MCP server with .env credentials (use this with Claude Desktop)
• toggle_mcp.ps1 - Enable/disable MCP server in Claude Desktop config
• toggle_mcp.bat - Windows wrapper for toggle script (double-click to toggle)

Running Tests

# Run all tests
pytest tests/test_queries.py -v

# Run specific test
pytest tests/test_queries.py::TestQueryValidator::test_valid_select_queries -v

# Expected: 12 tests passed

References

• MCP Documentation
• FastMCP
• python-oracledb Documentation
• SQL Basics

Version: 1.0.0
Last Updated: February 2026
Status: Production-Ready Oracle-Only MCP Server

Key Features:

• ✅ Oracle-only (all SQLite/mock references removed)
• ✅ Proper Python packaging (pip install -e .)
• ✅ Full pytest test coverage (12 tests)
• ✅ Secure credential management (.env file)
• ✅ Claude Desktop integration ready
• ✅ Toggle scripts for easy enable/disable