Custom Tools - Oracle MCP Servers

The Oracle Database MCP Toolkit’s custom tool framework lets you create MCP tools declaratively using YAML, without writing Java code. Each tool executes a SQL statement and can accept parameters from the LLM.

Overview

YAML-based custom tools provide:

Declarative Definition: Define tools in a simple YAML file
SQL Execution: Each tool runs a SQL statement
Parameter Binding: Map LLM parameters to SQL placeholders
Multiple Datasources: Connect to multiple databases
Type Safety: Parameter types validated by MCP
No Code Required: Extend the server without rebuilding

YAML Configuration Structure

A YAML configuration file has two main sections:

dataSources:
  # Database connection definitions
  
tools:
  # Custom tool definitions

Defining DataSources

Basic DataSource

dataSources:
  prod-db:
    url: jdbc:oracle:thin:@prod-host:1521/ORCLPDB1
    user: app_user
    password: secure_password

Required Fields

url (string, required): JDBC URL for the database connection
user (string, required): Database username
password (string, required): Database password

Optional Fields

host (string, optional): Hostname or IP address
port (integer, optional): Port number
database (string, optional): Oracle service name

Environment Variable Substitution

Use ${variable} syntax to reference environment variables:

dataSources:
  prod-db:
    url: jdbc:oracle:thin:@${DB_HOST}:${DB_PORT}/${DB_SERVICE}
    user: ${DB_USER}
    password: ${DB_PASSWORD}

Multiple DataSources

dataSources:
  prod-db:
    url: jdbc:oracle:thin:@prod-host:1521/ORCLPDB1
    user: prod_user
    password: prod_pass
    
  dev-db:
    url: jdbc:oracle:thin:@dev-host:1521/ORCLDEV
    user: dev_user
    password: dev_pass
    
  analytics-db:
    url: jdbc:oracle:thin:@analytics-host:1521/ANALYTICS
    user: readonly_user
    password: readonly_pass

Defining Tools

Basic Tool

tools:
  list-customers:
    description: Returns all customers in the database
    statement: SELECT id, name, email FROM customers ORDER BY name

Required Fields

description (string, required): Brief description of what the tool does
statement (string, required): SQL statement to execute

Optional Fields

dataSource (string, optional): Name of the datasource to use
parameters (array, optional): List of tool parameters

Tool with Parameters

tools:
  customer-by-email:
    description: Find a customer by their email address
    parameters:
      - name: email
        type: string
        description: Customer email address
        required: true
    statement: SELECT * FROM customers WHERE email = :email

Parameter Definition

Required Fields:

name (string): Parameter name (must match SQL placeholder)
type (string): Data type for MCP validation
description (string): Description for the LLM

Optional Fields:

required (boolean, default: false): Whether the parameter is required

Supported Types:

string: Text values
number: Numeric values (integers, decimals)
boolean: True/false values
array: Array of values
object: JSON objects

SQL Parameter Binding

Named Parameters

Use :paramName syntax in SQL statements:

tools:
  orders-by-status:
    description: Get orders by status
    parameters:
      - name: status
        type: string
        description: Order status (PENDING, SHIPPED, DELIVERED)
        required: true
    statement: SELECT * FROM orders WHERE status = :status

Multiple Parameters

tools:
  orders-by-date-range:
    description: Get orders within a date range
    parameters:
      - name: start_date
        type: string
        description: Start date (YYYY-MM-DD)
        required: true
      - name: end_date
        type: string
        description: End date (YYYY-MM-DD)
        required: true
    statement: |
      SELECT * FROM orders 
      WHERE order_date >= TO_DATE(:start_date, 'YYYY-MM-DD')
        AND order_date <= TO_DATE(:end_date, 'YYYY-MM-DD')
      ORDER BY order_date DESC

Optional Parameters

tools:
  search-products:
    description: Search products by name and optional category
    parameters:
      - name: product_name
        type: string
        description: Product name to search for
        required: true
      - name: category
        type: string
        description: Optional category filter
        required: false
    statement: |
      SELECT * FROM products
      WHERE name LIKE '%' || :product_name || '%'
        AND (:category IS NULL OR category = :category)

