How to Set Up an MCP Server for Oracle Databases

The Model Context Protocol (MCP) Server for Oracle Database enables AI assistants and large language models to interact directly with your database using natural language queries—transforming manual SQL coding into conversational AI workflows. Oracle's current implementation via SQLcl 25.2+ allows business users and developers to query, analyze, and manage databases through tools like Claude Desktop, GitHub Copilot, and VS Code extensions. For enterprises requiring governed data access alongside MCP capabilities, DreamFactory's Oracle connector adds an enterprise API layer with role-based access control, automatic documentation, and compliance-ready security features that extend far beyond basic MCP functionality.

Key Takeaways

• Oracle SQLcl version 25.2 or later (released in July 2025) is required for MCP Server functionality with Java 17+ as a prerequisite
• Database connections must use the -savepwd flag to store passwords securely for MCP access according to Oracle documentation
• Five core MCP tools are available: list-connections, connect, disconnect, run-sql, and run-sqlcl
• All LLM interactions are logged in the DBTOOLS$MCP_LOG table for audit compliance
• Session tracking through V$SESSION with PROGRAM='SQLcl-MCP' enables real-time monitoring of AI database interactions

Understanding the Role of an MCP Server in Oracle Database Integration

The MCP Server acts as a bridge between AI models and Oracle databases, translating conversational requests into database commands, executing them securely, and returning human-readable results. Rather than writing complex SQL queries manually, users can ask questions in plain English and receive structured data responses.

This architecture provides several advantages:

• Natural language database queries eliminate the need for SQL expertise
• Automatic SQL generation from plain English prompts reduces development time
• Built-in audit trails through the DBTOOLS$MCP_LOG table track all LLM interactions
• Session tracking in V$SESSION with PROGRAM='SQLcl-MCP' enables monitoring
• Complex multi-step tasks including schema exploration, performance tuning, and code generation become accessible to non-technical users

Oracle's official implementation provides enterprise-grade auditing capabilities that community alternatives often lack. The server runs locally on the user's machine with no data sent to Oracle by SQLcl, maintaining data sovereignty while enabling AI-powered database interactions.

Prerequisites for Deploying an MCP Server with Oracle Database Support

Before installing an MCP Server, ensure your environment meets these requirements:

Hardware and Software Dependencies

• Oracle Database: Version 12c or later; Oracle Database 23ai Free recommended for testing
• Java Runtime: Version 17 or later installed and accessible from command line
• SQLcl: Version 25.2 or later (released July 2025) downloaded from Oracle
• AI Client: Claude Desktop, GitHub Copilot, VS Code with SQL Developer extension, or OpenAI Codex

Database Credentials

You'll need at least one saved database connection with the password stored. The connection should have appropriate schema privileges—read-only access for queries, with DDL rights added only if table creation is required.

AI Client Subscriptions

While SQLcl itself is free, AI client subscriptions vary:

• Claude Pro: approximately $20/month
• GitHub Copilot Pro (Individual): approximately $10/month
• ChatGPT Plus (for Codex): approximately $20/month

Step-by-Step Installation of an MCP Server (Self-Hosted Model)

Choosing Your Deployment Environment

The MCP Server operates as a local, self-hosted solution. You can deploy on:

• Virtual Machines: Traditional deployment on Windows, macOS, or Linux
• Containers: Docker-based deployment for isolated environments
• Kubernetes (DreamFactory): Enterprise-scale deployment using Helm charts for orchestration

Installing SQLcl

Step 1: Download SQLcl 25.2 or later from Oracle's website and unzip to your preferred directory.

Step 2: Verify installation by running sql --version from the command line. You should see version 25.2 or later displayed.

Step 3: Create and save your database connection using the following command structure: conn -save followed by your connection name, then -savepwd, then your username, password, host, port, and service name in the format username/password@host:port/service.

Critical: The -savepwd flag is mandatory. Without it, the MCP server cannot auto-connect to your database.

Step 4: Verify your connection with connmgr list to confirm it appears in saved connections.

Configuring Your AI Client

Option A: VS Code with SQL Developer Extension (Easiest)

1. Requires VS Code version 1.102 or newer
2. Install Oracle SQL Developer extension (v25.2+) from VS Code marketplace
3. Enable GitHub Copilot and switch to "Agent" mode
4. SQLcl MCP Server automatically appears in Copilot's tools list

Option B: Claude Desktop (Manual Configuration)

1. Download Claude Desktop from claude.ai/download
2. Open Settings > Developer > Edit Config
3. Add this JSON to claude_desktop_config.json: Create an mcpServers object containing a sqlcl entry with a command property pointing to your SQLcl bin directory followed by /bin/sql, and an args property set to an array containing the single value -mcp.
4. Restart Claude Desktop and verify SQLcl tools appear in Search and tools

Option C: VS Code with Codex Extension

1. Install OpenAI Codex extension in VS Code
2. Open Codex settings > MCP Settings > Open config.toml
3. Add the SQLcl server configuration with a startup timeout of 60000ms
4. Restart Codex to load the new tools

Connecting Your Oracle Databases to the MCP Server

Once your MCP Server is configured, establishing robust connections to Oracle requires attention to several details.

Connection String Best Practices

Use descriptive connection names following a CLIENT-ENV format (like ACME-DEV rather than generic DEV1). This prevents AI assistants from selecting the wrong database when multiple connections exist.

For Oracle databases, configure:

• TNSNames.ora: Standard Oracle naming service configuration
• Service names: Use service names rather than SIDs for RAC compatibility
• Credentials management: Store passwords securely using SQLcl's wallet functionality
• Schema introspection: Grant SELECT privileges on relevant schemas for automatic endpoint discovery

