apex-mcp — Oracle APEX MCP Server

Build, inspect, and modify Oracle APEX 24.2 applications via natural language. Works with Claude, GPT, Gemini, Cursor, VS Code, and any MCP-compatible AI client.

---

Overview

apex-mcp is a Model Context Protocol (MCP) server that exposes 86 tools for building Oracle APEX 24.2 applications through AI assistants. Built on FastMCP 3.x and Python 3.11+, it connects to Oracle Autonomous Database via the oracledb thin driver (mTLS wallet — no Oracle Instant Client required).

Instead of navigating the APEX App Builder UI, you describe what you want and the AI does the work: create apps, generate CRUD pages, add JET charts, configure auth schemes, export pages, generate REST endpoints, and more.

┌─────────────────────────────────────────────────────────────────┐
│  AI Client (Claude / GPT / Gemini / Cursor / VS Code)           │
│                                                                  │
│  "Create a full HR app from the EMPLOYEES table"                 │
└────────────────────────────┬────────────────────────────────────┘
                             │ MCP (stdio or HTTP)
                             ▼
┌─────────────────────────────────────────────────────────────────┐
│  apex-mcp server (FastMCP 3.x, Python 3.11+)                    │
│  86 tools across 15 categories                                   │
└────────────────────────────┬────────────────────────────────────┘
                             │ oracledb (mTLS)
                             ▼
┌─────────────────────────────────────────────────────────────────┐
│  Oracle Autonomous Database 23ai                                 │
│  wwv_flow_imp_page → APEX 24.2 App Builder                      │
└─────────────────────────────────────────────────────────────────┘

---

Supported AI Clients

Client	Transport	Config File	Setup Guide
Claude Code	stdio	.mcp.json	docs/setup-claude.md
Claude Desktop	stdio	claude_desktop_config.json	docs/setup-claude.md
GPT / OpenAI Agents SDK	streamable-http	Python code	docs/setup-gpt.md
Google Gemini CLI	stdio	.gemini/settings.json	docs/setup-gemini.md
Cursor IDE	stdio	.cursor/mcp.json	docs/setup-cursor.md
VS Code / GitHub Copilot	stdio or http	.vscode/mcp.json	docs/setup-vscode.md
ZAI (visual companion)	n/a	side-by-side	docs/setup-zai.md

---

Quick Start

1. Install

git clone https://github.com/your-org/apex-mcp
cd apex-mcp
pip install -e .

2. Configure

Copy and edit the config file for your AI client:

Claude Code — create .mcp.json in your project root:

{
  "mcpServers": {
    "apex-mcp": {
      "command": "python",
      "args": ["-m", "apex_mcp"],
      "cwd": "/path/to/apex-mcp",
      "env": {
        "ORACLE_DB_USER": "YOUR_SCHEMA",
        "ORACLE_DB_PASS": "YOUR_PASSWORD",
        "ORACLE_DSN": "YOUR_DSN",
        "ORACLE_WALLET_DIR": "/path/to/wallet",
        "ORACLE_WALLET_PASSWORD": "YOUR_WALLET_PW",
        "APEX_WORKSPACE_ID": "YOUR_WORKSPACE_ID",
        "APEX_SCHEMA": "YOUR_SCHEMA",
        "APEX_WORKSPACE_NAME": "YOUR_WORKSPACE"
      }
    }
  }
}

For other clients see Supported AI Clients above.

3. Verify

In Claude Code:

/mcp

You should see apex-mcp connected with 86 tools.

Then:

Connect to Oracle and list all APEX applications in the workspace.

---

Transport Modes

apex-mcp supports three transport modes, controlled via --transport flag or environment variables.

stdio (default — local clients)

python -m apex_mcp                        # default
python -m apex_mcp --transport stdio      # explicit
apex-mcp                                  # if installed via pip

Best for: Claude Code, Claude Desktop, Cursor, VS Code, Gemini CLI.

Streamable HTTP (remote / multi-client)

apex-mcp --transport streamable-http --port 8000
# Server available at: http://127.0.0.1:8000/mcp

Best for: OpenAI Agents SDK, Codespaces, remote setups, multiple clients.

SSE (Server-Sent Events)

apex-mcp --transport sse --port 9000
# Server available at: http://127.0.0.1:9000/sse

Best for: clients that specifically require SSE transport.

Environment Variables

All CLI flags have environment variable equivalents (lower priority than CLI args):

Variable	CLI flag	Default
MCP_TRANSPORT	--transport	stdio
MCP_HOST	--host	127.0.0.1
MCP_PORT	--port	8000
MCP_PATH	--path	/mcp (http) or /sse

Oracle Connection Variables

