Oracle MCP Server

# Oracle MCP Server Test Results ## ✅ Server Status: WORKING **Test Date**: 2025-07-24 ## Connection Details - **Database**: Oracle Autonomous Database (photosightdb) - **User**: ADMIN - **Wallet**: `/Users/sam/oracle-wallets/photosight` - **TNS Name**: `photosightdb_high` - **Oracle Client**: `/opt/oracle/instantclient_19_16` (thick mode) ## Test Results ### 1. Server Initialization ✅ - Oracle client initialized in thick mode - MCP server running on stdio - Environment variables loaded correctly ### 2. Database Connection ✅ - Successfully connected to Oracle ADB - Wallet authentication working - TNS resolution successful ### 3. MCP Tools Tested ✅ #### `list_tables` Tool - Successfully lists all tables in the schema - Found PhotoSight tables: PHOTOS, PROJECTS, TASKS, YOLO_DETECTIONS - Also discovered Oracle Text index tables (DR$*) #### `describe_table` Tool - Successfully retrieves table structure - PHOTOS table has 29 columns including: - Primary key: ID - File management: FILE_PATH, FILE_HASH, FILE_SIZE - Camera metadata: CAMERA_MAKE, CAMERA_MODEL, LENS_MODEL - Image metadata: WIDTH, HEIGHT, CAPTURE_DATE - Processing status tracking #### `execute_query` Tool - Successfully executes parameterized queries - Properly handles bind variables - Returns results in JSON format #### `get_table_indexes` Tool - Found 6 indexes on PHOTOS table - Including primary key, unique constraints, and performance indexes #### `get_table_constraints` Tool - Found 9 constraints on PHOTOS table - Including CHECK constraints for status fields #### `list_schemas` Tool - Can list all accessible schemas in the database ## Performance - Connection establishment: < 1 second - Query execution: < 100ms for metadata queries - No connection pooling issues (uses single connections) ## Security Features - ✅ Uses bind variables to prevent SQL injection - ✅ Wallet-based authentication (no plaintext passwords in connections) - ✅ Single-use connections (no persistent pools) - ✅ Parameter conversion from PostgreSQL style ($1) to Oracle style (:1) ## Integration with Claude Desktop Ready for use with the following configuration: ```json { "mcpServers": { "oracle": { "command": "node", "args": ["/Users/sam/dev/oracle-mcp/src/index.js"], "env": { "ORACLE_TNS_NAME": "${ORACLE_TNS_NAME}", "ORACLE_USER": "${ORACLE_USER}", "ORACLE_PASSWORD": "${ORACLE_PASSWORD}", "TNS_ADMIN": "${TNS_ADMIN}", "ORACLE_CLIENT_PATH": "${ORACLE_CLIENT_PATH}" } } } } ``` ## Conclusion The Oracle MCP server is fully operational and ready for use with AI assistants to query and analyze the PhotoSight database.