Supported Oracle Features

DreamFactory's Oracle database connector natively supports Oracle 12c+ and automatically generates REST APIs from:

• Tables and views
• Stored procedures
• PL/SQL packages
• Functions with complex return types

This complements MCP functionality by providing governed API access alongside conversational database interactions.

Configuring Secure API Access for Oracle Data on the MCP Server

Security requires the most attention when deploying MCP Servers. Direct AI access to production databases carries significant risk.

Authentication Strategies

Multiple authentication methods protect your Oracle data:

• Database-level authentication: Standard Oracle username/password
• Role-based access control: Restrict AI queries to specific schemas and tables
• Read-only users: Create dedicated MCP users with SELECT-only privileges
• Row-level security: Leverage Oracle Virtual Private Database for data filtering

Critical Security Rules

Following security best practices is essential:

1. Start with least-privilege accounts: Create dedicated MCP database users with minimal grants
2. Use test databases initially: Never point MCP at production during the learning phase
3. Always review before approving: MCP clients ask permission before executing—never enable auto-approve mode
4. Create AGENTS.md files: Document coding standards and restrictions (e.g., "never drop tables")

For organizations requiring enterprise-grade access controls, DreamFactory's security features provide granular RBAC at service, endpoint, table, and field levels with automatic SQL injection prevention.

Optimizing Performance for Oracle Database APIs on Your MCP Server

Performance optimization ensures responsive AI interactions without overwhelming your database.

Connection Pooling and Caching

Implement connection pooling to reduce overhead from repeated connections. The MCP Server maintains stateful sessions with explicit connect/disconnect behavior, enabling precise control over database connection lifecycle.

Key optimization strategies include:

• Query optimization: Review AI-generated SQL for missing indexes
• Load balancing: Distribute queries across read replicas when available
• Transaction management: Use appropriate isolation levels for read-heavy workloads
• Indexing: Ensure frequently queried columns have proper indexes

Real-World Performance Gains

In practice, AI can achieve query cost reduction by creating optimal indexes quickly—work that typically takes longer manually when performed by DBAs.

Advanced Use Cases: Server-Side Scripting and Oracle Integration

Beyond basic queries, MCP enables sophisticated database operations through scripting and automation.

Database Schema Exploration

New developers joining a project can ask: "Connect to [database] and explain my schema structure, including table relationships." Schema documentation generates quickly for new team members joining undocumented projects.

SQL Performance Tuning

DBAs can prompt: "Look at SQL queries in the [schema] and list the top three poorly performing statements. Suggest optimizations with execution plans." The AI identifies missing indexes, suggests query rewrites, and can create indexes automatically.

Code Generation

For APEX or PL/SQL development, prompts like "Create TAPI packages for [table_list] following our coding standards" generate complete create/read/update/delete packages with consistent error handling.

DreamFactory extends these capabilities with its server-side scripting engine, allowing pre/post-process logic in PHP, Python, or Node.js for input validation, data transformation, and workflow automation.

Monitoring and Maintaining Your MCP Server with Oracle Databases

Ongoing monitoring ensures continued performance and compliance.

Log Analysis and Audit Trails

Monitor the DBTOOLS$MCP_LOG table weekly. Prune records to maintain approximately 1000 entries for optimal performance. This table captures all LLM interactions for compliance reporting.

Session tracking through V$SESSION with PROGRAM='SQLcl-MCP' provides real-time visibility into active AI database sessions.

Maintenance Best Practices

• Weekly log reviews: Identify unusual query patterns or potential security concerns
• Regular pruning: Keep log tables at manageable sizes
• Software updates: Oracle plans to release new MCP tools monthly following initial launch
• Backup verification: Ensure MCP configuration files are included in backup routines

Why DreamFactory is the Ideal Choice for Oracle MCP Server API Management

While Oracle's SQLcl MCP Server provides direct database access for AI assistants, enterprises often require additional governance, security, and integration capabilities that extend beyond conversational queries.

DreamFactory delivers a comprehensive API management layer specifically designed for Oracle databases within AI and LLM workflows:

Zero-Code API Generation: DreamFactory automatically generates secure, documented REST APIs from Oracle databases in minutes. Unlike code-generation tools that produce static code requiring manual maintenance, DreamFactory's configuration-driven approach means APIs automatically reflect schema changes without redeployment.

Enterprise Security Controls: Granular role-based access control operates at service, endpoint, table, and field levels. Support for OAuth 2.0, SAML, LDAP, and Active Directory authentication ensures enterprise identity management compatibility. Automatic SQL injection prevention protects against malicious queries.

HIPAA-Ready MCP Gateway: For healthcare organizations using Oracle/Cerner, DreamFactory provides a HIPAA-ready MCP integration with field-level PHI redaction. Clinical AI assistants can query EHR data safely through governed APIs with all interactions logged for compliance.

Multi-Database Integration: DreamFactory's Data Mesh capability merges data from multiple disparate databases—including Oracle, SQL Server, PostgreSQL, MongoDB, and Snowflake—into single API responses, enabling AI assistants to access unified data views across your entire enterprise.

Self-Hosted Deployment: Like Oracle's MCP Server, DreamFactory runs exclusively on customer infrastructure—on-premises, private cloud, or air-gapped environments—ensuring complete data sovereignty for regulated industries.

For organizations serious about governed AI data access, DreamFactory transforms Oracle MCP Server implementations from developer experiments into enterprise-ready solutions with comprehensive audit logging, access controls, and compliance capabilities.