Variable	Description
ORACLE_DB_USER	Oracle schema name
ORACLE_DB_PASS	Oracle schema password
ORACLE_DSN	TNS alias or connect string
ORACLE_WALLET_DIR	Directory containing wallet files (cwallet.sso, etc.)
ORACLE_WALLET_PASSWORD	Wallet encryption password
APEX_WORKSPACE_ID	Numeric APEX workspace ID
APEX_SCHEMA	Schema that owns APEX objects
APEX_WORKSPACE_NAME	APEX workspace name

---

Tool Reference

86 tools across 15 categories.

Connection & SQL

Tool	Description
apex_connect	Connect to Oracle ADB (mTLS wallet)
apex_run_sql	Execute any SELECT/DML/DDL statement
apex_status	Show connection status and session info

Applications

Tool	Description
apex_create_app	Create a new APEX application (starts import session)
apex_finalize_app	Finalize and commit the application
apex_list_apps	List all apps in the workspace
apex_delete_app	Delete an application
apex_get_app_details	Get full app configuration
apex_validate_app	Validate app structure (home page, orphan items, etc.)
apex_dry_run_preview	Toggle dry-run mode (log SQL without executing)
apex_describe_page	Describe a page's components in human-readable form

Pages

Tool	Description
apex_add_page	Add a new page to the app
apex_list_pages	List all pages
apex_get_page_details	Get full page details
apex_update_page	Update page properties
apex_delete_page	Delete a page
apex_copy_page	Copy a page to a new ID
apex_diff_app	Compare app structure before/after changes

Regions & Components

Tool	Description
apex_add_region	Add a region (HTML, IR, report, etc.)
apex_list_regions	List regions on a page
apex_update_region	Update region properties
apex_delete_region	Delete a region
apex_add_interactive_grid	Add an Interactive Grid region
apex_add_master_detail	Add a master-detail region pair
apex_add_notification_region	Add a notification/alert region
apex_add_timeline	Add a timeline region
apex_add_breadcrumb	Add a breadcrumb navigation region
apex_add_faceted_search	Add a faceted search region
apex_add_file_upload	Add a file upload component
apex_add_search_bar	Add a search bar component

Items (Form Fields)

Tool	Description
apex_add_item	Add a form item (text, select, date, etc.)
apex_list_items	List items on a page
apex_update_item	Update item properties
apex_delete_item	Delete an item
apex_bulk_add_items	Add multiple items in one call
apex_add_item_validation	Add server-side validation to an item
apex_add_item_computation	Add a computation to set item value

Buttons & Processes

Tool	Description
apex_add_button	Add a button to a page/region
apex_delete_button	Delete a button
apex_add_process	Add a page process (PL/SQL, DML, branch)
apex_list_processes	List page processes

Dynamic Actions

Tool	Description
apex_add_dynamic_action	Add a dynamic action (client-side event handler)
apex_list_dynamic_actions	List dynamic actions on a page

Charts & Visualizations

Tool	Description
apex_add_jet_chart	Add an Oracle JET chart (bar, line, pie, etc.)
apex_add_gauge	Add a gauge/dial chart
apex_add_funnel	Add a funnel chart
apex_add_sparkline	Add a sparkline chart
apex_add_metric_cards	Add metric/KPI cards with gradient styling
apex_add_calendar	Add a calendar region
apex_add_chart_drilldown	Add drill-down behavior to a chart

Shared Components

Tool	Description
apex_add_lov	Add a List of Values (static or dynamic)
apex_list_lovs	List all LOVs in the app
apex_add_auth_scheme	Add an authorization scheme
apex_list_auth_schemes	List authorization schemes
apex_add_nav_item	Add a navigation menu item
apex_add_app_item	Add an application-level item (global variable)
apex_add_app_process	Add an application-level process

Schema Inspection

Tool	Description
apex_list_tables	List Oracle tables/views in the schema
apex_describe_table	Describe columns, types, PKs, FKs (with cache)
apex_detect_relationships	Auto-detect FK relationships between tables

Generators (High-Level)

Tool	Description
apex_generate_crud	Generate full CRUD (list + form pages) for a table
apex_generate_dashboard	Generate a dashboard with charts
apex_generate_login	Generate a login page
apex_generate_report_page	Generate a formatted report page
apex_generate_wizard	Generate a multi-step wizard
apex_generate_analytics_page	Generate an analytics page with multiple charts
apex_generate_from_schema	Generate a full app from multiple tables
apex_generate_modal_form	Generate a modal form dialog
apex_generate_rest_endpoints	Generate ORDS REST endpoints for tables

JavaScript

