execute_query
Run SQL queries on Oracle databases directly through the Oracle MCP Server. Execute specific queries, set parameters, and control row limits for precise data retrieval and analysis.
Instructions
Execute a SQL query on the Oracle database
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| maxRows | No | Maximum number of rows to return (default: 1000) | |
| params | No | Query parameters (optional) | |
| query | Yes | SQL query to execute |
Implementation Reference
- src/index.js:316-343 (handler)MCP tool handler for 'execute_query': validates input arguments, calls the core executeQuery method, and formats the response as MCP content.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:171-192 (schema)Input schema/JSON schema definition for the 'execute_query' tool, specifying query (required string), optional params array, and maxRows.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:168-193 (registration)Tool registration/definition for 'execute_query' in the ListTools response, including name, description, and input schema.{ 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:281-283 (registration)Dispatch/registration in CallToolRequestSchema handler: switch case that routes 'execute_query' calls to handleExecuteQuery.switch (name) { case 'execute_query': return await this.handleExecuteQuery(args);
- src/index.js:89-162 (helper)Core helper method that performs the actual Oracle DB query execution: manages connections, converts PG-style params to Oracle binds, audit logging, error handling.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)`); } }