DataSource Resolution Logic

The server determines which datasource to use based on these rules (in priority order):

1. Tool-Specific DataSource

If the tool specifies a datasource, it’s used:

tools:
  prod-customers:
    dataSource: prod-db  # Explicitly use prod-db
    description: Get customers from production
    statement: SELECT * FROM customers

2. System Properties

If no tool-specific datasource, check system properties:

java -Ddb.url=jdbc:oracle:thin:@host:1521/service \
     -Ddb.user=user \
     -Ddb.password=pass \
     -jar oracle-db-mcp-toolkit-1.0.0.jar

3. YAML Default

If no system properties, use the first datasource in YAML:

dataSources:
  default-db:  # This will be used as fallback
    url: jdbc:oracle:thin:@host:1521/service
    user: user
    password: pass

4. Configuration Error

If no datasource can be resolved, the tool fails with a clear error message.

Best Practice: Explicitly specify dataSource for each tool to avoid ambiguity.

Complete Examples

Example 1: Hotel Search System

dataSources:
  hotel-db:
    url: jdbc:oracle:thin:@hotels.example.com:1521/HOTELS
    user: ${HOTEL_DB_USER}
    password: ${HOTEL_DB_PASSWORD}

tools:
  hotels-by-name:
    dataSource: hotel-db
    description: Returns hotel details by name including capacity, rating and address
    parameters:
      - name: name
        type: string
        description: Hotel name to search for (partial match supported)
        required: false
    statement: SELECT * FROM hotels WHERE name LIKE '%' || :name || '%'
  
  hotels-by-city:
    dataSource: hotel-db
    description: Find hotels in a specific city
    parameters:
      - name: city
        type: string
        description: City name
        required: true
      - name: min_rating
        type: number
        description: Minimum rating (1-5)
        required: false
    statement: |
      SELECT name, address, rating, capacity
      FROM hotels
      WHERE city = :city
        AND (:min_rating IS NULL OR rating >= :min_rating)
      ORDER BY rating DESC
  
  available-rooms:
    dataSource: hotel-db
    description: Check room availability for a hotel on specific dates
    parameters:
      - name: hotel_id
        type: number
        description: Hotel ID
        required: true
      - name: check_in
        type: string
        description: Check-in date (YYYY-MM-DD)
        required: true
      - name: check_out
        type: string
        description: Check-out date (YYYY-MM-DD)
        required: true
    statement: |
      SELECT room_type, COUNT(*) as available_rooms
      FROM rooms r
      WHERE r.hotel_id = :hotel_id
        AND r.id NOT IN (
          SELECT room_id FROM bookings
          WHERE hotel_id = :hotel_id
            AND check_out > TO_DATE(:check_in, 'YYYY-MM-DD')
            AND check_in < TO_DATE(:check_out, 'YYYY-MM-DD')
        )
      GROUP BY room_type

Example 2: E-commerce System

dataSources:
  ecommerce-db:
    url: jdbc:oracle:thin:@shop.example.com:1521/SHOP
    user: shop_app
    password: ${SHOP_PASSWORD}

