# Oracle DB MCP Tool - Technical Context

## Stack and Dependencies

### Python Runtime

- **Path**: D:\Dropbox\dev\python\python.exe
- **Architecture**: 32-bit (required for Oracle client compatibility)
- **Version**: Python 3.x (compatible with cx_Oracle 8.3.0)

### Core Dependencies

- **mcp**: MCP protocol implementation for Python
- **cx_Oracle==8.3.0**: Oracle Database driver (version locked for 32-bit compatibility)
- **attrs**: Used by MCP library for data structures

### Requirements.txt

```
mcp
cx_Oracle==8.3.0
attrs
```

## Oracle Client Requirements

### Oracle Instant Client

- **Architecture**: 32-bit
- **Purpose**: Database connectivity libraries
- **Installation**: Download and add to PATH
- **Version**: Compatible with cx_Oracle 8.3.0

## Configuration

### Environment Variables

```bash
# Database Connection
ORACLE_USER=v8live
ORACLE_PASS=live
ORACLE_DSN=10.0.0.18:1521/v8

# Safety Limits
MCP_MAX_ROWS=500
MCP_MAX_TEXT_SIZE=5000
MCP_TIMEOUT_S=30
```

### VS Code MCP Configuration

File: `.vscode/mcp.json`

```json
{
	"servers": {
		"oracle-db": {
			"command": "D:\\Dropbox\\dev\\python\\python.exe",
			"args": ["-u", "server\\main.py"],
			"env": {
				"ORACLE_DSN": "10.0.0.18:1521/v8",
				"ORACLE_USER": "v8live",
				"ORACLE_PASS": "live"
			},
			"enabled": true
		}
	}
}
```

## Build and Test

### Development Setup

1. Create virtual environment (optional but recommended)
2. Install dependencies: `pip install -r requirements.txt`
3. Configure Oracle Instant Client PATH
4. Set environment variables
5. Start MCP server: `python server/main.py`

### Testing Strategy

- Manual testing through VS Code MCP client
- Unit tests for individual components (future enhancement)
- Integration tests for MCP protocol compliance

## Performance Considerations

### Connection Pooling

- Uses cx_Oracle SessionPool for efficient connection management
- Configurable pool size (default: minimal for personal use)
- Automatic connection cleanup on timeout

### Query Optimization

- Result set limiting via max_rows parameter
- Array size optimization for cursor fetching
- Text truncation to prevent memory issues

### Timeout Handling

- Connection timeout: `conn.callTimeout = timeout_s * 1000`
- Query timeout enforced at connection level
- Prevents runaway queries from blocking the server

## SLAs and Budgets

### Response Time Targets

- Simple queries: < 1 second
- Complex queries: < 30 seconds (configurable)
- Metadata operations: < 5 seconds

### Resource Limits

- Maximum rows per query: 500 (configurable)
- Maximum text size per field: 5000 characters (configurable)
- Connection timeout: 30 seconds (configurable)

### Error Rates

- Target: < 1% errors for valid operations
- All errors should be properly caught and reported
- No unhandled exceptions should reach the client

## Deployment Architecture

### Local Development

- MCP server runs as local process
- Direct connection to Oracle Database
- No network services exposed

### File Structure

```
├─ server/
│  ├─ main.py              # MCP server entry point
│  ├─ mcp_server.py        # Tool registration
│  ├─ oracle/
│  │  ├─ pool.py           # Connection pool management
│  │  ├─ exec.py           # Query execution
│  │  ├─ meta.py           # Metadata operations
│  │  ├─ plsql.py          # PL/SQL operations
│  │  ├─ tests.py          # Test helpers
│  │  └─ usage_description.md # Dynamic usage instructions template
│  └─ util/
│     ├─ json.py           # Serialization utilities
│     └─ log.py            # Logging utilities
├─ requirements.txt
└─ .vscode/mcp.json
```

## Monitoring and Observability

### Logging

- Minimal logging to file/console
- Connection events and errors logged
- No sensitive data (passwords, query data) logged

### Health Checks

- `ping()` tool for connectivity testing (enhanced with Oracle version)
- `session_info()` for connection status
- `db_get_server_info` for version and usage overview
- `db_get_usage_instructions` for dynamic guidelines (file-loaded per request)
- Automatic connection pool health monitoring

### New Tool: db_get_usage_instructions

- **Purpose**: Provide dynamic MCP usage instructions loaded from server/oracle/usage_description.md on each call (no caching for freshness).
- **Schema**:
  ```json
  {
  	"type": "object",
  	"properties": {},
  	"required": []
  }
  ```
- **Implementation**: Read file, substitute placeholders (oracle_version via SELECT banner FROM v$version, env vars like MCP_MAX_ROWS), return formatted text in {"status": "success", "usage": "..."}.
- **File Loading**: Synchronous file read per request; fallback to static text if file missing. Supports updates without server restart.
- **Rationale**: Enables autonomous evolution of instructions; low overhead for MCP usage.

## Related Documentation

- See [`projectbrief.md`](memory-bank/projectbrief.md) for project constraints and requirements
- See [`systemPatterns.md`](memory-bank/systemPatterns.md) for architecture decisions (ADRs)
- See [`productContext.md`](memory-bank/productContext.md) for user requirements
- See [`activeContext.md`](memory-bank/activeContext.md) for current implementation status