Tool	Description
apex_add_page_js	Add page-level JavaScript
apex_add_global_js	Add app-level global JavaScript
apex_add_global_css	Add app-level CSS
apex_add_page_css	Add page-level CSS
apex_generate_ajax_handler	Generate an AJAX callback process

DevOps & Batch

Tool	Description
apex_export_page	Export a page as SQL (for version control)
apex_generate_docs	Generate Markdown documentation for an app
apex_begin_batch	Begin a batch operation (queue multiple SQL statements)
apex_commit_batch	Execute the batch queue in one round-trip
apex_preview_page	Preview page structure (without opening browser)

Setup & Validation

Tool	Description
apex_setup_guide	Get setup instructions for this workspace
apex_check_requirements	Verify all requirements are met
apex_check_permissions	Verify Oracle grants and APEX access
apex_fix_permissions	Auto-fix common permission issues

Users

Tool	Description
apex_create_user	Create an APEX workspace user
apex_list_users	List workspace users

---

Code Examples

Example 1: Full app from schema (single command)

# Uses apex_generate_from_schema internally
# Prompt: "Generate a full app from tables EMPLOYEES, DEPARTMENTS, JOBS"
# Result: App with CRUD pages, dashboard, navigation — in ~10 seconds

Example 2: Incremental build

1. apex_connect()
2. apex_create_app(app_id=200, app_name="Inventory", home_page=1)
3. apex_add_page(page_id=1, page_name="Dashboard", page_mode="Normal")
4. apex_add_region(page_id=1, region_name="KPIs", region_type="STATIC")
5. apex_add_metric_cards(page_id=1, region_id=..., metrics=[...])
6. apex_generate_crud(table_name="PRODUCTS", start_page_id=10)
7. apex_add_nav_item("Dashboard", app_id=200, page_id=1, icon="fa-home")
8. apex_add_nav_item("Products", app_id=200, page_id=10, icon="fa-box")
9. apex_finalize_app()

Example 3: OpenAI Agents SDK

import asyncio
from agents import Agent, Runner
from agents.mcp import MCPServerStreamableHttp

async def main():
    # apex-mcp must be running: apex-mcp --transport streamable-http
    async with MCPServerStreamableHttp(url="http://127.0.0.1:8000/mcp") as apex:
        agent = Agent(
            name="APEX Developer",
            instructions="Build Oracle APEX apps using apex-mcp tools.",
            mcp_servers=[apex],
        )
        result = await Runner.run(
            agent,
            "Connect to Oracle and generate a CRUD app for the ORDERS table."
        )
        print(result.final_output)

asyncio.run(main())

Example 4: Batch operations

apex_begin_batch()
# ... add many regions, items, processes ...
apex_commit_batch(rollback_on_error=True)
# All statements execute in one round-trip; rolls back on any error

Example 5: Dry-run mode

apex_dry_run_preview(enabled=True)
apex_generate_crud(table_name="EMPLOYEES")
# Logs all SQL without executing — useful for review before committing
apex_dry_run_preview(enabled=False)

---

Architecture

apex_mcp/
├── server.py          Entry point — FastMCP 3.x, argparse (stdio/http/sse)
├── __main__.py        Enables `python -m apex_mcp`
├── __init__.py        Version: 0.2.0
├── config.py          Constants (APEX version, workspace ID, date format)
├── db.py              ConnectionManager singleton — mTLS, retry, batch, dry-run
├── ids.py             Sequential ID generator (base 8_900_000_000_000_000)
├── session.py         ImportSession — tracks pages, regions, items, buttons
├── templates.py       Hardcoded Universal Theme 42 template IDs
├── validators.py      Input validation (page IDs, SQL, chart types, table names)
├── utils.py           Shared helpers (_esc, _blk, _sql_to_varchar2)
└── tools/
    ├── sql_tools.py       apex_connect, apex_run_sql, apex_status
    ├── app_tools.py       apex_create_app, apex_finalize_app, ...
    ├── page_tools.py      apex_add_page, apex_list_pages
    ├── component_tools.py apex_add_region, apex_add_item, apex_add_button, ...
    ├── shared_tools.py    apex_add_lov, apex_add_auth_scheme, ...
    ├── schema_tools.py    apex_list_tables, apex_describe_table, ...
    ├── generator_tools.py apex_generate_crud, apex_generate_dashboard, ...
    ├── js_tools.py        apex_add_page_js, apex_add_global_js, ...
    ├── inspect_tools.py   apex_get_app_details, apex_update_*, apex_delete_*, ...
    ├── user_tools.py      apex_create_user, apex_list_users
    ├── setup_tools.py     apex_setup_guide, apex_check_requirements, ...
    ├── validation_tools.py apex_add_item_validation, apex_add_item_computation
    ├── visual_tools.py    apex_add_jet_chart, apex_add_gauge, ...
    ├── advanced_tools.py  apex_generate_report_page, apex_generate_wizard, ...
    └── devops_tools.py    apex_generate_rest_endpoints, apex_export_page, ...