tools:
  customer-orders:
    dataSource: ecommerce-db
    description: Get all orders for a customer by email address
    parameters:
      - name: email
        type: string
        description: Customer email address
        required: true
    statement: |
      SELECT o.id, o.order_date, o.total, o.status, o.tracking_number
      FROM orders o
      JOIN customers c ON o.customer_id = c.id
      WHERE c.email = :email
      ORDER BY o.order_date DESC
  
  product-search:
    dataSource: ecommerce-db
    description: Search products by name and optional filters
    parameters:
      - name: search_term
        type: string
        description: Product name or description keywords
        required: true
      - name: category
        type: string
        description: Product category filter
        required: false
      - name: max_price
        type: number
        description: Maximum price filter
        required: false
    statement: |
      SELECT id, name, description, price, category, stock_quantity
      FROM products
      WHERE (name LIKE '%' || :search_term || '%' 
             OR description LIKE '%' || :search_term || '%')
        AND (:category IS NULL OR category = :category)
        AND (:max_price IS NULL OR price <= :max_price)
        AND stock_quantity > 0
      ORDER BY price ASC
  
  order-status:
    dataSource: ecommerce-db
    description: Check order status by order ID
    parameters:
      - name: order_id
        type: number
        description: Order ID
        required: true
    statement: |
      SELECT 
        o.id,
        o.status,
        o.order_date,
        o.total,
        o.tracking_number,
        c.name as customer_name,
        c.email as customer_email,
        COUNT(oi.id) as item_count
      FROM orders o
      JOIN customers c ON o.customer_id = c.id
      LEFT JOIN order_items oi ON o.id = oi.order_id
      WHERE o.id = :order_id
      GROUP BY o.id, o.status, o.order_date, o.total, 
               o.tracking_number, c.name, c.email

Example 3: Multi-Database Analytics

dataSources:
  prod-db:
    url: jdbc:oracle:thin:@prod.example.com:1521/PROD
    user: prod_reader
    password: ${PROD_PASSWORD}
  
  analytics-db:
    url: jdbc:oracle:thin:@analytics.example.com:1521/ANALYTICS
    user: analyst
    password: ${ANALYTICS_PASSWORD}

tools:
  daily-sales-summary:
    dataSource: analytics-db
    description: Get daily sales summary from analytics warehouse
    parameters:
      - name: date
        type: string
        description: Date in YYYY-MM-DD format
        required: true
    statement: |
      SELECT 
        date,
        total_orders,
        total_revenue,
        avg_order_value,
        new_customers
      FROM daily_sales_summary
      WHERE date = TO_DATE(:date, 'YYYY-MM-DD')
  
  real-time-inventory:
    dataSource: prod-db
    description: Get real-time inventory levels from production database
    parameters:
      - name: product_id
        type: number
        description: Product ID
        required: true
    statement: |
      SELECT 
        p.id,
        p.name,
        i.quantity,
        i.last_updated,
        w.name as warehouse_name
      FROM products p
      JOIN inventory i ON p.id = i.product_id
      JOIN warehouses w ON i.warehouse_id = w.id
      WHERE p.id = :product_id

Running with YAML Configuration

Launch the server with your YAML config:

java -DconfigFile=/path/to/config.yaml \
     -jar oracle-db-mcp-toolkit-1.0.0.jar

Claude Desktop Configuration

{
  "mcpServers": {
    "oracle-db-mcp-toolkit": {
      "command": "java",
      "args": [
        "-DconfigFile=/absolute/path/to/config.yaml",
        "-jar",
        "/path/to/oracle-db-mcp-toolkit-1.0.0.jar"
      ]
    }
  }
}

With Environment Variables

{
  "mcpServers": {
    "oracle-db-mcp-toolkit": {
      "command": "java",
      "args": [
        "-DconfigFile=/path/to/config.yaml",
        "-jar",
        "/path/to/oracle-db-mcp-toolkit-1.0.0.jar"
      ],
      "env": {
        "DB_USER": "app_user",
        "DB_PASSWORD": "secure_password",
        "DB_HOST": "database.example.com",
        "DB_PORT": "1521",
        "DB_SERVICE": "ORCLPDB1"
      }
    }
  }
}

Using Custom Tools

Once configured, use natural language to invoke your tools:

"Find hotels named 'Grand' in the database"
"Get all orders for customer [email protected]"
"Search for products matching 'wireless headphones' under $100"
"Check order status for order ID 12345"
"Show me daily sales summary for 2024-03-15"

The LLM will:

Select the appropriate tool based on your request
Extract parameters from your natural language input
Call the tool with the parameters
Return the SQL query results

Best Practices

Security

Use environment variables for sensitive credentials
Limit permissions: Use read-only database users when possible
Avoid dynamic SQL: Use parameterized queries only
Validate input: Rely on MCP type validation
Rotate credentials: Change passwords regularly

Performance

