Verify that the Oracle driver is loaded correctly and the database connection is working.

Reports: oracledb driver version, driver mode (thin/thick), connection status, Oracle DB version, connect string, TNS config, pool stats, and available TNS aliases.

Use this tool FIRST when setting up the MCP server or when troubleshooting connection issues.

No parameters required.

Read and parse the local tnsnames.ora file, returning all available TNS alias names.

Searches in order: TNS_ADMIN directory → ORACLE_HOME/network/admin → current directory.

Args:

• file_path (string, optional): Explicit path to tnsnames.ora.

List all tables in the connected Oracle database (excluding system schemas like SYS, SYSTEM, etc.).

Use this FIRST to discover what tables exist before running queries.

Args:

• owner (string, optional): Filter by schema/owner name (case-insensitive)

Get the column structure of a specific Oracle table: column names, data types, data lengths, nullable flag, and default values.

Use this after db_list_tables to understand a table's schema before writing queries.

Args:

• table_name (string): The table name to inspect (case-insensitive)

• owner (string, optional): Schema/owner name for cross-schema access

Execute a read-only SQL query against the Oracle database. Only SELECT and WITH (CTE) statements are allowed.

IMPORTANT: Always use Oracle bind variables (:1, :2, ...) instead of string interpolation to prevent SQL injection.

Example: sql: "SELECT * FROM users WHERE active = :1 AND created_at > :2" params: ["Y", "2024-01-01"]

Safety:

• Only SELECT/WITH queries allowed (INSERT/UPDATE/DELETE/DROP blocked)

• Automatic row limit (FETCH FIRST n ROWS ONLY)

• Query timeout enforced

• Maximum 500 rows returned

Args:

• sql (string): Read-only SQL with :1, :2, ... bind variables

• params (array): Values for bind variables (default: [])

• max_rows (number): Max rows to return (default: 100, max: 500)

Generate and display the execution plan for a SQL query WITHOUT executing it.

Uses Oracle's EXPLAIN PLAN to show how the query optimizer would run the query: table access methods (FULL SCAN, INDEX RANGE SCAN, etc.), join strategies, and filter predicates.

Use this to understand query performance before running expensive queries.

Args:

• sql (string): Read-only SQL (SELECT/WITH only) to analyze

• params (array): Bind variable values (default: [])

Insert a single record into an Oracle database table using parameterized named binds.

Auto-generates an INSERT with named bind variables from the provided data object. Column names are validated to prevent SQL injection. After insert, the full row is fetched back via ROWID.

dry_run mode: Set dry_run=true to preview the generated SQL without executing it.

Example: table_name: "users" data: { "name": "Alice", "email": "alice@example.com", "active": true } dry_run: false

Args:

• table_name (string): Target table name

• data (object): Column-value pairs to insert

• dry_run (boolean): Preview SQL without executing (default: false)

Update one or more records in an Oracle database table using parameterized named binds.

Auto-generates the SET clause from the data object. The WHERE clause MUST include named bind variables (:w_1, :w_2, ...) with corresponding where_params.

Safety features:

• WHERE clause is REQUIRED (updates without conditions are blocked)

• Pre-counts matching rows and refuses if exceeding DML_MAX_ROWS (default: 1000)

• dry_run mode previews SQL + affected row count

Example: table_name: "users" data: { "status": "active" } where: "id = :w_1" where_params: [42] dry_run: false

Args:

• table_name, data, where, where_params, dry_run (default: false)

Delete one or more records from an Oracle database table using parameterized named binds.

Safety features:

• WHERE clause is REQUIRED (deletes without conditions are blocked)

• Pre-counts matching rows and refuses if exceeding DML_MAX_ROWS (default: 1000)

• dry_run mode previews SQL + affected row count

Example: table_name: "users" where: "id = :w_1 AND status = :w_2" where_params: [42, "inactive"] dry_run: false

Execute multiple DML statements (INSERT/UPDATE/DELETE) in a single atomic transaction.

All steps either succeed and commit together, or fail and roll back together. This ensures data consistency for multi-step operations.

Limits: 1-10 steps per transaction. Each step must use Oracle bind variables (:1, :2, ...) — never string interpolation.

Example: steps: [ { sql: "UPDATE accounts SET balance = balance - :1 WHERE id = :2", params: [100, 1] }, { sql: "UPDATE accounts SET balance = balance + :1 WHERE id = :2", params: [100, 2] }, { sql: "INSERT INTO transfers (from_id, to_id, amount) VALUES (:1, :2, :3)", params: [1, 2, 100] } ]

This is an atomic money transfer: either all three succeed or none do.

Retrieve information about the current database session: connected user, schema, instance name, host, database name and version, session ID, NLS date format.

Useful for verifying which database/schema you're connected to and for debugging NLS-related issues.

No parameters required.