CLAUDE.md

This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.

Project Overview

This is an Oracle database metadata extraction tool that serves as an MCP (Model Context Protocol) server. It provides read-only access to Oracle database schema information through both CLI and server interfaces.

Commands

Running the Tool

# Run in CLI mode for specific operations
uv run oracle_inspector_server.py <operation> [--args]

# Run in MCP server mode
uv run oracle_inspector_server.py --server

# Run with MCP dev tools for debugging
uv run mcp dev oracle_inspector_server.py

# Kill MCP server processes (uses ports 6277 and 6274)
./bin/kill_mcp.sh

Common Operations

# List all available operations
uv run oracle_inspector_server.py list_operations

# Get tables for a schema
uv run oracle_inspector_server.py get_tables_for_schema --schema_name MYSCHEMA

# Get column information
uv run oracle_inspector_server.py get_columns_for_tables --table_list TABLE1 TABLE2

# Execute custom query (use DBA_* tables, not ALL_*)
uv run oracle_inspector_server.py execute_custom_query --query "SELECT * FROM dba_tables WHERE owner = 'MYSCHEMA'"

Architecture

Project Structure

• oracle_inspector_server.py: Main server file with MCP endpoint definitions and CLI interface
• oracle_inspector_util.py: Database connection utilities and query execution (supports both oracledb and cx_Oracle)
• oracle_inspector_logging.py: Centralized logging configuration (Note: has duplicate setup that needs consolidation)
• specs/: Project specifications and implementation roadmap

Key Design Patterns

1. Dual Interface: All operations work in both CLI and MCP server modes (auto-detects TTY environment)
2. Type Safety: Uses Pydantic Field for parameter validation and type hints throughout
3. Read-Only: All database operations are read-only for safety
4. Excluded Schemas: System schemas (SYS, SYSTEM, etc.) are automatically excluded from results
5. Connection Strategy: Creates new connection for each operation (no pooling by design)
6. Error Handling: Returns structured error responses with enough info for calling LLM to fix queries

Design Patterns Used

• Facade Pattern: oracle_inspector_util.py provides simplified interface to Oracle operations
• Adapter Pattern: Bridges between CLI arguments and MCP protocol
• Command Pattern: Each operation is a self-contained command
• Decorator Pattern: @mcp.tool() decorator for MCP registration
• Factory Pattern: Connection creation abstracted in utility module

MCP Integration

• Uses FastMCP framework for server implementation
• Each operation is exposed as an MCP tool with typed parameters
• Environment variables configure database connection (ORACLE_USER, ORACLE_PASS, ORACLE_DSN)
• Balances strongly typed parameters against context overload for AI consumers

Implementation Guidelines

Adding New Operations

1. Define operation in oracle_inspector_server.py with @mcp.tool() decorator
2. Use Pydantic Field for all parameters with descriptions
3. Implement corresponding CLI argument parsing
4. Follow existing patterns for error handling and logging
5. Update the operations list in README.md

Code Conventions

• All functions must have type hints
• Use descriptive docstrings for operations
• Log operations at appropriate levels (info for normal flow, error for exceptions)
• Return structured dictionaries that serialize cleanly to JSON
• Handle Oracle-specific types (like oracledb.DB_TYPE_CURSOR) appropriately
• SQL Query Style: Use uppercase for SQL keywords, clear indentation, parameterized queries
• Use DBA_* tables instead of ALL_* data dictionary tables for broader visibility
• Oracle data types must be converted to JSON-serializable formats

Cursor Rules Integration

• Specifications are in ./specs/ directory
• Master specification: ./specs/project.md
• Mini-specs: ./specs/<number>-<subject>.md (numbers increase by 10)
• Don't implement specs unless explicitly requested

Known Issues and Priority Tasks

Urgent Priority

• Consolidate logging setup between server and utility files (duplicate initialization)

High Priority Operations to Add

• get_indexes: Index metadata extraction
• get_view_definition: View SQL and comments
• get_all_constraints: Comprehensive constraint information

Medium Priority Operations

• get_sequences: Sequence information
• get_triggers: Trigger metadata
• get_table_dependencies: Dependency analysis

Performance Considerations

• No pagination for large result sets (needs implementation)
• Connection overhead per operation (pooling explicitly not implemented)
• No result caching mechanism

Environment Setup

Required Environment Variables

• ORACLE_USER: Database username
• ORACLE_PASS: Database password
• ORACLE_DSN: Database connection string (TNS name or Easy Connect format)

Optional Environment Variables

• ORACLE_HOME: Oracle client directory (if using thick mode)
• LD_LIBRARY_PATH: Oracle client library path (if using thick mode)
• ORACLE_DEFAULT_SCHEMA: Default schema to use when operations don't specify a schema (e.g., "MYSCHEMA")
  • When set, this schema is used as a fallback for all operations with a schema_name parameter
  • Also sets the session's CURRENT_SCHEMA via ALTER SESSION on connection
• ORACLE_READONLY: Set to "true" to enforce read-only transactions at the database level
  • Executes SET TRANSACTION READ ONLY on each connection
  • Blocks DML only (INSERT, UPDATE, DELETE) - does NOT block DDL (DROP, TRUNCATE, CREATE, ALTER)
  • Provides partial protection beyond the tool's read-only design
  • Important when ORACLE_ALLOW_CUSTOM_SQL=true to prevent DML operations via custom queries
  • For full protection, use a database user with SELECT-only privileges
• ORACLE_ALLOW_CUSTOM_SQL: Set to "true" to enable the execute_custom_query operation
  • DISABLED BY DEFAULT for security
  • When disabled, the execute_custom_query tool is completely hidden from MCP clients
  • When enabled, executes queries without restrictions
  • All custom queries logged at WARNING level for audit trail
  • Security note: ORACLE_READONLY=true only blocks DML, not DDL (DROP, TRUNCATE, etc.)
  • Best practice: Use a database user with SELECT-only privileges when enabling custom SQL
  • Only enable in trusted environments where unrestricted SQL access is acceptable

Database Requirements:

• Read-only access to Oracle data dictionary views (DBA_* views)
• Privileges to query schema metadata
• Connection to Oracle database instance

Development Workflow

1. Use uv for dependency management (not pip directly)
2. Python 3.13+ required
3. Testing approach: Manual testing via CLI mode before deploying to MCP server
4. Check logs for debugging (uses Python logging module)
5. Dual library support: Automatically tries oracledb first, falls back to cx_Oracle

Recent Additions

• get_package_metadata: Package metadata including members (procedures/functions) and their arguments, optionally with source code
• get_source_code: Retrieve source code for any database object (procedure, function, package, trigger, etc.)
• include_comments parameter: Added to get_tables_for_schema for table comments
• Automatic TTY detection for mode selection (CLI vs server)