Content
# Oracle APEX MCP Server
MCP (Model Context Protocol) server for **Oracle Database** and **Oracle APEX** integration. Provides 25 tools for multi-database management, querying, executing DDL/DML, inspecting database objects, and reading APEX metadata — all through a standardized MCP interface.
## Features
- **Multi-database support**: switch between databases on the fly without restarting
- **TNS names**: auto-discovers `tnsnames.ora` from ORACLE_HOME, TNS_ADMIN, or common paths
- **3 connection modes**: TNS alias, connection string, or manual (host/port/service)
- **Oracle Database**: query, execute DML/DDL/PL/SQL, transactions, explain plans, compile objects, show errors
- **Object Inspection**: list tables, describe tables (columns, indexes, constraints, triggers), list objects, get source, search, dependencies
- **APEX Metadata (read-only)**: list applications, describe apps/pages, workspace users, REST services, ORDS-enabled objects
- **Connection pooling** with configurable min/max/timeout
- **Thick mode** support for legacy Oracle databases (pre-12c crypto)
## Requirements
- **Node.js** >= 18.0.0
- **Oracle Database** accessible via network (any version supported by `oracledb` driver)
- **Oracle Instant Client** (only if using Thick mode for legacy databases)
## Quick Install (auto-registers with Claude Code)
```bash
git clone <repo-url>
cd oracle-apex-mcp-server
npm install
npm run install:claude
```
That's it. Restart Claude Code and start using it. No manual configuration needed.
The installer will:
1. Install dependencies
2. Build the TypeScript project
3. Register the MCP server in `~/.claude/mcp.json`
> To register for a specific project instead: `npm run install:project`
> **Windows note**: If `npm run build` runs out of memory, use:
> ```cmd
> set NODE_OPTIONS=--max-old-space-size=4096
> npx tsc
> ```
## How It Works
When you start a Claude Code session, the server starts with no pre-configured database. You choose how to connect:
### 1. Browse available databases from TNS
```
> list my available oracle databases
(Claude calls oracle_list_tns_entries)
> connect to PROD_DB as user hr
(Claude calls oracle_connect mode=tns tns_alias=PROD_DB username=hr password=...)
```
### 2. Connect with host/port/service
```
> connect to oracle on 192.168.1.100 port 1521 service MYDB as user admin
(Claude calls oracle_connect mode=manual host=192.168.1.100 port=1521 service_name=MYDB ...)
```
### 3. Switch databases anytime
```
> switch to DEV_DB
(Claude calls oracle_connect mode=tns tns_alias=DEV_DB ...)
> now switch to TEST_DB
(same — closes old pool, opens new one)
```
### 4. Check current connection
```
> which database am I connected to?
(Claude calls oracle_current_connection)
```
## Configuration
### Environment Variables (all optional)
Set these in `~/.claude/mcp.json` under `env` if you want a default connection at startup:
| Variable | Default | Description |
|---|---|---|
| `ORACLE_HOST` | `localhost` | Oracle DB hostname |
| `ORACLE_PORT` | `1521` | Oracle DB port |
| `ORACLE_SERVICE_NAME` | `XE` | Oracle service name |
| `ORACLE_USERNAME` | `hr` | Database username (also reads `ORACLE_USER`) |
| `ORACLE_PASSWORD` | _(empty)_ | Database password |
| `ORACLE_CONNECTION_STRING` | _(auto-built)_ | Full TNS connect string (overrides host/port/service) |
| `ORACLE_TNS_ALIAS` | _(none)_ | TNS alias to use from tnsnames.ora |
| `TNS_ADMIN` | _(none)_ | Directory containing tnsnames.ora |
| `ORACLE_HOME` | _(none)_ | Oracle home directory (fallback for TNS lookup) |
| `TNS_NAMES_FILE` | _(auto-detected)_ | Explicit path to tnsnames.ora |
| `ORACLE_POOL_MIN` | `1` | Minimum pool connections |
| `ORACLE_POOL_MAX` | `10` | Maximum pool connections |
| `ORACLE_POOL_TIMEOUT` | `60` | Pool timeout in seconds |
| `ORACLE_STMT_CACHE_SIZE` | `30` | Statement cache size |
| `ORACLE_FETCH_SIZE` | `100` | Fetch array size |
| `ORACLE_OLD_CRYPTO` | `false` | Set `true` to enable Thick mode (required for pre-12c databases) |
| `ORACLE_CLIENT_LIB_DIR` | _(none)_ | Path to Oracle Instant Client (Thick mode only) |
### TNS Discovery
The server automatically searches for `tnsnames.ora` in these locations (in order):
1. `$TNS_ADMIN/tnsnames.ora`
2. `$ORACLE_HOME/network/admin/tnsnames.ora`
3. Common Windows paths (`C:\oracle\...`, `C:\app\oracle\...`)
4. Common Linux paths (`/etc/oracle/...`, `/opt/oracle/...`, `/u01/...`)
## Manual Claude Code Setup
If you prefer manual configuration instead of the auto-installer:
### Option 1: CLI command
```bash
claude mcp add-json oracle-apex '{"type":"stdio","command":"node","args":["/ruta/a/oracle-apex-mcp-server/dist/index.js"],"env":{"TNS_ADMIN":"/ruta/a/network/admin"}}' --scope user
```
### Option 2: Edit `~/.claude/mcp.json`
```json
{
"mcpServers": {
"oracle-apex": {
"type": "stdio",
"command": "node",
"args": ["/ruta/a/oracle-apex-mcp-server/dist/index.js"],
"env": {
"TNS_ADMIN": "/ruta/a/network/admin",
"ORACLE_OLD_CRYPTO": "true",
"ORACLE_CLIENT_LIB_DIR": "/ruta/a/instantclient"
}
}
}
}
```
### With default connection at startup
```json
{
"mcpServers": {
"oracle-apex": {
"type": "stdio",
"command": "node",
"args": ["/ruta/a/oracle-apex-mcp-server/dist/index.js"],
"env": {
"ORACLE_HOST": "myhost",
"ORACLE_PORT": "1521",
"ORACLE_SERVICE_NAME": "MYDB",
"ORACLE_USERNAME": "myuser",
"ORACLE_PASSWORD": "mypassword"
}
}
}
}
```
## Available Tools (25)
### Connection Management (4) — NEW
| Tool | Description |
|---|---|
| `oracle_list_tns_entries` | List all databases from tnsnames.ora (auto-discovered or custom path) |
| `oracle_connect` | Connect/switch to a database (TNS alias, connection string, or manual) |
| `oracle_disconnect` | Disconnect and close the connection pool |
| `oracle_current_connection` | Show which database is currently connected |
### Database Tools (9)
| Tool | Description |
|---|---|
| `oracle_health_check` | Check Oracle DB and APEX connectivity, version, pool status |
| `oracle_query` | Execute read-only SELECT/WITH queries (up to 10,000 rows) |
| `oracle_execute` | Execute DML, DDL, or PL/SQL with optional auto-commit |
| `oracle_transaction` | Execute multiple statements in a single transaction |
| `oracle_explain_plan` | Generate execution plan for SQL optimization |
| `oracle_compile_object` | Compile/recompile PL/SQL objects (PACKAGE, PROCEDURE, FUNCTION, etc.) |
| `oracle_show_errors` | Show compilation errors (like SQL*Plus SHOW ERRORS) |
| `oracle_table_data_preview` | Preview sample data from a table with optional WHERE/ORDER BY |
| `oracle_connection_info` | Show current connection config (password masked) |
### Object Inspection Tools (6)
| Tool | Description |
|---|---|
| `oracle_list_tables` | List tables with row counts, comments, last analyzed date |
| `oracle_describe_table` | Full table description: columns, indexes, constraints, triggers |
| `oracle_list_objects` | List objects by type (TABLE, VIEW, PACKAGE, etc.) with filters |
| `oracle_get_source` | Get PL/SQL source code or VIEW definition |
| `oracle_search` | Search object names and/or PL/SQL source code |
| `oracle_dependencies` | Show object dependencies (uses / used by) |
### APEX Metadata Tools (6) — Read-Only
| Tool | Description |
|---|---|
| `apex_list_applications` | List APEX applications with page counts |
| `apex_describe_application` | App details: pages, LOVs, auth schemes, build options |
| `apex_describe_page` | Page details: regions, items, processes, dynamic actions, validations |
| `apex_list_workspace_users` | List APEX workspace users with admin/login status |
| `apex_list_rest_services` | List ORDS RESTful service modules, templates, handlers |
| `apex_list_ords_enabled_objects` | List AutoREST-enabled tables/views |
## Development
```bash
npm run build # Compile TypeScript
npm run start # Run the compiled server
npm run dev # Build + start
npm run install:claude # Build + register in ~/.claude/mcp.json
npm run install:project # Build + register in .claude/mcp.json (current dir)
npm run clean # Remove dist/
```
## License
MIT