Add LIMIT clauses: Prevent returning too many rows
Use indexes: Ensure queried columns are indexed
Optimize SQL: Test queries for performance
Connection pooling: Server uses UCP automatically

Maintainability

Clear descriptions: Help the LLM understand tool purpose
Descriptive names: Use clear tool and parameter names
Comment complex SQL: Add comments for complex logic
Version control: Keep YAML configs in version control
Test thoroughly: Validate tools with sample data

SQL Best Practices

# Good: Clear, parameterized, with limits
tools:
  recent-orders:
    description: Get the 10 most recent orders for a customer
    parameters:
      - name: customer_id
        type: number
        required: true
    statement: |
      SELECT id, order_date, total, status
      FROM orders
      WHERE customer_id = :customer_id
      ORDER BY order_date DESC
      FETCH FIRST 10 ROWS ONLY

# Avoid: No limits, unclear purpose
tools:
  get-stuff:
    description: Gets stuff
    statement: SELECT * FROM orders  -- Returns all rows!

Troubleshooting

Tool Not Found

Symptom: “Tool X not found” error Solutions:

Check tool name spelling in YAML
Ensure YAML syntax is valid
Verify -DconfigFile path is correct
Check server logs for YAML parsing errors

Parameter Binding Error

Symptom: “Missing parameter” or “Bind variable not found” Solutions:

Ensure parameter names match SQL placeholders exactly
Check for typos in :paramName
Verify parameter is marked required: true if mandatory

Connection Failed

Symptom: “Cannot connect to database” Solutions:

Verify JDBC URL format is correct
Check database hostname, port, service name
Ensure network connectivity to database
Verify credentials are correct
Check firewall rules

DataSource Resolution Error

Symptom: “No datasource configured for tool” Solutions:

Add dataSource field to tool definition
Or set -Ddb.url, -Ddb.user, -Ddb.password
Or ensure at least one datasource is in YAML

Next Steps

Built-in Tools

Explore log analysis and vector search

Deployment

Deploy with HTTP and OAuth2

Overview

MySQL

Oracle Database

​Overview

​YAML Configuration Structure

​Defining DataSources

​Basic DataSource

​Required Fields

​Optional Fields

​Environment Variable Substitution

​Multiple DataSources

​Defining Tools

​Basic Tool

​Required Fields

​Optional Fields

​Tool with Parameters

​Parameter Definition

​SQL Parameter Binding

​Named Parameters

​Multiple Parameters

​Optional Parameters

​DataSource Resolution Logic

​1. Tool-Specific DataSource

​2. System Properties

​3. YAML Default

​4. Configuration Error

​Complete Examples

​Example 1: Hotel Search System

​Example 2: E-commerce System

​Example 3: Multi-Database Analytics

​Running with YAML Configuration

​Claude Desktop Configuration

​With Environment Variables

​Using Custom Tools

​Best Practices

​Security

​Performance

​Maintainability

​SQL Best Practices

​Troubleshooting

​Tool Not Found

​Parameter Binding Error

​Connection Failed

​DataSource Resolution Error

​Next Steps

Built-in Tools

Deployment

Build docs developers (and LLMs) love

Overview

YAML Configuration Structure

Defining DataSources

Basic DataSource

Required Fields

Optional Fields

Environment Variable Substitution

Multiple DataSources

Defining Tools

Basic Tool

Required Fields

Optional Fields

Tool with Parameters

Parameter Definition

SQL Parameter Binding

Named Parameters

Multiple Parameters

Optional Parameters

DataSource Resolution Logic

1. Tool-Specific DataSource

2. System Properties

3. YAML Default

4. Configuration Error

Complete Examples

Example 1: Hotel Search System

Example 2: E-commerce System

Example 3: Multi-Database Analytics

Running with YAML Configuration

Claude Desktop Configuration

With Environment Variables

Using Custom Tools

Best Practices

Security

Performance

Maintainability

SQL Best Practices

Troubleshooting

Tool Not Found

Parameter Binding Error

Connection Failed

DataSource Resolution Error

Next Steps