execute_query
Execute SQL queries on Oracle databases to retrieve or manipulate data, supporting parameters and row limits for efficient database operations.
Instructions
Execute a SQL query on the Oracle database
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| query | Yes | SQL query to execute | |
| params | No | Query parameters (optional) | |
| maxRows | No | Maximum number of rows to return (default: 1000) |
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)`); } }