Oracle MCP Server

Instructions

Implementation Reference

• src/index.js:316-343 (handler)

  The handleExecuteQuery method is the primary handler for the 'execute_query' tool. It validates the input arguments, calls the executeQuery helper, and returns the result in the MCP content format.

  async handleExecuteQuery(args) {
    // Input validation
    if (!args.query || typeof args.query !== 'string') {
      throw new Error('Query parameter is required and must be a string');
    }
    
    if (args.query.length > 10000) {
      throw new Error('Query too long (max 10000 characters)');
    }
    
    const result = await this.executeQuery(args.query, args.params || [], {
      maxRows: args.maxRows || 1000
    });
  
    return {
      content: [
        {
          type: 'text',
          text: JSON.stringify({
            query: args.query,
            rowCount: result.rowCount,
            rows: result.rows,
            metadata: result.metadata
          }, null, 2)
        }
      ]
    };
  }

• src/index.js:168-193 (schema)

  The input schema definition for the 'execute_query' tool, including properties for query, params, and maxRows, as returned by the ListToolsRequestHandler.

  {
    name: 'execute_query',
    description: 'Execute a SQL query on the Oracle database',
    inputSchema: {
      type: 'object',
      properties: {
        query: {
          type: 'string',
          description: 'SQL query to execute'
        },
        params: {
          type: 'array',
          description: 'Query parameters (optional)',
          items: {
            type: ['string', 'number', 'boolean', 'null']
          }
        },
        maxRows: {
          type: 'number',
          description: 'Maximum number of rows to return (default: 1000)',
          default: 1000
        }
      },
      required: ['query']
    }
  },

• src/index.js:282-284 (registration)

  Registration of the 'execute_query' tool in the CallToolRequestHandler switch statement, dispatching calls to the handleExecuteQuery method.

  case 'execute_query':
    return await this.handleExecuteQuery(args);

• src/index.js:89-162 (helper)

  The executeQuery helper method handles the core database interaction: connects to Oracle, processes parameters, executes the query, handles errors and logging, and returns formatted results.

  async executeQuery(query, params = [], options = {}) {
    let connection;
    const startTime = Date.now();
    
    // Security audit logging
    console.error(`[AUDIT] Query execution started at ${new Date().toISOString()}`);
    console.error(`[AUDIT] Query: ${query.substring(0, 200)}${query.length > 200 ? '...' : ''}`);
    console.error(`[AUDIT] Parameters: ${JSON.stringify(params)}`);
    
    try {
      // Get connection from pool or create new connection
      const config = this.getConnectionConfig();
      connection = await oracledb.getConnection(config);
      
      // Set default schema if specified
      if (process.env.ORACLE_DEFAULT_SCHEMA) {
        // Validate schema name to prevent SQL injection
        const schemaName = process.env.ORACLE_DEFAULT_SCHEMA.toUpperCase();
        if (!/^[A-Z][A-Z0-9_$]*$/.test(schemaName)) {
          throw new Error('Invalid schema name format');
        }
        await connection.execute(`ALTER SESSION SET CURRENT_SCHEMA = ${schemaName}`);
      }
      
      // Convert positional parameters ($1, $2) to Oracle bind parameters (:1, :2)
      let oracleQuery = query;
      let oracleParams = {};
      
      // Handle PostgreSQL style parameters
      if (params.length > 0) {
        params.forEach((param, index) => {
          const pgParam = `$${index + 1}`;
          const oracleParam = `:${index + 1}`;
          oracleQuery = oracleQuery.replace(new RegExp('\\' + pgParam + '\\b', 'g'), oracleParam);
          oracleParams[index + 1] = param;
        });
      }
      
      // Execute query with options
      const result = await connection.execute(oracleQuery, oracleParams, {
        outFormat: oracledb.OUT_FORMAT_OBJECT,
        autoCommit: options.autoCommit !== false,
        maxRows: options.maxRows || 1000
      });
      
      // Format result to match expected structure
      return {
        rows: result.rows || [],
        rowCount: result.rowsAffected || (result.rows ? result.rows.length : 0),
        metadata: result.metaData
      };
    } catch (error) {
      if (connection) {
        try {
          await connection.rollback();
        } catch (rollbackError) {
          console.error('Rollback error:', rollbackError);
        }
      }
      // Audit log for errors
      console.error(`[AUDIT] Query failed: ${error.message} (Duration: ${Date.now() - startTime}ms)`);
      throw error;
    } finally {
      if (connection) {
        try {
          await connection.close();
        } catch (closeError) {
          console.error('Connection close error:', closeError);
        }
      }
      // Audit log completion
      console.error(`[AUDIT] Query execution completed (Duration: ${Date.now() - startTime}ms)`);
    }
  }