How APEX creation works

apex-mcp creates APEX applications by generating and executing wwv_flow_imp_page.* PL/SQL calls directly against the Oracle database — the same mechanism used by APEX's own import/export system. This bypasses the App Builder UI entirely and is fully compatible with APEX 24.2.13 / Universal Theme 42.

HTTP transport note

When using --transport streamable-http or --transport sse, apex-mcp is a single-user server: the ImportSession singleton means only one active import session exists at a time. For multi-user scenarios, run separate server instances on different ports.

---

Known Limitations

Limitation	Details
Single import session	Only one apex_create_app → apex_finalize_app sequence at a time
No HTTP authentication	Bind HTTP transport to 127.0.0.1 (default); add a reverse proxy for remote access
APEX 24.2 only	wwv_flow_imp_page API is version-specific; not tested on APEX 23.x or 25.x
Oracle mTLS required	TLS direct connections are not supported (wallet required)
Licensed instrument content	TEA_QUESTOES / TEA_OPCOES_RESPOSTA tables are empty (content is licensed)

---

Requirements

Component	Version
Python	3.11+
FastMCP	3.0.0+
oracledb	2.0.0+
Oracle APEX	24.2 (Universal Theme 42)
Oracle DB	19c+ (tested on ADB 23ai)
Oracle Wallet	Required for mTLS connections

No Oracle Instant Client required — the oracledb thin driver handles mTLS natively.

---

Troubleshooting

Connection issues

Error	Cause	Solution
ORA-12541: no listener	Wrong DSN	Check ORACLE_DSN matches the TNS alias in tnsnames.ora
ORA-28759: failure to open file	Wrong wallet path	ORACLE_WALLET_DIR must contain cwallet.sso
DPY-6005: cannot connect to database	Wallet password wrong	Check ORACLE_WALLET_PASSWORD
ModuleNotFoundError: apex_mcp	Not installed	Run pip install -e . from apex-mcp directory

APEX issues

Error	Cause	Solution
ORA-20987: APEX - Application not found	Wrong app_id or workspace	Check APEX_WORKSPACE_ID and run apex_list_apps
ORA-01403: no data found in item creation	Missing p_attributes on DatePicker	Fixed in v0.2.0 — update to latest
PLS-00306: wrong number of arguments	Wrong process parameter name	Use p_process_success_message not p_success_message
Buttons not showing on Standard pages	Wrong region position	Use content region positions (CLOSE/CREATE), not dialog footer

HTTP transport issues

Problem	Solution
ConnectionRefusedError	Start server with apex-mcp --transport streamable-http first
404 Not Found at /	Default path is /mcp; use curl http://127.0.0.1:8000/mcp
OpenAI SDK can't connect	Ensure MCPServerStreamableHttp URL matches --path setting

---

Running Tests

cd apex-mcp
pip install -e ".[dev]"

# Unit tests (no database required)
pytest tests/ -v -m "not integration"

# All tests including integration (requires live Oracle connection)
pytest tests/ -v

The test suite has 51 unit tests covering tools, validators, and generators.

---

Demo Scripts

Script	Description
demos/build_app100.py	TEA clinical assessment app (15 pages, 46 items)
demos/build_app203.py	Analytics dashboard with JET charts + metric cards
demos/build_app204.py	Backoffice from schema (8 tables, 17 pages, score 100/100)
demos/build_app205.py	Assessment wizard (4-step wizard, CRUD, dashboard, 9s build)
demos/demo_rest_endpoints.py	5 tables → 25 ORDS endpoints in 1 second
demos/demo_generate_docs.py	Auto-generate Markdown docs for an app

Run any demo (requires live Oracle connection):

python demos/build_app205.py

---

Contributing

1. Fork the repository
2. Create a feature branch
3. Add tests for new tools in tests/
4. Ensure pytest tests/ -m "not integration" passes
5. Submit a pull request

Adding a new tool

1. Add the function to the appropriate tools/*.py module
2. Register it in server.py with mcp.tool()(function_name)
3. Add unit tests in tests/
4. Update the Tool Reference section in this README

---

License

MIT — see LICENSE for details.

---

pt-BR: Este servidor MCP foi construído para o projeto Plataforma Desfecho TEA (Unimed Nacional), usando Oracle APEX 24.2 + Oracle ADB 23ai. Para configuração detalhada do ambiente de produção, consulte o CLAUDE.md na raiz do projeto.